시퀀스(SEQUENCE): 자동 번호 발생기

시퀀스(SEQUENCE)

숫자를 자동으로 생성하는 자동번호 발생기. 오라클에서 행을 구분하는 기본 키가 항상 유일한 값을 갖도록 테이블 내의 유일한 숫자를 자동으로 생성해주는 것이 시퀀스로, 시퀀스를 기본 키로 사용하면 사용자의 부담을 줄일 수 있다.

게시판에 글쓸 때 게시판 새 글번호, 사원 데이터를 추가할 때 새 사번 등.

시퀀스를 쓰지 않는 경우에는

SELECT MAX(EMPNO)+1 FROM EMP -- 새 사원번호는 기존 사원번호+1

1. 시퀀스의 형식

CREATE SEQUENCE sequence_name

[START WITH n]

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]

1) START WITH n : 시퀀스 번호의 시작값 지정

2) INCREMENT BY n : 연속적 시퀀스 번호의 증가치

3) MAXVALUE n | NOMAXVALUE : 시퀀스 최대값을 지정

NOMAXVALUE: 오름차순 ASC일 때 1027승, 내림차순 DESC일 때 -1

4) MINVALUE n | NOMINVALUE : 시퀀스 최소값을 지정.

NOMAXVALUE: 오름차순 ASC일 때 1, 내림차순 DESC일 때 1026승

5) CYCLE | NOCYCLE : 시퀀스가 최대값까지 완료하면 시작값에서 다시 시퀀스 시작

NOCYCLE은 증가가 완료 되면 에러 발생

6) CACHE n | NOCACHE : 메모리상의 시퀀스값 관리(기본값은 20)

NOCACHE는 원칙적으로 메모리상에서 시퀀스를 관리하지 않는다.

예제) 부서번호를 자동으로 부여해주는 시퀀스 객체를 생성

CREATE SEQUENCE DEPTNO_SEQ

INCREMENT BY 10

START WITH 1;

-- 시작값이 1이고 10씩 증가하는 시퀀스 EMP_SEQ를 생성한다.

2. 시퀀스 관련 데이터 딕셔너리

DESC USER_SEQUENCES

-- 데이터 딕셔너리 USER_SEQUENCES의 테이블 구조 살펴보기

이름            널        유형           

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

SEQUENCE_NAME NOT NULL VARCHAR2(30) 

MIN_VALUE              NUMBER       

MAX_VALUE              NUMBER       

INCREMENT_BY  NOT NULL NUMBER       

CYCLE_FLAG             VARCHAR2(1)  

ORDER_FLAG             VARCHAR2(1)  

CACHE_SIZE    NOT NULL NUMBER       

LAST_NUMBER   NOT NULL NUMBER      

CREATE SEQUENCE EMP_COPY_SEQ;

-- 시퀀스를 생성

SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG

FROM USER_SEQUENCES;

-- 데이터 딕셔너리 USER_SEQUENCES로 현재 사용 중인 시퀀스 객체의 정보를 살펴본다.

3. 시퀀스 값을 알아보는 CURRVAL과 NEXTVAL

CURRVAL : 현재 시퀀스 값

NEXTVAL : 다음 시퀀스 값

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

- 서브쿼리가 아닌 SELECT문

- INSERT문의 SELECT절

- INSERT문의 VALUE절

- UPDATE문의 SET절

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

- VIEW의 SELECT절

- DISTINCT 키워드가 있는 SELECT문

- GROUP BY, HAVING, ORDER BY절이 있는 SELECT문

- SELECT, DELETE, UPDATE의 서브쿼리

- CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값

SELECT EMP_COPY_SEQ.NEXTVAL FROM DUAL;

SELECT EMP_COPY_SEQ.NEXTVAL FROM DUAL; -- 실행할 때마다 값이 증가한다.

1번 실행했을 때엔 1, 2번 실행하면 2...실행 때마다 시퀀스 값이 계속 증가하는 것을 확인할 수 있다.

SELECT EMP_COPY_SEQ.CURRVAL FROM DUAL;

현재 시퀀스 값을 확인한다.

4. 시퀀스 실무에 적용하기

SELECT * FROM EMP_COPY;

INSERT INTO EMP_COPY VALUES(

EMP_COPY_SEQ.NEXTVAL, 'KIM', 'SALESMAN', 7698, '1982-03-01', 700, NULL, 20);

-- EMPNO를 시퀀스로 자동생성. 맨 아래에 추가된다.

5. 시퀀스 삭제 DROP SEQUENCE

SELECT SEQUENCE_NAME, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG

FROM USER_SEQUENCES;

DROP SEQUENCE EMP_COPY_SEQ;

Sequence EMP_COPY_SEQ이(가) 삭제되었습니다.

6. 시퀀스 수정 ALTER SEQUENCE

CREATE SEQUENCE DEPT_DEPTNO_SEQ

START WITH 10

INCREMENT BY 10

MAXVALUE 30;

-- 시퀀스 최대값이 30인 시퀀스 생성

SELECT SEQUENCE_NAME, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG

FROM USER_SEQUENCES;

-- 생성된 최대값 30의 시퀀스 확인.

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

-- 부서번호를 계속 생성한다. 10, 20, 30으로 증가하다가 4번째에서 오류가 발생한다.

(CYCLE 옵션을 지정하지 않았으므로 기본 NOCYCLE이므로 오류가 발생한다.)

ALTER SEQUENCE DEPT_DEPTNO_SEQ

MAXVALUE 1000;

-- 시퀀스 최대값을 1000으로 수정

SELECT SEQUENCE_NAME, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG

FROM USER_SEQUENCES;

-- 최대값이 1000으로 변경된 시퀀스 확인.