개발새발, 써놓기

인덱스 (INDEX) ?

- 일반 테이블이나 클러스에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.

- SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체이다.

- 인덱스는 디스크 I/O를 줄이는 많은 방법 중 하나이다.

-- 인덱스 확인

SELECT * FROM user_indexes;
SELECT * FROM user_indexes WHERE table_name = 'EMP';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';

PRIMARY KEY, UNIQUE 컬럼은 자동으로 인덱스가 만들어지며 인덱스 이름은 제약조건의이름이다.

-- B-Tree 인덱스 만들기

SELECT empNo, name, sal FROM emp WHERE name = '심심해';

실행하려는 SQL의 범위를 잡고 F10을 누르면 밑에 계획 설명을 보여준다. COST를 보면 SQL문을 실행해서 걸리는 여러 요인들을 계산해서 대략적인 수치를 보여준다. (원래는 3인데, 지금은 INDEX를 만들어 놓은 상태라서 2입니다.)

-- B-Tree 인덱스 작성

CREATE INDEX idx_emp_name ON emp ( name ); -- NON UNIQUE

이름은 중복의 값이 있을 수 있기 때문에 UNIQUE 값이 아닙니다.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼) ;

여러 예들 ;

SELECT empNo, name, sal FROM emp WHERE SUBSTR(name, 1, 1) = '심';

SELECT empNo, name, sal FROM emp WHERE NOT name = '심심해';

위의 쿼리의 경우 인덱스를 사용하지 않습니다. 비교전에 SUBSTR를 통해 변형되기 때문입니다. 또 NOT을 사용한 문장에서도 인덱스가 사용되지 않습니다.

-- 인덱스 삭제

DROP INDEX idx_emp_name;

DROP INDEX 인덱스명; 을 통해 삭제할 수 있습니다.

-- 결합 인덱스

CREATE INDEX idx_emp_comp ON emp(name, dept);

AND 조건으로 검색하는 경우 결합 인덱스를 만들면 성능에 중요한 역할을 합니다. OR연산은 인덱스를 만들지 않습니다.

CREATE INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명);

※ 주의

CREATE INDEX idx_emp_comp ON emp(dept, name);

위 쿼리처럼 INDEX를 만들면 dept 에서 name 을 검색하므로, (순서가 다르므로) name을 검색하고 dept을 검색하는 것보다 속도가 느립니다. name을 만족하는 행보다 dept을 만족하는 행이 더 많기 때문입니다. (name이 중복값이 있을 수 있지만 dept보다 행의 개수가 적다.) 따라서 결합 인덱스를 만들 때, 괄호 안의 위치도 신경을 써야합니다.

-- 함수 기반 인덱스 만들기

CREATE INDEX idx_emp_fun ON emp(MOD(SUBSTR(rrn, 8, 1), 2 ) ) ;

-- 인덱스 관리

-- 모니터링

-- 인덱스 생성
CREATE INDEX idx_emp_name ON emp(name);

-- 모니터링 시작
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 인덱스 사용 유무 확인
SELECT * FROM v$object_usage; -- used :  no

-- 모니터링 중단
ALTER INDEX idx_emp_name NOMONITORING USAGE;

-- REBUILD

실습을 위해 테이블을 작성하고 테이터를 10000개를 넣어주었습니다.

CREATE TABLE demo (
    num NUMBER
);

BEGIN
    FOR n IN 1 .. 10000 LOOP
        INSERT INTO demo VALUES (n);
    END LOOP;
    COMMIT;
END;
/

SELECT COUNT(*) FROM demo;

-- 위 테이블에 인덱스 추가하기.

CREATE INDEX idx_demo_num ON demo(num);

-- 인덱스 분석

ANALYZE INDEX idx_demo_num VALIDATE STRUCTURE;

SELECT * FROM index_stats;

SELECT (del_lf_rows_len / lf_rows_len ) * 100
FROM index_stats
WHERE name = 'IDX_DEMO_NUM'; -- 0에 가까우면 좋은 상태(인덱스가 깨지지 않은 상태)

