[Oracle 12c] Sequence

 

데이터베이스를 사용할 때 순서가 필요한 많은 양의 데이터가 저장이 되는 경우가 상당히 많습니다.

예를 들어, 고객의 주문번호나 학번들처럼 고유한 번호로  중복되서는 안 되고 연속적인 번호가 필요합니다.

이 경우 시퀀스를 사용하면 간편하게 고유하고 순차적인 번호를 자동으로 받아 처리할 수 있습니다.

 

정리하자면 시퀀스는 자동으로 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체입니다.

 

문법

CREATE SEQUENCE sequence_name
	   [INCREMENT BY n] --시퀀스 번호의 증가 값으로 기본값은 1이다.
       [START WITH n] -- 시퀀스 시작 번호로 기본값은 1이다.
       [MAXVALUE n | NOMAXVALUE] -- 생성할 수 있는 시퀀스 최대값이다
       [MINXVALUE N | NOMINVALUE] -- CYCLE일 경우 새로 시작되는 값과 감소하는 시퀀스일 경우 최솟값
       [CYCLE | NOCYCLE] --시퀀스 번호를 순환 사용할 것인지 지정한다.
       [CACHE n | NOCACHE] -- 시퀀스 생성 속도를 개선하기 위해 캐시 여부를 지정한다.

 

그럼 이제 시작은 100부터 최댓값은 110까지 최댓값 도달 시 1부터 시작하도록 설정하고 캐시는 10개씩 하는 시퀀스를 만들어 봅니다.

 

CREATE SEQUENCE my_sequence
		INCREMENT BY 1
		START WITH 100
		MAXVALUE 110
		MINVALUE 1
		CYCLE
		CACHE 10;

 

그리고 생성한 시퀀스에 지금 어떤 번호가 만들어지고 어떤 번호가 나올지는 CURRVAL 함수와 NEXTVAL함수를 사용하여 조회하거나 사용할 수 있습니다.

SELECT my_sequence.nextval, my_sequence.currval FROM dual;

 

이제 해당 시퀀스를 PK로 사용하는 테이블을 하나 만들고 데이터를 입력하면서 잘 동작하는지 확인해 보겠습니다.

 

CREATE TABLE MY_TABLE(
	"no" NUMBER(4) PRIMARY KEY ,
	name varchar2(10)
);


BEGIN
	FOR i IN 1..10 LOOP
	 INSERT INTO MY_TABLE VALUES(my_sequence.nextval, TO_CHAR(i));
	 END LOOP
	COMMIT;
END;

SELECT * FROM MY_TABLE;

 

MAXVALUE에 도달했기 때문에 CYCLE이 돌 것입니다. 그럼 다음 값은 MINVALUE = 1 이여야 합니다.

여기서 문제가 발생했는데 조회를 위해 시퀀스의 다음 값을 호출하니까 의도치 않게 시퀀스의 값이 증가했습니다.

적절한 권한설정으로 시퀀스 사용을 INSERT 한정하지 않으면 PK의 값에 혼동을 줄 수 있는 것 같다고 생각됩니다.

SELECT my_sequence.nextval, my_sequence.currval FROM dual;

CYCLE옵션을 지정하지 않으면 기본값은 NOCYCLE값인데 이러면 최댓값에 도달 시 ORA-08004 에러가 발생하면서 더 이상 번호가 발생되지 않습니다.

 

 

SEQUENCE 조회 및 수정

 

우선 현재 생성한 시퀀스가 어떤 상태인지 확인하는 SQL문은 다음과 같습니다.

SELECT * FROM user_sequences;

ALTER SEQUENCE my_sequence MAXVALUE 120 cache 5;

시퀀스의 속성값을 변경할 때 START WITH값은 변경할 수 없습니다.

 

 

아마 오라클에서 테스트 데이터베이스를 다운로드하여 사용할 경우 테이블을 만들고 DEFAULT값으로 시퀀스의 값을 주었을 때 에러가 발생합니다.

 

그 이유는 무료 버전은 11g 버전일 수 있습니다. default값으로 시퀀스를 적용하는 것은 12c버전부터 됩니다.

CREATE TABLE 12gORACLE(
	"NO" NUMBER(5) DEFAULT my_sequence.nextval PRIMARY KEY
);

 

만약 자신의 오라클 버전을 SQL문을 통해 쉽게 알고 싶다면 다음과 같은 쿼리를 사용해보자

SELECT * FROM PRODUCT_COMPONENT_VERSION;

그래서 난 안되더라

 

 

또한 데이터가 롤백시 시퀀스는 증가한 값을 유지하고 롤백이 되지 않는다.

12c에서 추가된 기능으로 IDENTITU Column을 지원한다는 것인데, 

CREATE TABLE 12gORACLE(
	"NO" NUMBER(5) GENERATED AS IDENTITY
);

이 기능은 오라클이 내부적으로 시퀀스를 생성해서 관리하게 됩니다.  

GENERATED AS IDENTITY는 해당 테이블에만 적용되며 모든 IDENTITY의 기본 속성을 사용하려면

CREATE TABLE 12gORACLE(
	"NO" NUMBER(5) GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 2)
);

다음과 같이 설정하고 START WITH절로 시작과 증가를 정할 수 있고 모든 IDENTITY의 기본값이 됩니다.