[SQL] 시퀀스

시퀀스의 개념

1. 고유한 정수 생성을 위해 사용하는 데이터베이스 객체
   1. 응용 프로그램에 코드를 작성하여 대체 가능
2. 기본키 값 생성에 소요되는 시간이 줄어드는 장점 제공
   1. 시퀀스 생성에 필요한 응용 프로그램 코드의 양을 줄여 주기 때문
3. 테이블에 독립적인 객체
   1. 여러 테이블에 동일한 시퀀스를 사용 가능 (권장하지 않음)

 

시퀀스의 생성

1. CREATE SEQUENCE 문을 사용하여 생성
2. 구문

CREATE SEQUENCE sequence_name
    [INCREMENT BY b]
    [START WITH n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}];

 

시퀀스의 생성 구문 설명

1. INCREMENT BY
   1. 시퀀스 번호 사이의 간격을 정수로 지정 (기본값 1)
2. START WITH
   1. 시퀀스 시작 번호를 지정 (기본값 1)
3. MAXVALUE, MINVALUE
   1. 시퀀스의 최댓값, 최솟값을 지정
4. CYCLE
   1. 시퀀스가 최댓값 또는 최솟값 도달 후 값을 순환
5. CACHE
   1. 번호를 메모리에 미리 캐시
   2. 기본값은 CACHE 20

 

• y_dept 테이블의 dept_id 열에 사용할 목적으로 dept_deptid_seq라는 이름의 시퀀스 생성, 이 시퀀스는 1000번으로 시작하며 100씩 증가하고 최대 9900번까지 저장할 수 있다. 또한 캐시를 허용하지 않고 순환하지 않는다.

CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 1000
MAXVALUE 9900
NOCYCLE
NOCACHE;

 

시퀀스의 사용

1. USER_SEQUENCES 데이터 딕셔너리 뷰에서 시퀀스 확인
2. 사용자는 NEXTVAL 및 CURRVAL 의사 열을 사용하여 시퀀스 값을 사용 또는 참조
   1. NEXTVAL 및 CURRVAL 의사열을 시퀀스 이름과 함께 사용
   2. NEXTVAL 의사 열은 시퀀스 번호를 할당하는 데 사용
   3. CURRVAL 의사열은 사용자가 방금 생성한 시퀀스 번호를 참조하는 데 사용

 

• user_sequences를 조회하여 dept_deptid_seq 시퀀스 설정을 확인

SELECT sequence_name, increment_by, last_number
FROM user_sequences
WHERE sequence_name = 'DEPT_DEPTID_SEQ';

 

• dept_deptid_seq 시퀀스를 사용하여 y_dept 테이블에 새 행을 입력

INSERT INTO y_dept(dept_id, dept_name)
VALUES(dept_deptid_seq.NEXTVAL, 'AS');
COMMIT;

SELECT * FROM y_dept;

 

• dual 테이블을 사용하여 sequence.CURRVAL을 참조하면 마지막으로 사용한 값이 표시된다.

SELECT dept_deptid_seq.CURRVAL FROM dual;

 

시퀀스의 공유

1. 서로 다른 여러 테이블에서 동일한 시퀀스 사용 가능
   1. 시퀀스가 하나의 테이블에 전용으로 연결되어 있는 것 아니기 때문
   2. 일반적으로 시퀀스는 용도에 따라 이름을 정하게 되지만 이름과 상관없이 어디에나 사용 가능
2. 개별 테이블 내에서는 시퀀스 번호에 공백이 생기는 원인
3. 테이블의 일련번호가 필요한 열마다 시퀀스를 별도로 생성하기를 권장

 

시퀀스 사용 SQL

1. 다음과 같은 경우에 NEXTVAL 및 CURRVAL을 사용할 수 있다.
   1. 서브 쿼리에 속하지 않은 SELECT 문의 SELECT 목록
   2. INSERT 문에 포함되는 서브 쿼리의 SELECT 목록
   3. INSERT 문의 VALUES 절
   4. UPDATE 문의 SET 절

 

시퀀스 사용의 고려사항

1. 다음과 같은 경우에는 NEXTVAL 및 CURRVAL을 사용할 수 없으므로 SQL 문장 작성 시 참조한다.
   1. 뷰를 정의하는 SELECT 절의 목록
   2. DISTINCT 키워드가 있는 SELECT 문
   3. GROUP BY, HAVING 또는 ORDER BY 절이 있는 SELECT 문
   4. SELECT, DELETE, UPDATE 문의 서브 쿼리
   5. TABLE의 DEFAULT 값

 

시퀀스에 공백(GAP)이 생기는 경우

1. CACHE 속성이 있는 시퀀스를 사용 시 데이터베이스 시스템에 장애 발생
2. 시퀀스를 포함하는 명령문을 ROLLBACK
3. 동일한 시퀀스를 여러 테이블에서 사용

 

• dept_deptid_seq 시퀀스를 사용해 y_emp 테이블에 새 행을 입력 후 확인

INSERT INTO y_emp(emp_id, emp_name)
VALUES(dept_deptid_seq.NEXTVAL, '노영준');
COMMIT;

SELECT emp_id, emp_name FROM y_emp
WHERE emp_name = '노영준';

 

시퀀스의 수정

1. ALTER SEQUENCE 명령문을 사용
   1. START WITH 옵션을 제외한 시퀀스 생성 시 지정한 모든 옵션 수정
      1. 시퀀스의 증분 값
      2. 최댓값, 최솟값
      3. CYCLE 옵션 또는 CACHE 옵션 변경
2. 시퀀스의 수정은 이후 시퀀스 번호에만 영향
3. 시퀀스 수정에 대한 검증 수행
4. 구문

ALTER SEQUENCE sequence_name
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

 

시퀀스의 삭제

1. DROP SEQUENCE 문을 사용
   1. 데이터 딕셔너리에서 시퀀스를 삭제
   2. 제거된 시퀀스는 더 이상 참조할 수 없으므로 시퀀스를 참조하던 응용프로그램의 수정 필요
2. 구문

DROP SEQUENCE sequence_name;

 

• dept_deptid_seq 시퀀스의 증분 값을 10으로, MAXVALUE를 20000으로 수정

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 10
MAXVALUE 20000;

 

• dept_deptid_seq 시퀀스 삭제

DROP SEQUENCE dept_deptid_seq;