DELETE FROM demo WHERE num <= 4000;
COMMIT;

ANALYZE INDEX idx_demo_num VALIDATE STRUCTURE;

SELECT (del_lf_rows_len / lf_rows_len ) * 100
FROM index_stats
WHERE name = 'IDX_DEMO_NUM'; -- 40% 정도 밸런싱이 깨짐

세 번째 쿼리를 처음 실행하면 0이 나오는데, 이는 인덱스가 깨지지 않은 상태를 의미합니다. 후 에, 10000개의 데이터에서 4000개를 삭제하면 IDX_DEMO_NUM 이 39.xxxxx 로 나옵니다. 인덱스가 40%정도 깨져있는 것을 확인할 수 있습니다.

-- REBUILD

ALTER INDEX idx_demo_num REBUILD; -- 밸런싱에 문제가 있으면 관리자가 다시 조절한다.

-- 인덱스 다시 분석
ANALYZE INDEX idx_demo_num VALIDATE STRUCTURE;

SELECT (del_lf_rows_len / lf_rows_len ) * 100
FROM index_stats
WHERE name = 'IDX_DEMO_NUM' -- 인덱스를 REBUILD 했기 때문에 다시 밸런싱이 좋아짐.

밸런싱에 문제가 있어서 ALTE INDEX 인덱스명 REBUILD; 를 통해 REBUILD하고 나면 다시 0으로 되어있는 것을 확인할 수 있습니다.

인덱스는 데이터가 삭제되도 남아있기 때문에 밸런싱이 깨지면 나중에 REBUILD를 하는 것이 필요해보입니다.

문제

-- score1 테이블 작성
hak     문자(20)  기본키
name   문자(30)  NOT  NULL
kor      숫자(3)     NOT  NULL
eng      숫자(3)    NOT  NULL
mat      숫자(3)    NOT  NULL

풀이 >>

CREATE TABLE score1 (
    hak VARCHAR2(30),
    name VARCHAR2(30) NOT NULL,
    kor NUMBER(3) NOT NULL,
    eng NUMBER(3) NOT NULL,
    mat NUMBER(3) NOT NULL,
    CONSTRAINT pk_score1_hak 
        PRIMARY KEY(hak)
);

-- score2 테이블 작성
hak     문자(20)  기본키, score1 테이블의 참조키
kor      숫자(2,1)     NOT  NULL
eng      숫자(2,1)    NOT  NULL
mat      숫자(2,1)    NOT  NULL
     

풀이 >>

CREATE TABLE score2 (
    hak VARCHAR2(20),
    kor NUMBER(2, 1) NOT NULL,
    eng NUMBER(2, 1) NOT NULL,
    mat NUMBER(2, 1) NOT NULL,
    CONSTRAINT pk_score2_hak PRIMARY KEY (hak),
    CONSTRAINT fk_score2_hak FOREIGN KEY (hak) 
        REFERENCES score1 (hak)
);

-- 평점을 구하는 함수 작성
-- 함수명 : fnGrade(s)
95~100:4.5    90~94:4.0
85~89:3.5     80~84:3.0
75~79:2.5     70~74:2.0
65~69:1.5     60~64:1.0
60미만 0
       

풀이 >> 

CREATE OR REPLACE FUNCTION fnGrade
(
	n NUMBER
)
RETURN NUMBER
IS
	s NUMBER(2, 1) := 0;
BEGIN
	IF n>= 95 THEN s := 4.5;
	ELSIF n>=90 THEN s := 4.0;
	ELSIF n>=85 THEN s := 3.5;
	ELSIF n>=80 THEN s := 3.0;
	ELSIF n>=75 THEN s := 2.5;
	ELSIF n>=70 THEN s := 2.0;
	ELSIF n>=65 THEN s := 1.5;
	ELSIF n>=60 THEN s := 1.0;
	ELSE s := 0;
    END IF;
	
	RETURN s;
END;
/

