자바 프로그램 교육 :: [펌] oracle Sequence

[펌] oracle Sequence

DATABASE/Oracle 2008/09/05 12:36

☞ 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

♣ 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]