[Oracle] 29.시퀀스

in DB on Oracle


시퀀스



employee_id와 같이 테이블의 기본키는 보통 중복되지 않은 숫자입니다!

그런데 데이터 양이 많을 때 중복되지 않는 번호를 찾아서 생성하기가 쉽지 않습니다!

Oracle에선 다행히 자동으로 숫자를 생성해주는 시퀀스가 존재합니다!

시퀀스는 다음과 같이 생성합니다! 


CREATE SEQUENCE EMP_SEQ
INCREMENT BY 1 -- 얼마씩 증가할지
START WITH 1; -- 시작 번호



만들어진 시퀀스는 데이터 딕셔너리에 저장됩니다! 


SELECT
    *
FROM
    user_sequences;



시퀀스를 생성하고 번호를 발생시키려면 시퀀스명.NEXTVAL 하면 됩니다! 


SELECT
    emp_seq.NEXTVAL
FROM
    dual;




image



시퀀스명.nextval 이 호출될 때 마다 시퀀스 값은 증가해서 생성됩니다!

시퀀스의 현재 값을 보려면 시퀀스명.CURRVAL 하면 됩니다. 


SELECT
    emp_seq.NEXTVAL,
    emp_seq.CURRVAL
FROM
    dual; --현재 squence 값 조회




image



조심할 것은 시퀀스명.NEXTVAL을 포함한 코드가 컴파일 에러가 발생해도 시퀀스의 값은 증가합니다~!

또, 시퀀스 값은 테이블에 독립적입니다!

즉, 하나의 시퀀스를 여러 테이블이 공용해서 사용가능합니다!

그리고 시퀀스 값을 초기화하려면 시퀀스를 제거하고 다시 생성할 수 밖에 없습니다!

롤백한다고 해서 이전 값으로 돌아가지 않습니다~

시퀀스를 제거할 때는 DROP문을 이용합니다! 


DROP SEQUENCE emp_seq;



NEXTVAL, CURRVAL을 사용할 수 있는 경우



시퀀스의 값을 사용할 수 있는 경우는 다음과 같습니다.


  • 서브쿼리가 아닌 SELECT문



  • INSERT문의 SELECT문, VALUES 절



  • UPDATE의 SET 절






다음은 사용하지 못하는 경우입니다!


  • VIEW의 SELECT문



  • DISTINCT가 있는 SELECT 문



  • GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문



  • 서브쿼리



  • CREATE TABLE, ALTER TABLE 등




어차피 시퀀스는 99.9% INSERT 연산과 같이 사용되기에 INSERT 문에만 잘 사용하시면 됩니다!


MAXVALUES n, MINVALUES n



시퀀스를 생성할 때도 다양한 옵션이 있는데요~

MAXVALUES와 MINVALUES는 시퀀스의 최대값과 최소값을 정해줍니다!

한 번 예제를 통해 살펴보죠!

다음과 같이 DEPT01 테이블을 정의하겠습니다. 


CREATE TABLE dept01 (
    deptno     NUMBER,
    deptname   VARCHAR2(30)
);



그리고 시퀀스를 생성하겠습니다! 


CREATE SEQUENCE dept_seq 
INCREMENT BY 10 
START WITH 10 
MAXVALUE 30; --최대값 30



시퀀스를 이용해 데이터를 넣어보죠! 


INSERT INTO dept01 VALUES (
    dept_seq.NEXTVAL,
    '개발부'
);

INSERT INTO dept01 VALUES (
    dept_seq.NEXTVAL,
    '편집부'
);

INSERT INTO dept01 VALUES (
    dept_seq.NEXTVAL,
    '판매부'
);




image



잘 들어간 것을 볼 수 있습니다!

이 때 다음과 같이 INSERT 문을 사용하면 에러가 발생됩니다. 


INSERT INTO dept01 VALUES (
    dept_seq.NEXTVAL,
    '인사부'
); --오류




image



그 이유는 시퀀스를 생성할 때 정해준 최대값 30을 넘어버렸기 때문입니다~

이 때 시퀀스를 수정하여 최대값을 늘려주면 데이터 삽입이 가능합니다! 


ALTER SEQUENCE dept_seq MAXVALUE 100; -- 최대값 100으로 증가

INSERT INTO dept01 VALUES (
    dept_seq.NEXTVAL,
    '인사부'
);




image



CACHE



CACHE 옵션은 메모리상에서 시퀀스 값을 관리하도록 하는 것입니다.

기본값은 20이며, CHCHE 옵션을 사용하지 않으려면 NOCACHE 라고 해주시면 됩니다! 


CREATE SEQUENCE dept_seq 
INCREMENT BY 10 
START WITH 10 
CACHE 10;





CREATE SEQUENCE dept_seq 
INCREMENT BY 10 
START WITH 10 
NOCACHE;




CYCLE



CYCLE 옵션은 만약 시퀀스의 값이 최대값을 넘으면 다시 최소값으로 되돌아가게 하는 것입니다! 


CREATE SEQUENCE dept_seq02 
INCREMENT BY 10 
START WITH 10 
MAXVALUE 30 
MINVALUE 10 --cycle이 돌았을 때 최소값으로 이동
NOCACHE --CYCLE을 이용하려면 캐시보다 크게 max를 잡아주어야함 아니면 캐쉬 사용하지 않아야함
CYCLE;



한 번 실습해볼까요??

위의 시퀀스를 이용해서 dept02 테이블에 데이터를 삽입하겠습니다! 


CREATE TABLE dept02 (
    deptno     NUMBER,
    deptname   VARCHAR2(30)
);





INSERT INTO dept02 VALUES (
    dept_seq02.NEXTVAL,
    '개발부'
);

INSERT INTO dept02 VALUES (
    dept_seq02.NEXTVAL,
    '편집부'
);

INSERT INTO dept02 VALUES (
    dept_seq02.NEXTVAL,
    '판매부'
);

INSERT INTO dept02 VALUES (
    dept_seq02.NEXTVAL,
    '인사부'
);




image