-- score1 테이블과 score2 테이블에 데이터를 추가하는 프로시저 만들기
프로시저명 : pScoreInsert
실행예 : EXEC pScoreInsert('1111', '가가가', 80, 60, 75);
   
score1 테이블 => '1111', '가가가', 80, 60, 75  정보 추가
score2 테이블 => '1111',            3.0, 1.0, 2.5 정보 추가(국, 영, 수 점수가 평점으로 계산되어 추가)
   
단, 국어, 영어, 수학 점수는 0~100 사이가 아니면 예외 발생하고 종료
 

풀이 >>

CREATE OR REPLACE PROCEDURE pScoreInsert
(
	pHak VARCHAR2,
	pName VARCHAR2,
	pKor NUMBER,
	pEng NUMBER,
	pMat NUMBER
)
IS
BEGIN
	IF pKor < 0 OR pKor > 100 THEN 
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF pEng < 0 OR pEng > 100 THEN
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF pMat <0 OR pMat > 100 THEN
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	END IF;
	
	INSERT INTO score1(hak, name, kor, eng, mat) 
		VALUES(pHak, pName, pKor, pEng, pMat);
	INSERT INTO score2(hak, kor, eng, mat)
		VALUES(pHak, fnGrade(pKor), fnGrade(pEng), fnGrade(pMat) );
	COMMIT;
END;
/

-- score1 테이블과 score2 테이블에 데이터를 수정하는 프로시저 만들기
프로시저명 : pScoreUpdate
실행예 : EXEC pScoreUpdate('1111', '가가가', 90, 60, 75);
   
score1 테이블 => 학번이 '1111' 인 자료를  '가가가', 90, 60, 75  으로 정보 수정
score2 테이블 => 학번이 '1111' 인 자료를           4.0, 1.0, 2.5 으로 정보 수정(국, 영, 수 점수가 평점으로 계산되어 수정)
   
단, 국어, 영어, 수학 점수는 0~100 사이가 아니면 예외 발생하고 종료
   

풀이 >>

CREATE OR REPLACE PROCEDURE pScoreUpdate(
	pHak score1.hak%TYPE,
	pName score1.name%TYPE,
	pKor score1.kor%TYPE,
	pEng score1.eng%TYPE,
	pMat score1.mat%TYPE
)
IS
BEGIN
	IF pKor < 0 OR pKor > 100 THEN 
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF pEng < 0 OR pEng > 100 THEN
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF pMat <0 OR pMat > 100 THEN
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	END IF;
	
	UPDATE score1 SET name = pName, kor = pKor, eng = pEng, mat = pMat 
		WHERE hak = pHak;
	UPDATE score2 SET kor = fnGrade(pKor), eng = fnGrade(pEng), mat = fnGrade(pMat)
		WHERE hak = pHak;
	COMMIT;
END;
/

-- score1 테이블과 score2 테이블에 데이터를 삭제하는 프로시저 만들기
프로시저명 : pScoreDelete
실행예 : EXEC pScoreDelete('1111');
score1 과 score2 테이블 정보 삭제

풀이 >>

CREATE PROCEDURE pScoreDelete(
	pHak score1.hak%TYPE
)
IS
BEGIN
	DELETE FROM score2 WHERE hak = pHak;
	DELETE FROM score1 WHERE hak = pHak;
	COMMIT;
END;
/

※ 프로시저 실행시에는 앞에 꼭 EXEC 를 붙여야 한다. 

EXEC pScoreInsert('1111', '홍길동', 80, 90, 90);
EXEC pScoreUpdate('1111', '홍길동', 100, 90, 90);
EXEC pScoreDelete('1111');

IDENTITY COLUMN 이란 ANSI SQL서 지원하는 자동으로 숫자가 되는 컬럼이다.

오라클에서는 내부적으로 시퀀스를 사용한다.

실습용 테이블 생성

CREATE TABLE test(
    num NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    subject VARCHAR2(1000) NOT NULL
);

생성시 GENERATED BY IDENTITY 를 넣으면 된다.

-- 데이터 추가

