오라클 - Sequence

시퀀스란?  ◈ 유일(UNIQUE)한 값을 생성해주는 오라클 객체입니다.  ◈ 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할수 있습니다.  ◈ 보통 primary key 값을 생성하기 위해 사용합니다.  ◈ 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가 합니다.  ◈ Sequence는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 sequence를      여러 테이블에서 쓸 수 있습니다.   시퀀스 생성  START WITH : 시퀀스의 시작 값을 지정합니다. n을 1로 지정하면 1부터 순차적으로                       시퀀스번호가 증가 합니다.  INCREMENT BY : 시퀀스의 증가 값을 말합니다. n을 2로 하면 2씩 증가합니다.                          START WITH를 1로 하고 INCREMENT BY를 2으로 하면 1, 3, 5,7,.. 이렇게                         시퀀스  번호가 증가하게 됩니다.  MAXVALUE n | NOMAXVALUE : MAXVALUE는 시퀀스가 증가할수 있는 최대값을 말합니다.                                             NOMAXVALUE는 시퀀스의 값을 무한대로 지정합니다.  MINVALUE n | NOMINVALUE : MINVALUE는 시퀀스의 최소값을 지정 합니다.                                        기본값은 1이며, NOMINVALUE를 지정할 경우 최소값은 무한대가 됩니다     SQL>CREATE SEQUENCE emp_seq          START WITH 1          INCREMENT BY 1          MAXVALUE 100000 ;        sequence created.       시작 값이 1일고 1씩 증가하고, 최대값이 100000만이 되는 시퀀스를 생성했습니다.   SQL>INSERT INTO emp(empno, ename, hiredate ) VALUES(emp_seq.NEXTVAL, 'julia' , sysdate);      empno는 컬럼값을 입력할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여      자동으로 입력할 수 있습니다.    CURRVAL : 현재 값을 반환 합니다. .     NEXTVAL : 현재 시퀀스값의 다음 값을 반환 합니다.   SQL>SELECT emp_seq.CURRVAL FROM DUAL ;          CURRVAL          ---------                    1   SQL>SELECT emp_seq.NEXTVAL FROM DUAL ;         NEXTVAL       ---------                   2   사용규칙    ◈ NEXTVAL, CURRVAL을 사용할 수 있는 경우     - subquery가 아닌 select문     - insert문의 select절     - insert문의 value절     - update문의 set절   ◈ NEXTVAL, CURRVAL을 사용할 수 없는 경우     - view의 select절     - distinct 키워드가 있는 select문     - group by, having, order by절이 있는 select문     - select, delete, update의 subquery     - create table, alter table 명령의 default값 시퀀스의 수정 및 삭제  START WITH는 수정할수 없습니다.  START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같습니다.     SQL>ALTER SEQUENCE emp_seq           INCREMENT BY 2           CYCLE;        sequence altered.        2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정하였습니다.        DROP 문을로 필요하지 않은 시퀀스는 삭제 할수 있습니다.   SQL>DROP SEQUENCE PRD_SEQ;          sequence dropped.

================================================      * 오라클 정보공유 커뮤니티 oracleclub.com      * http://www.oracleclub.com      * http://www.oramaster.net      * 운영자 : 김정식 (oramaster _at_ naver.com)   ================================================

※ oracleclub 강좌를 다른 홈페이지에 퍼가실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

 

 

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

 

오라클 자동증가(Create sequence) 오라클 시퀀스 생성

 

MS-SQL에서 테이블 생성시 자동증가할수 있는 명령어가 있습니다.
근데 오라클은 없냐고 반문하시면 비슷하게 한번 만들어 보겠습니다.

 

아래와 같이 SEQUENCE 를 생성하고 인서트할때 시퀀스명.NEXTVAL을 이용하면 됩니다.

 

 

1. SEQUENCE 생성

   create sequence autonum 
     increment by 1   -- 1씩 증가하라는거고요
     start with 1        -- 1부터 시작이고요 100 시작할려면 100 이라고 쓰시면 됩니다.
     nomaxvalue       -- 최대값 제한이 없다
     nocycle             
     nocache;
 
DEFAULT VALUE를 이용할려고 했는데 이것도 안됩니다.

create table test(num number default autonum.nextval, name varchar2(10)); 
1행에 오류:
ORA-00984: 열을 사용할 수 없습니다

 

 

 

2. TABLE 생성

  create table test(num number, name varchar2(10));

 

 

 

3. 자동증가값 생성

  입력시 다음과 같이 해주시면 됩니다.
  insert into test (num)  values (autonum.nextval);

 

조회 해보면 1 값이 들어가 있습니다.

select * from test

       NUM NAME
