[Oracle] SQL문법 11. SEQUENCE

SEQUENCE

자동으로 숫자를 생성하는 객체이다
정수값을 자동으로 순차적으로 발생시켜주며 보통 기본키(PRIMARY KEY)로서 활용한다

※ 오라클에서는 자동증가 컬럼을 사용할 수가 없다
다른 DMBS에서는 컬럼 자체에 옵션이 있으나, 오라클에서는 컬럼의 값을 증가시키기 위해서 MAX(컬럼)+1 또는 SEQUENCE를 사용하여 일련번호를 부여해주어야 한다

SEQUENCE 생성

[표현법]
CREATE SEQUENCE 시퀀스명
[ START WITH 시작숫자 ]
[ INCREMENT BY 증가값 ]
[ MAXVALUE 최대값 | NOMAXVALUE ]
[ MINVALUE 최소값 | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE | NOCACHE ]

-- 예시1
CREATE SEQUENCE SEQ_TEST;

-- 예시2
CREATE SEQUENCE SEQ_TEST2
START WITH 300
INCREMENT BY 5
MAXVALUE 510
NOCYCLE
NOCACHE;

• START WITH

  • 시퀀스의 시작값을 지정한다
  • DEFAULT = 1

• INCREMENT BY

  • 한번 시퀀스가 증가할 때 얼마씩 증가할지 증가값을 지정한다
  • 음수도 가능하다
  • DEFAULT = 1

• MAXVALUE

  • 최대값 또는 무한대(NOMAXVALUE)로 지정한다
  • 10의 27승 -1까지 지정 가능하다
  • 시작숫자와 같거나 커야하고 MINVALUE보다 커야 한다
  • DEFAULT = 9999999999999999999999999999

• MINVALUE

  • 최소값 또는 무한대(NOMINVALUE)로 지정한다
  • -10의 26승까지 지정 가능하다
  • 시작숫자와 같거나 작아야하고 MAXVALUE보다 작아야 한다
  • DEFAULT = 1

• CYCLE

  • 최대값 도달 시 순환여부를 지정한다
  • NOCYCLE은 증가 완료 시 에러를 유발한다
  • CYCLE은 값을 START WITH 설정 값으로 돌아가게 만들며 다시 증가를 시작하도록 한다(=순환)

• CACHE

  • 메모리상에서 시퀀스 값을 관리(CACHE)할지 여부를 정한다
  • 캐시 메모리 지정 여부를 정하며 CACHE_SIZE의 기본값은 20 BYTE이다
  • 원하는 숫자만큼 미리 만들어 캐시로 상주시킬 수 있다
    => 시퀀스로부터 발생될 값들을 미리 생성해서 저장해두는 것
  • NOCACHE는 캐시 메모리를 지정하지 않게 한다
  • CACHE를 사용하면 시퀀스 값의 액세스 효율이 사용하지 않았을 때보다 증가한다
    => 단, 접속이 끊기고 나서 재접속하게되면 기존에 생성되어있던 값들은 제거된다
  • 캐싱을 하는 것이 퍼포먼스상으로 우위에 있지만 연속된 번호를 얻어야 한다면 NOCACHE가 바람직 하다
    => 캐싱이 AGING-OUT되거나 DB의 비정상적 종료로 채번이 불연속적(중복은 아님)으로 될 수 있기 때문이다

CURRVAL 과 NEXTVAL

[표현법]
시퀀스명.CURRVAL
시퀀스명.NEXTVAL

-- 예시
SELECT SEQ_TEST.NEXTVAL FROM DUAL;
SELECT SEQ_TEST.CURRVAL FROM DUAL;

-- INSERT문 예시
INSERT INTO TEST_TABLE(A_COLUMN, B_COLUMN)
VALUES (SEQ_TEST.NEXTVAL, 'TEST');

-- 데이터 딕셔너리로 시퀀스 정보 조회
SELECT * FROM USER_SEQUENCES;

• CURRVAL

  • 현재 시퀀스의 값(= 마지막으로 성공적으로 발생된 NEXTVAL의 값)이다
  • 시퀀스 생성 후 최소 NEXTVAL이 한번은 실행되어야만 CURRVAL 수행이 가능하다
    => CURRVAL은 마지막으로 성공적으로 수행된 NEXTVAL의 값을 저장해서 보여주는 임시 값이기 때문이다

• NEXTVAL

  • 시퀀스 생성 후 첫 NEXTVAL은 START WITH으로 지정된 시작값으로 발생된다
  • 그 외 NEXTVAL은 현재 시퀀스의 값을 증가시키고 그 증가된 시퀀스의 값이다
    => == 시퀀스명.CURRVAL + INCREMENT_BY로 증가된 값

• 데이터 딕셔너리

  • 해당 시퀀스의 정보를 표시
  • LAST_NUMBER : 현재 시퀀스에 앞으로 NEXTVAL을 수행하면 반환시킬 값을 표시한다

• NEXTVAL/CURRVAL 사용 가능 여부표

  사용가능	사용불가
  서브쿼리가 아닌 SELECT문	VIEW의 SELECT절
  INSERT문의 SELECT절	DISTINCT 키워드가 있는 SELECT문
  INSERT문의 VALUE절	GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문
  UPDATE문의 SET절	SELECT, DELETE, UPDATE의 서브쿼리
  	CREATE TABLE, ALTER TABLE 명령의 DEFAULT값

SEQUENCE 변경

[표현법]
ALTER SEQUENCE 시퀀스명
[ INCREMENT BY 증가값 ]
[ MAXVALUE 최대값 | NOMAXVALUE ]
[ MINVALUE 최소값 | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE | NOCACHE ]

-- 예시
ALTER SEQUENCE SEQ_TEST
INCREMENT BY 10
CACHE 40;

• START WITH 값을 제외한 다른 설정들을 수정 가능하다
  => START WITH값은 변경 불가로 바꾸고 싶다면 시퀀스 삭제 후 다시 생성해주어야 한다

• 시퀀스의 삭제는 DROP SEQUENCE 시퀀스명;으로 진행한다