INSERT INTO test(subject) VALUES ('a');
INSERT INTO test(subject) VALUES ('b');
INSERT INTO test(subject) VALUES ('c');

SELECT * FROM test;

자동으로 num 에 숫자가 들어가 있는 것을 확인할 수 있다.

INSERT INTO test(num, subject) VALUES (10, 'x');

기본적으로 ALWAYS 옵션이므로 INSERT, UPDATE에서 값을 수정할 수 없다.

그러므로 num의 현재 값을 알기위해서

SELECT* FROM user_objects;

에서 ISEQ$$ 으로 시작하는 SEQUENCE를 찾으면된다.

SELECT ISEQ$$_75428.CURRVAL FROM dual;

BY DEFAULT ON NULL : 특정 값으로 IDENTITY 컬럼 값을 지정할 수 있다.

NULL 이면 IDENTITY 값으로 추가하는 테이블을 만들어보자.

CREATE TABLE test(
    num NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    subject VARCHAR2(1000) NOT NULL
);

INSERT INTO test(subject) VALUES ('a'); -- 1 a 추가
INSERT INTO test(num, subject) VALUES (9, 'b'); -- 9 b 추가
INSERT INTO test(num, subject) VALUES (null, 'c'); -- 2 c 추가
INSERT INTO test(subject) VALUES ('d'); -- 3 d 추가

-- BY DEFAULT 옵션 제거

ALTER TABLE test MODIFY (num GENERATED ALWAYS AS IDENTITY);

ALWAYS는 INSERT, UPDATE에서 IDENTITY 컬럼 수정이 불가능하다.

IDENTITY 컬럼은 테이블을 만들때만 지정할 수 있고, 테이블이 만들어진 후 일반컬럼을 IDENTITY 컬럼으로는 변경이 불가능하다. 단, IDENTITY 컬럼을 만든 후 속성을 변경할 수 있다.

BY DEFAULT 옵션을 제거했기 때문에

INSERT INTO test(num, subject) VALUES (99, 'y'); -- 에러

자동으로 생성되는 IDENTITY 컬럼에 값을 넣으면 오류이다.

-- 존재하는 테이블에 IDENTITY 컬럼 추가

CREATE TABLE test(
    qty NUMBER NOT NULL,
    subject VARCHAR2(1000) NOT NULL
);
ALTER TABLE test ADD( num NUMBER GENERATED AS IDENTITY PRIMARY KEY);

IDENTITY 컬럼을 존재하는 테이블에 추가하면된다.

보이지 않는 컬럼(INVISIBLE COLUMN)에 대해 알아보자.

테이블을 생성해서 알아보자.

CREATE TABLE test (
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    tel VARCHAR2(30) INVISIBLE
);


DESC test; -- 확인불가
SELECT * FROM col WHERE tname = 'TEST'; -- INVISIBLE 컬럼 보이지 않음.
SELECT * FROM cols WHERE table_name = 'TEST'; -- INVISIBLE 컬럼 보임.

테이블을 생성할 때 컬럼명 자료형 기술 후 INVISIBLE 을 넣으면 보이지 않는 컬럼이 생성된다.

이 컬럼의 존재는 SELECT * FROM cols WHERE table_name = 'TEST';를 통해 알 수 있다.

이렇게 숨겨진 컬럼에 데이터를 넣을 때는 컬럼명을 명시해야한다.

INSERT INTO test VALUES(1, 'a'); -- 가능
INSERT INTO test VALUES(2, 'b', '010'); -- 에러

INSERT INTO test (num, name, tel) VALUES(2, 'b', '010'); -- 가능

또, 테이블을 보기위해서도 컬럼명을 명시해야한다.

SELECT num, name, tel FROM test; -- INVISIBLE 컬럼 보임
SELECT * FROM test; -- INVISIBLE 컬럼 보이지 않음

-- VISIBLE/INVISIBLE 컬럼으로 변경

ALTER TABLE test MODIFY (tel VISIBLE);
ALTER TABLE test MODIFY (tel INVISIBLE);