---------- ----------
         1

 

삭제는 drop sequence autonum; 이렇게 하시면 됩니다.

 

유의할 사항은 시퀀스명.Nextval 하면 전에 값으로 되돌릴수 없습니다.

현재값은 시퀀스명.CurrVal 로 조회 하시면 됩니다.

 

select 시퀀스명.currval from dual;

 

 

 

 

 

 

 

# SEQUENCE - 같은말 반복

 [ 대부분 SEQ 사용 ]
-----------------------------------------------------------------------------
CREATE SEQUENCE SEQ1
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE

 

 [ 1 ] SEQUENCE 생성 -> 하나의 Object
-----------------------------------------------------------------------------

CREATE SEQUENCE SEQ1
START WITH 1              // 처음 시작값
INCREMENT BY 1         // 1씩 증가
MAXVALUE 1E27          // 최대값
MINVALUE 1                // 최소값(다시 시작할때는 1부터 시작)
CYCLE                       // 최대값이 지나면 다시 시작해서 사용
NOCACHE                  // 캐쉬상 메모리에 올려놓지 않는다

 

 

 [ 2 ] 지우거나 수정
-----------------------------------------------------------------------------

DROP SEQUENCE SEQ1;

ALTER SEQUENCE SEQ1 CYCLE;
-> START WITH는 ATER로 변경할 수 없다.

 

 

 [ 3 ] 테이블 생성 및 SEQ1 적용
-----------------------------------------------------------------------------

CREATE TABLE SEQTEST (
             NO NUMBER, 
             NAME VARCHAR2(10)

);

CREATE TABLE SEQTEST2
AS
SELECT * FROM SEQTEST;

 

 

 [ 4 ] SEQUENCE의 마지막 값 조회
------------------------------------------------------------------------------

SELECT LAST_NUMBER

   FROM USER_SEQUENCES
 WHERE SEQUENCE_NAME = 'SEQ1';

 

 

 [ 5 ] SEQUENCE의 현재 값 조회
------------------------------------------------------------------------------

SELECT SEQ1.CURRVAL

   FROM DUAL;

 

 

 

 

 ▶ SEQUENCE를 사용할 수 없는 경우 : SEQUENCE는 PL/SQL과 SQL 코딩시 몇몇 제한되게 사용됨.
-----------------------------------------------------------------------------------------------

 

 ex 1 )

 

INSERT INTO TEST (AAA, BBB)

SELECT AAA, TEST_SEQ.NEXTVAL

   FROM TEST

 ORDER BY BBB;

[ORA-02287: sequence number not allowed here] 라는 에러를 발생할 것이다.

 

 ex 2 )

 

SELECT TEST_SEQ.NEXTVAL FROM DUAL

UNION ALL

SELECT TEST_SEQ.NEXTVAL FROM DUAL 

 

[ORA-02287: sequence number not allowed here]

 

 

 ex 3 )

 

SELECT COUPON_STR,
             COUPON_SEQ 
   FROM AA
 WHERE GAMEID = 'high'
      AND COUPON_SEQ  = AA_SQ1.NEXTVAL

 

 

 ex 4 ) 등등.. 그룹함수도 마찬가지...

 

 

 ▶ SEQUENCE 사용 불가

 SELECT 문

 

    1. WHERE 절 안에서
    2. GROUP BY나 ORDER BY 절에서
    3. DISTINCT 절에서
    4. UNION 이나 INTERSECT 나 MINUS 와 함께 사용 할때
    5. 서브 쿼리 안에서

 기타

 

    6. UPDATE나 DELETE 의 서브쿼리
    7. VIEW나 SNAPSHOPT 에서
    8. 테이블 정의시 CHECK 조건이나 DEFAULT 에서

 

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

 

 회피 방법 생각해 볼 문제....

 

 

 

 

 

 ▶ SEQUENCE 초기화

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

 ※ 힌트 : 증가된 시퀀스번호만큼 빼주어 값을 초기화하는 프로지져를 생성한다.

     http://blog.naver.com/jadin1 참고.

 

CREATE OR REPLACE PROCEDURE P_RESET_SEQ(SEQ_NAME IN VARCHAR2)
IS
  L_VAL NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || SEQ_NAME || '.NEXTVAL FROM DUAL ' INTO L_VAL;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SEQ_NAME || ' INCREMENT BY -' || L_VAL || ' MINVALUE 0';
  EXECUTE IMMEDIATE 'SELECT ' || SEQ_NAME || '.NEXTVAL FROM DUAL ' INTO L_VAL;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SEQ_NAME || ' INCREMENT BY 1 MINVALUE 0';
END;

출처 : Tong - 병조님의 Oracle통