[Oracle] 시퀀스(Sequence)

<SEQUENCE시퀀스>

- 숫자값이 일정한 규칙에 의해서 연속적으로 자동증가.

- 테이블에 종속적이지 않다(테이블과 독립적)

형식)

  CREATE SEQUENCE sequence_name  

                  [INCREMENT BY] 증가치

                  [START WITH] 초기값

                  [MAXVALUE n|NOMAXVALUE]

                  [MINVALUE|NOMINVALUE]

                  [CYCLE|NOCYCLE]

                  [CACHE|NOCACHE];

  maxvalue: 시퀀스가 가질 수 있는 최대값 지정.   

            nomaxvalue일 경우 ascending순서일 경우 10의 27승 

                              descending일 경우 -1           

  minvalue: 시퀀스가 가질 수 있는 최대값 지정.               

            nominvalue일 경우 ascending 1

                              descending 10의 26승으로 설정.

  cache: 메모리상의 시퀀스 값을 관리하도록 하는 것인데 기본값은 20.

         nocache는 메모리 상에서 시퀀스를 관리하지 않음.   

   

사용법)

    시퀀스명.nextval

    시퀀스명.currval

   

create sequence test_seq;  --기본 1부터 시작해서 1씩 증가되는 숫자값

 --> 시퀀스 생성.   

   

drop sequence test_seq;   

create sequence test_seq

       start with 1

       increment by 1

       nocycle

       nocache;

- 현재 숫자 보기(조회)

  select test_seq.currval

  from dual;

==> ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session  

    ---> 현재값 currval는 최소 한번 이상의 nextval실행 후 사용!!

    

- 자동 번호(숫자) 얻어오기

  select test_seq.nextval from dual;    

  select test_seq.nextval from dual;    

  select test_seq.nextval from dual;    

  select test_seq.nextval from dual;    

  select test_seq.nextval from dual;    

  ---> 5

  

  - 현재 숫자 보기

  select test_seq.currval from dual;    

   CURRVAL

----------

         5  

테이블에 적용한 사례)

   drop table dept3;

   create table dept3

   as select * from dept

      where 1=0;

   <dept3테이블 deptno에 primary key설정>

    alter table dept3

    add constraint dept3_pk primary key (deptno);

    ---> 중복된 부서번호, null 방지를 위해 primary key 설정.

   insert into dept3 values (10, '영업부', '가산'); 

   insert into dept3 values (20, '자재부', '대전'); 

   insert into dept3 values (30, '개발부', '교대'); 

   

SQL>select * from dept3;

    DEPTNO DNAME          LOC

---------- -----------------------

        10 영업부                       가산

        20 자재부                       대전

        30 개발부                       교대

  ~~~~~~~~  시간이 흘러    ~~~~~~~~~~~   

  

    데이터  ===> '기획부'    '서초'

  insert into dept3  (dname,loc) values ('기획부','서초');

  ---> ORA-01400: cannot insert NULL into ("SCOTT"."DEPT3"."DEPTNO")

  insert into dept3  (deptno, dname,loc) values (40,'기획부','서초');

SQL>select * from dept3;

    DEPTNO DNAME          LOC

---------- -----------------------

        10 영업부                       가산

        20 자재부                       대전

        30 개발부                       교대

        40 기획부                       서초

        

   drop table dept3;

   create table dept3

   as select * from dept

      where 1=0;

   <dept3테이블 deptno에 primary key설정>

    alter table dept3

    add constraint dept3_pk primary key (deptno);

    ---> 중복된 부서번호, null 방지를 위해 primary key 설정.

   <테이블내의 규칙적인 수를 표현 ---> sequence 객체 적용!!>

   create sequence dept3_seq

            start with 10     -- 시작

            increment by 10   -- 증가치

            nocycle

            nocache; 

   insert into dept3 values (dept3_seq.nextval, '영업부', '가산'); 

   insert into dept3 values (dept3_seq.nextval, '자재부', '대전'); 

   insert into dept3 values (dept3_seq.nextval, '개발부', '교대'); 

   

SQL> select * from dept3;

    DEPTNO DNAME          LOC

---------- ------------------------

        10 영업부                       가산

        20 자재부                       대전

        30 개발부                       교대

  ~~~~~~~~  시간이 흘러    ~~~~~~~~~~~   

  

    데이터  ===> '기획부'    '서초'

     

     

   insert into dept3 values (dept3_seq.nextval, '기획부', '서초');

   ----> 이전에 부여된 부서번호를 몰라도 된다!!

SQL> select * from dept3;

    DEPTNO DNAME          LOC

---------- ----------------------

        10 영업부                       가산

        20 자재부                       대전

        30 개발부                       교대

        40 기획부                       서초

* Oracle 11g에서 시퀀스 사용시 주의할 점!!

  deferred_segment_creation=true(기본값) ===> false로 변경

  

  alter system set deferred_segment_creation=false;    

  ORA-01031: insufficient privileges ==> 권한불충분

SQL> conn system/oracle    또는  conn /as sysdba

Connected.

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL> conn scott/tiger

Connected.