-- INVISIBLE 컬럼에 NOT NULL 제약 조건 설정

ALTER TABLE test MODIFY (tel NOT NULL) ;

오라클 11g 방식

-- 급여 내림차순 정렬하여 5개 건너 뛰고 3개 출력 ( 즉 6~8번 출력)

SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM(
        SELECT empNo, name, sal
        FROM emp
        ORDER BY sal DESC
    ) tb WHERE ROWNUM <= 8
)WHERE rnum >= 6;

-- 20개 건너뛰고 10개 출력

SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM(
        SELECT empNo, name, sal
        FROM emp
        ORDER BY sal DESC
    ) tb WHERE ROWNUM <= 30
)WHERE rnum >= 21;

오라클 12c 이상

-- 급여 상위 10%

SELECT empNo, name, sal
FROM emp
ORDER BY sal DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

-- 남자 중 급여 내림차순 정렬하여 11번에서 16번까지 출력

SELECT empNo, name, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 1
ORDER BY sal DESC
OFFSET 10 ROWS FETCH FIRST 6 ROWS ONLY;

-- 여자 중 급여 내림차순 정렬하여 상위 10% 출력

SELECT empNo, name, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0
ORDER BY sal DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

MY-SQL 페이징 처리

-- 20개 건너뛰고 10개 출력

SELECT empNo, name, sal
FROM emp
ORDER BY sal DESC
LIMIT 20, 10;

좀 더 알아보기 >>

CREATE TABLE board (
    num  NUMBER,
    name VARCHAR2(30) NOT NULL,
    subject VARCHAR2(500) NOT NULL,
    content VARCHAR2(4000) NOT NULL,
    reg_date DATE DEFAULT SYSDATE,
    hitCount NUMBER DEFAULT 0,
    CONSTRAINT board_num_pk PRIMARY KEY(num)
);

CREATE SEQUENCE board_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;

CREATE OR REPLACE PROCEDURE pInsertBoard
IS
    vName  VARCHAR2(30);
    vSubject  VARCHAR2(500);
    vContent VARCHAR2(4000);
BEGIN
    FOR  n  IN  1 .. 200000 LOOP
        vName := '자바' || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65)) || (CHR(TRUNC(DBMS_RANDOM.VALUE * 26)+65));
        vSubject := '제목-' || (TRUNC(DBMS_RANDOM.VALUE * 1000000000)+1) || '-입니다.';
        vContent := '게시글 내용-' || (TRUNC(DBMS_RANDOM.VALUE * 100000000)+1) || '-입니다.';
        
        INSERT INTO board(num, name, subject, content, reg_date, hitCount) VALUES
            (board_seq.NEXTVAL, vName, vSubject, vContent, SYSDATE, 0);
    END LOOP;
    COMMIT;
END;
/

EXEC pInsertBoard;
EXEC pInsertBoard;
EXEC pInsertBoard;
EXEC pInsertBoard;
EXEC pInsertBoard;

SELECT num, name, subject, reg_date, hitCount
FROM board
ORDER BY num DESC
OFFSET 1000 ROWS FETCH FIRST 10 ROWS ONLY;

SELECT num, name, subject, reg_date, hitCount FROM (
    SELECT ROWNUM rnum, tb.* FROM (
        SELECT num, name, subject, reg_date, hitCount
        FROM board
        ORDER BY num DESC
    ) tb WHERE ROWNUM <= 1010
) WHERE rnum >= 1001;

우선 정규식을 실습할 테이블이다.

num, name, email에 데이터 들어가 있다.

-- name이 한 또는 백으로 시작하는 레코드만 출력해라.

우리가 정규식을 배우기 전에 했던 방식

SELECT * FROM reg WHERE SUBSTR(name, 1, 1) IN ('한', '백');
SELECT * FROM reg WHERE INSTR(name, '한') = 1 OR INSTR(name, '백') = 1;

정규식을 이용해 출력하기

SELECT * FROM reg WHERE REGEXP_LIKE(name, '^[한백]');

