db/oracle

Oracle Sequence

C/H 2008. 9. 24. 19: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 
    [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]
    반응형