시퀀스란?
 

◈ 유일(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통

저작자 표시
신고

+ Recent posts