-- name : 강산으로 끝나는 레코드

SELECT * FROM reg WHERE REGEXP_LIKE(name, '강산$');

-- com으로 끝나는 email

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'com$');
    -- 소문자만 가능
SELECT * FROM reg WHERE REGEXP_LIKE(email, 'com$', 'i');
    -- 대소문자 구분 안함

-- email : kim포함

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim');
SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim*');

-- email : kim3?3 가운데 한글자가 뭐든지 상관없음

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim3?3');

-- email : kim 다음에 0~3 사이의 문자가 2번 반복

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim[0-3]{2}');

-- email : kim 다음에 2~3 사이의 문자가 3~4번 반복

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim[2-3]{3,4}');

-- email : kim다음에 1이 아닌 레코드

SELECT* FROM reg WHERE REGEXP_LIKE(email, 'kim[^1]');

-- email : k로 시작하지 않는 사람들

SELECT* FROM reg WHERE REGEXP_LIKE(email, '^[^k]');

-- name : 이름이 한글인 사람만

SELECT* FROM reg WHERE REGEXP_LIKE(name, '^[가-힣]{2,}$');

-- email : 숫자가 있는 이메일을 출력

SELECT* FROM reg WHERE REGEXP_LIKE(email, '[0-9]');
SELECT* FROM reg WHERE REGEXP_LIKE(email, '[[:digit:]]');

-- email : 영문자가 존재하는 이메일 출력

SELECT* FROM reg WHERE REGEXP_LIKE(email, '[a-z|A-Z]');

REGEXP_LIKE(컬럼, '정규식'); 을 통해 컬럼에서 원하는 데이터만 출력해보았다.

REGEXP_REPLACE 는 문자열을 지정한 패턴으로 치환한다.

SELECT 'hong gil dong' FROM dual;
SELECT REGEXP_REPLACE('hong gil dong', '(.*) (.*) (.*)', '\2 \3 \1') FROM dual;
-- . 은 한글자 * 0자 이상
-- (.*) 모든 글자

-- email 에 숫자 없애기

SELECT email, REGEXP_REPLACE(email, '[[:digit:]]', '')FROM reg;

-- 특수 문자 제거

SELECT REGEXP_REPLACE('우리&*나라 12 대!@한', '[[:digit:]|[:punct:]]', '') FROM dual;

-- 숫자와 특수문자 제거

SELECT REGEXP_REPLACE('우리&*나라 12 대!@한', '[[:digit:]|[:punct:]]', '') FROM dual;

-- 지정된 위치부터 문자를 *로 치환

SELECT REGEXP_REPLACE ('00090931111111', '[0-9]', '*', 9) FROM dual;
    -- 7번째 위치부터 [0-9] 문자를 *로 치환

-- 영문자(한글도 포함)가 아닌 문자의 위치

SELECT name, REGEXP_INSTR(name, '[^[:alpha:]]') FROM reg;

-- 이름에 소문자가 아닌 문자가 존재하는 위치

SELECT name, REGEXP_INSTR(name, '[^[:lower:]]') FROM reg;

-- 이름에 문자가 아닌 것이 있는 위치

SELECT name, REGEXP_INSTR(name, '[^[:alpha:]]') FROM reg;

:alpha: 는 한글도 검색한다.

-- email에서 @ 뒤부분을 제외한 ID부분 출력

SELECT email, REGEXP_SUBSTR(email, '[^@]+') FROM reg;

-- email : 0~9 범위의 수가 몇개 존재하는지

SELECT email, REGEXP_COUNT(email, '[0-9]') FROM reg;

시노님 (SYNONYM) ?

사용자가 다른 사용자의 테이블 등의 객체를 참조하는 방법으로 사용자.테이블명으로 접근한다.

-- 현재 사용자가 가지고 있는 권한 확인

SELECT * FROM USER_SYS_PRIVS;

SKY 계정에서 HR계정의 employees 테이블의 내용 확인을 하고 싶다.

그러면

