Oracle Sequence 만들기 (일련번호 생성하기)

데이터베이스에서 일련번호를 생성하는 것은 동시성 문제가 발생할 수 있어 간단한 작업이 아닙니다. 그래서 대부분의 데이터베이스에서는 일련번호 생성을 간단히 처리할 수 있는 기능들을 제공해 주고 있습니다.

MS-SQL 서버에서는 컬럼에 identify 속성을 지정할 수 있고, MySQL 에서는 컬럼에 auto_increment 속성을 지정할 수 있습니다. Oracle에서는 테이블 속성으로 제공하지 않고 별도의 Sequence 객체를 생성해서 이용할  수 있습니다.

이러한 데이터베이스에서 제공하는 기능을 사용하면 동시성 문제에 신경을 쓰지 않고 간단히 처리할 수 있지만, 하나의 데이터베이스에 종속되는 단점도 있습니다.

오라클에서는 일련번호를 생성할 수 있는 Sequece 사용법을 알아보겠습니다.

1. 시퀀스 생성

CREATE SEQUENCE  시퀀스명
    START WITH n
    INCREMENT BY n
    MAXVALUE n | NOMAXVALUE
    MINVALUE n | NOMINVALUE
    CACHE n
    NOORDER | ORDER
    NOCYCLE | CYCLE

- START WITH n : n = 초기화 값

- INCREMENT BY n : n = 증가값

- MAXVALUE n | NOMAXVALUE : n = 최대값 또는 NOMAXVALUE = 무한대 값

- MINVALUE n | NOMINVALUE : n = 최소값 또는 NOMINVALUE = 무한대 값

- CACHE n : 시퀀스를 빨리 제공하기 위해 메모리에 캐쉬 하는 갯수를 지정합니다. 기본값은 20입니다. (시스템이 비정상적으로 종료하게 되면 캐쉬가 사라지게 되어 캐시된값 다음부터 시작됩니다.)

- NOORDER | ORDER : 기본값은 NOORDER 입니다. 병렬서버를 사용할 경우 요청 순서에 따라 정확하게 시퀀스를 생성하기를 원할 때 ORDER로 지정합니다. 단일서버일 경우 이 옵션과 관계 없이 정확히 요청 순서에 따라 시퀀스가 생성됩니다.

- CYCLE | NOCYCLE : 기본값을 NOCYCLE 입니다. CYCLE로 지정하면 MAXVALUE에 도달 했을 때 다시 MINVALUE부터 시작하게 됩니다.

사용 예)

CREATE SEQUENCE SEQ_BOARD
    START WITH 1
    INCREMENT BY 1;

기본값이 적용되어 실제 생성된 코드는 다음과 같습니다.

CREATE SEQUENCE  SEQ_BOARD
    MINVALUE 1
    MAXVALUE 9999999999999999999999999999
    INCREMENT BY 1
    START WITH 1
    CACHE 20
    NOORDER
    NOCYCLE ;

2. 시퀀스 사용하기

- 시퀀스를 생성하기 위해서는 "시퀀스명.NEXTVAL" 로 사용합니다.

INSERT INTO TB_BOARD(NUM, NAME, TITLE) VALUES (SEQ_BOARD.NEXTVAL, '홍길동', '제목');

- 현재 생성된 시퀀스 값을 확인하기 위해서 "시퀀스명.CURRVAL" 을 사용할 수 있습니다.

SELECT SEQ_BOARD.CURRVAL FROM DUAL;

- 현재 데이터베이스 세션에서 .NEXTVAL 을 먼저 사용해야만 .CURRVAL 을 사용할 수 있습니다. 현재 세션에서 .NEXTVAL을 한번도 사용하지 않은 상태에서 .CURRVAL을 호출하면 다음과 같은 오류가 발생합니다.

ORA-08002: sequence SEQ_BOARD.CURRVAL is not yet defined in this session
08002. 00000 -  "sequence %s.CURRVAL is not yet defined in this session"
*Cause:    sequence CURRVAL has been selected before sequence NEXTVAL
*Action:   select NEXTVAL from the sequence before selecting CURRVAL

3. 시퀀스 수정

- START WITH 값을 제외하고 ALTER SEQUENCE 명령어로 수정 할 수 있습니다.

ALTER SEQUENCE 시퀀스명
    [INCREMENT BY n]
    [MAXVALUE n | NOMAXVALUE]
    [MINVALUE n | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE | NOCACHE]

※ 참고사항

시퀀스 값을 증가시키지 않고 현재값을 확인하는 방법으로 다음과 같은 시퀀스의 상태를 확인하는 쿼리를 사용한다는 내용이 많은데 시퀀스는 증가시키지 않고 현재값을 확인하는 방법은 없는 것 같습니다.

SELECT * FROM user_sequences WHERE SEQUENCE_NAME = 'SEQ_BOARD';

기본적으로 CACHE 가 2 이상 이어야 하므로 항상 CACHE만큼 미리 만들어져 있습니다. 그러므로 위의 쿼리에서 LAST_NUMBER 값으로 현재 시퀀스 값을 알 수 없습니다.

시퀀스의 값을 특정값으로 초기화 하는 방법으로 많이 예기되는 increment를 마이너스(-) 값으로 지정해서 .nextval 한 후에 다시 increment 를 1로 변경하는 방법도 CACHE로 인해 동작하지 않는것 같습니다.

시퀀스의 시작값을 변경하기 위해서는 DROP 후 START WITH 값을 새 값으로 주어 새로 생성하는 방법이 맞을것 같습니다.