Oracle Sequence

Sequenc 생성

자동적으로 순차적인 숫자를 생성하기 위해 Sequenc를 정의한다.

CREATE SEQUENC NAME
　　[INCREMENT BY n]
　　[START WITH n]
　　[{MAXVQLUE n | NOMAXVALUE}]
　　[{MINVALUE n | NOMINVALUE}]
　　[{CYCLE | NOCYCLE}]
　　[{CACHE n | NOCACHE}]

Sequenc 생성

여기서,

• INCREMENT BY n은 Sequenc번호간의 간격인 정수 n이며, 생략되면 Sequenc는 1씩 증가
• START WITH n은 생성되는 첫 번째 Sequenc번호로 생략되면 시퀀스는 1씩 증가
• MAXVALUE n은 생성가능한 Sequenc의 최대값
• NOMAXVALUE는 기본 옵션으로 최대값은 1027
• MINVALUE n은 생성가능한 Sequenc의 최소값
• NOMINVALUE최소값1
• CACHE n|NOCACHE는 Oracle7 Sever가 미리 지정하고 메모리에 유지할 값의 수로 Default값을 20이다.자세한 설명은, Oracle7 Server SQL Reference, Release 7.2, "CREATE SEQUENCE"를 보십시오.

Sequence 생성 : 예

질의 예제 : s_dept 테이블의 dept_id column 에 사용할 sequence인 s_dept_id를 만들라.sequence는 51에서 시작 한다.캐쉬와 cycle을 하지 않게 하라.

SQL> CREATE SEQUENCE s_dept_id
	2      INCREMENT BY      1
	3      START WITH      51
	4      MAXVALUE      9999999
	5      NOCACHE
	6      NOCYCLE;
	
	Sequence created.

※Sequence가 Primary Key값을 생성한다면 CYCLE 옵션을 쓰지 말라.

Sequence 확인

　
• Sequence를 생성하면 data dictionary에 기록된다.
　
• Sequence가 데이터베이스 객체이므로 USER_OBJECTS dictionary에서 볼 수 있다.
　
• 데이터 사전의 USER_SEQUENCES 테이블을 참조하면 설정된 사항을 확인할 수 있다.

질의 예제 : 자신이 소유한 모든 Sequence 정보를 출력하라.

SQL> SELECT sequence_name, min_value, max_value,
		2	increment_by, last_number
		3	FROM    user_sequences;

LAST_NUMBER column은 사용가능한 다음 순서 번호를 출력합니다.

Sequence 이용

• 시퀀스를 만들면 테이블에서 사용할 순차적인 번호를 생성하기 위해 시퀀스를 이용할 수 있다.
• NEXTVAL과 CURRVAL Pseudocolumn을 써서 Sequence 값을 참조하라.

질의 예제 : 지역2에 "Finance"라는 입력을 삽입하라.

SQL>INSERT INTO s_dept(id, name, region_id)
		2   VALUES (s_dept_id, NEXTVAL, 'Finance', 2):
		
		1 row selected.

질의 예제 : S_DEPT_ID Sequence에 대한 현재 값을 보라.

SQL>SELECT s_dept_id,CURRVAL
		2   FROM SYS,dual; 

NEXTVAL과 CURRVAL Pseudocolumn

• NEXTVAL은 사용 가능한 다음 시퀀스값을 반환한다. - 매번 고유한 값을 반환한다.
• CURRVAL은 현재 시퀀스값을 구한다. -CURRVAL은 NEXTVAL사용 후에 사용되어야 한다.
• 사용규칙을 따르라.

NEXTVAL과 CURRVAL 의사 Pseudocolumn

NEXTVAL Pseudocolumn은 명시된 Sequence에서 다음 값을 추출하는데에 사용된다. Sequence명.NEXTVAL을 참조하면 새로운 번호가 생성되고 CURRVAL은 현재의 시퀀스 번호 값을 갖는다. CURRVAL이 참조되기 전에 현 사용자의 세션에서 NEXTVAL이 이용되어야 합니다. Sequence명,CURRVAL이 참조되면 사용자 프로세스에 사용된 최종 Sequence값이 출력된다.

