오라클 시퀀스 [펌]

[출처] http://cafe.naver.com/tonkjsp.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=362

[출처 및 참조]  http://blog.naver.com/c3mosi?Redirect=Log&logNo=60048716887

[출처 및 참조] * 오라클 정보공유 커뮤니티 oracleclub.com

                     * http://www.oracleclub.com

                     * http://www.oramaster.net

                     * 운영자 : 김정식

 

전에 SQL 쿼리 정리하면서 대략적으로 정리하였지만 여기서는 점더 집중적으로 정리하여 보겠습니다.

 

http://cafe.naver.com/tonkjsp/218

 

》오라클에서는 시퀀스

》MSSQL에서는 오라클의 시퀀스 대용으로 IDENTITY(초기값, 증가값)로 사용

   http://cafe.naver.com/tonkjsp/309

》MySQL에서는 오라클의 시퀀스 대용으로 auto_increment 를 사용합니다.

   다른 DB 서버에 비해 굉장히 좋지는 않습니다. 그래도 기능은 기능이니 한번 설명은 하고 넘어가 가겠습니다.

 

   CREATE TABLE 'tbl_name' (

       'column_name1' column_type  [column_constraint] auto_increment Primary Key,

       'column_name2' column_type 

     .....

   )

 

   위 쿼리에서 column_name1의 필드 타입이 int 라고 가정하면 이 필드에는 0부터 입력될 때마다 1씩 증가된 값이

   자동으로 활당하게 된다고 하네요 ^^

 

 [시퀀스 특징]

 

 ① 유일(UNIQUE)한 값을 생성해주는 오라클 객체다.

 ② 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할 수 있다.

     (MySQL은 불가능 하다, 즉 증가 값을 설정할 수 없습니다.)

 ③ 보통 PK 값을 생성하기 위해 사용됩니다.

 ④ 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가 합니다.

 ⑤ SEQUENCE는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 SEQUENCE를

     여러 테이블에서 쓸 수 있습니다.

     (오라클만 가지고 있는 독특한 특징 중 하나 입니다. 다른 DB 서버는 이렇게 되지 않더라고요)

 

 [시퀀스 생성]

 

  CREATE SEQUENCE seq_name

  [START WITH n]

  [INCREMENT BY n]

  [MAXVALUE n or NOMAXVALUE]

  [MINVALUE n or NOMINVALUE]

  [CYCLE or NOCYCLE]

 

   - START WITH : 시퀀스의 시작 값을 지정합니다. 

                          예) n을 1로 지정하면 1부터 순차적으로 시퀀스 번호가 증가합니다.

   - INCREMENT BY : 시퀀스의 증가 값을 말합니다.

                          예) n을 1로 하면 1씩 증가하고 2로 하면 2씩 증가 됩니다.

   - MAXVALUE : 시퀀스가 증가할 수 있는 최대값을 지정할때 사용한다.

   - NOMAXVALUE : 시퀀스의 값을 무한대로 지정할 때 사용한다.

   - MINVALUE : 시퀀스의 최소값을 지정할 때 사용한다.

   - NOMINVALUE : 기본값이 1 이며 , NOMINVALUE를 지정할 경우 최소값은 무한대가 된다.

 

  사용예)

  SQL> CREATE SEQUENCE tbl_seq

           START WITH 1

           INCREMENT BY 1

           MAXVALUE 100000;

           -- 시작 값은 1이고 1씩 증가하며, 최대값이 100000이 되는 시퀀스를 생성했다는 의미

 

  SQL> INSERT INTO tbl_name(col1,col2, col3) VALUES(tbl_seq.NEXTVAL,'JAVA','C++');

           -- tbl_name는 컬럼값을 읽할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여 자동으로 입력할

          수 있다.  MSSQL 나 MySQL은 별도의 필드를 지정하지 않고 바로 증가 시킨다.

   

  [CURRVAL, NEXTVAL]

 

  - CURRVAL : 현재 값을 반환 한다.

 

     예) TEST 위에서 작성된 시퀀스 사용

     SELECT  tbl_seq.CURRVAL FROM DUAL;

    

     ※ SEQUENCE의 마지막 값 조회

     SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME='seq_name';

 

  - NEXTVAL : 현재 시퀀스값의 다음 값을 반환한다.

                     유의할 사항이 있는데 , NEXTVAL 한번 하게하면 전에 값으로 되돌리는 것이 불가능하다.

   

    예) TEST 위에서 작성된 시퀀스 사용

    SELECT tbl_seq.NEXTVAL FROM DUAL;

 

  - 사용규칙

 

     ① NEXTVAL, CURRVAL을 사용할 수 있는 경우

 

        ㉠ subquery 가 아닌 select 문

        ㉡ insert문의 select 절

        ㉢ insert문의 value  절 ← 초보 개발자에게 가장 많이 쓰이는 방식이죠 ^^

        ㉣ update문의 set 절

        

     ② NEXTVAL, CURRVAL을 사용할 수 없는 경우

      

        ㉠ view의 select

        ㉡ distinct 키워드가 있는 select 문

        ㉢ group by, having , order by절이 있는 select 문 , union all 문 사용시

           예1)

           INSERT INTO tbl_name(col1, col2)  SELECT col1, seq_name.nextval from tbl_name order by col2

           예2)

           SELECT seq_name1.nextval FROM DUAL

           UNION ALL

           SELECT seq_name2.nextval FROM DUAL

 

        ㉣ select , delete , update의 subquery

        ㉤ create table, alter table 명령의 default 값

            예)

            CREATE TABLE tbl_name(

                 col1 number default seq_name.nextval,

                 col2 varchar2(10)

            )

            ORA-00984: 열을 사용할 수 없습니다. ← 에러메시지 ...

 

     [주의] 한 세션에서 CURRVAL, NEXTVAL는 적어도 INSERT가 한번이라도 발생해야 사용가능합니다.

 

 [시퀀스의 수정 및 삭제] 

 

 ALTER SEQUENCE seq_name

 [INCREMENT BY n]

 [MAXVALUE n or NOMAXVALUE]

 [MINVALUE n or NOMINVALUE]

 

 - INCREMENT BY : 시퀀스의 증가 값에 대한 수정 값

   예) n을 1로 하면 1씩 증가로 수정되고  2을 사용하면  2씩 증가로 변경된다.

 - MAXVALUE : 시퀀스가 증가할 수 있는 최대값을 수정할 때 사용한다.

 - NOMAXVALUE : 시퀀스의 값을 무한대로 수정할 때 사용한다.

 - MINVALUE : 시퀀스의 최소값으로 수정 할 때 사용한다.

 - NOMINVALUE : 기본값이 1 이며 , NOMINVALUE를 지정할 경우 최소값은 무한대가 수정할 수 있다.

 

  [주의] START WITH는 수정할수 없다.

  저 같은 경우는 그냥 수정보다는 삭제하고 다시하는 면이 더 편할 수 있습니다. 

  → seq_board_categroy,  seq_board_management 시퀀스명

 

  drop sequence seq_board_categroy;
  drop sequence seq_board_management;

 

  create sequence seq_board_categroy   start with 1 increment by 1 nocache;
  create sequence seq_board_management start with 1 increment by 1 nocache;

  

 DROP SEQUENCE seq_name

 

 사용예)

 SQL>ALTER SEQUENCE tbl_seq

          INCREMENT BY 2

         CYCLE ;

         -- 2씩 증가하도록 수정하고 최대값을 넘으면 다시 처음부터 순환하도록 수정한 것을 의미

 

 SQL> DROP SEQUENCE tbl_seq;

        -- 시퀀스 삭제