1. SKY계정이 시노님을 작성할 수 있도록 권한을 가지고 있는지 확인한다.

2. 권한이 없으면 관리자에서 권한을 부여한다.

관리자 계정으로 들어가서 GRANT CREATE SYNONYM TO SKY; 로 주면 될 것이다.

-- SKY계정에서 hr.employees를 employees 라는 시노님으로 작성

CREATE SYNONYM employees FOR hr.employees;

-- 시노님 확인

SELECT * FROM syn;

SYNONYM_NAME, TABLE_OWNER, TABLE_NAME 등 원래 소유자와 소유자의 테이블명, 내가 만든 시노님의 테이블명 등을 확인할 수 있다.

-- 시노님 삭제

DROP SYNONYM employees;

시퀀스 (SEQUENCE) ?

유일한 정수값을 연속적으로 생성하는 객체이다.

시퀀스 번호는 트랜잭션 커밋 또는 롤백과 상관없이 증가한다.

-- 시퀀스 만들기

-- 1부터 1씩 증가하는 시퀀스 작성

CREATE SEQUENCE test_seq1
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;

-- 시퀀스 목록 확인

SELECT * FROM user_sequences;
SELECT * FROM seq;

※ 시퀀스를 만들 때 seq로 시퀀스 명을 부여하면 안된다. seq로 시퀀스 이름을 부여하면 목록을 확인할 때 seq로 시퀀스 확인이 불가하다.

-- 다음 시퀀스 값 가져오기

SELECT test_seq1.NEXTVAL FROM dual;

-- 현재 시퀀스 값 확인

SELECT test_seq1.CURRVAL FROM dual;

※ 동일한 SELECT, INSERT 문에서는 NEXTVAL을 여러번 해도 동일한 값이 나옴에 주의하자.

SELECT test_seq1.NEXTVAL, test_seq1.CURRVAL, test_seq1.NEXTVAL FROM dual;

-- 시퀀스 삭제

DROP SEQUENCE 시퀀스이름;
DROP SEQUENCE test_seq1;

SELECT * FROM seq; -- 확인

-- 1부터 증가하는 시퀀스(기본 캐시 20개)를 만들어보자

CREATE SEQUENCE test_seq1;
SELECT * FROM seq;

CREATE SEQUENCE 시퀀스명; 으로 시퀀스를 생성하면 1부터 증가하는 시퀀스(기본캐시20개)를 자동으로 만들어준다.

기본 캐시가 20개라는 의미는 미리 20개의 시퀀스를 만들어 놓는다는 의미로, 만약 현재 시퀀스가 3인 상태에서 오라클 서버가 재실행되면 다음 시퀀스의 값은 21이 된다.

예제를 통해 시퀀스 만드는 방법을 좀 더 알아보자 >>

더보기

-- 100~200까지 100부터 2씩 증가하는 시퀀스(캐시5개, 200 넘어가면 오류)

CREATE SEQUENCE test_seq2
INCREMENT BY 2
START WITH 100
MINVALUE 100
MAXVALUE 200
CACHE 5;

-- 10~20까지 3씩 증가하는 시퀀스(캐시3개 최소1, 최대값에 도달하면 처음부터 다시)

CREATE SEQUENCE test_seq3
INCREMENT BY 3
START WITH 10
MINVALUE 1
MAXVALUE 20
CYCLE
CACHE 3;

몇 번 실행해서 어떻게 진행되는지 확인해보자

SELECT test_seq1.NEXTVAL, test_seq2.NEXTVAL, test_seq3.NEXTVAL
FROM dual;

test_seq3의 경우 CYCLE이 설정되어있는데, CYCLE옵션이 있는 경우 명시적으로 CACHE를 지정하거나 NOCACHE로 설정해야한다.

MINVALUE가 1이기 때문에 20을 초과하면 1부터 다시 CYCLE을 만들며 시작된다.

-- 시퀀스 삭제

DROP SEQUENCE test_seq1;
DROP SEQUENCE test_seq2;
DROP SEQUENCE test_seq3;