NEXTVAL과 CURRVAL 사용규칙

• 다음에서 NEXTVAL과 CURRVAL을 사용할 수 있다.
• - Subquery가 아닌 SELECT 문의 SELECT List - INSERT 문의 Subquery SELECT List - INSERT 문의 VALUE 절- UPDATE 문의 SET 절
• 다음에서 NEXTVAL과 CURRVAL을 사용할 수 없다.
• - 뷰의 SELECT List - DISTINCT 키워드가 있는 SELECT 문 - GROUP BY, HAVING, 또는 ORDER BY 절이 있는 SELECT 문 - SELECT, DELETE, 또는 UPDATE 문의 Subquery - CREATE TABLE 또는 ALTER TABLE 명령의 DEFAULT 값

※자세한 설명은 Oracle7 Server SQL Reference, Release 7.2, "Pseudocolumn" 절과 "CREATE SEQUENCE"을 보라

Sequence 값의 캐슁

Sequence를 메모리에 캐쉬하면 Sequence 값을 더 빨리 액세스할 수 있다. 다음 Sequence값을 요구하면 캐쉬된 Sequence에서 return해 준다.

증가 없이 다음 시퀀스 값 보기

NOCACHE로 Sequence가 생성될 경우에만 USER_SEQUENCES 테이블을 query를 하여 다음 Sequence값을 볼 수 있다.

Sequence 변경

• 증가값, 최대 값, 최소 값, 순환옵션, 캐쉬 옵션을 변경한다.
• Sequence에 대한 MAXVALUE 한계에 도달하면 Sequence에 대한 추가적인 값이 할당되지 않게 되고 Sequence가 MAXVALUE를 넘었음을 알리는 오류 메시지를 받게 됩니다. 이때Sequence를 계속 사용하려면 ALTER SEQUENCE 명령을 써서 수정할 수 있다.

Syntax

ALTER SEQUENCE NAME 
		[INCREMENT BY n]
		[{MAXVALUE n │ NOMAXVALUE}]
		[{MINVALUE n │ NOMINVALUE}]
		[{CYCLE │ NOCYCLE}]
		[{CACHE n│ NOCACHE}] 

Guidelines

• 자신의 Sequence이거나 Sequence에 대한 ALTER 권한을 가져야만 Sequence를 수정할 수 있다.
• 아직 생성되지 않은 시퀀스값만이 ALTER SEQUENCE명령에 영향받는다.
• 유효성 검사를 하게 된다. 예를 들어 새로운 MAXVALUE는 현재의 순서 번호보다 작지 않아야 한다.
• START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없다. 다른 번호에서 다시 시작하려면 이전 시퀀스를 삭제하고 다시 생성해야 한다.

※자세한 설명은 Oracle7 Server SQL Reference, Release 7.2 "ALTER SEQUENCE"을 보라.

Sequence 삭제

• DROP SEQUENCE명령을 사용하여 Sequence를 삭제한다.
• Sequence가 삭제되면 더 이상 참조할 수 없다.
• Sequence를 제거할 DROP ANY SEQUENCE 권한을 가지고 있거나 그 시퀀스의 소유자이어야 한다.

SQL>DROP SEQUENCE s_dept_id;

Sequence dropped. 

Syntax

DROP SEQUENCE 시퀀스 이름; 

※자세한 설명은 Oracle7 Server SQL Reference, Release 7.2 "DROP SEQUENCE"를 보라.

sequence 요약

• Sequence 사용하여 테이블의 행에 대한 primary key 값을 자동으로 생성한다.
• 데이타베이스 객체인 Sequence를 다른 사용자들과 공유할 수도 있다.
• USER_SEQUENCES 테이블에 있는 정보를 참조할 수 있다.
• Sequence.NEXTVAL을 이용하여 다음 값을 Return 받는다.
• Sequence.CURRVAL을 이용하여 NEXTVAL 에 의해 Return된 마지막 값을 확인할 수 있다.

출처 : 쿠그닷넷 [오라클/OCP/10g/9i/SQL/oracle]