오라클에서 시퀀스는 고유하고 순차적인 번호를 부여할 수 있도록 지원하는 객체입니다. 특히 주문번호, 입금번호 등 특정 테이블에서 MAX값을 호출하는 경우에 데이터베이스 통신과 관련하여 값이 중복되는 등의 문제를 해결할 수 있도록 합니다. 오라클 시퀀스 생성, 수정, 삭제 구문을 알아보고 조회하는 방법에 대해 알아봅시다. 해당 게시물은 다음과 같은 목차로 진행하겠습니다. 😊
- 오라클 시퀀스 생성, 수정, 삭제, 조회 방법
- 시퀀스 생성
- 시퀀스 수정
- 시퀀스 삭제
- 시퀀스 조회
오라클 시퀀스 생성, 수정, 삭제, 조회 방법
오라클에서 시퀀스 객체는 데이터 정의 언어로 생성, 수정, 삭제합니다. 시퀀스를 조회하는 방법은 NEXTVAL와 CURRVAL라고 하는 의사칼럼(pseudocolumns)을 사용합니다.
시퀀스 생성
오라클 시퀀스 생성 예제 자료는 다음과 같습니다.
CREATE SEQUENCE USER_NAME.SEQUENCE_NAME
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;스키마.시퀀스명
스키마를 생략하면 현재 사용자로 지정이 됩니다. CREATE SEQUENCE [schema.]시퀀스명으로 데이터 정의를 설정합니다.
INCREMENT BY integer
시퀀스의 증감을 설정합니다. 기본값은 1이어서 생략하는 경우에는 1씩 증가하는 시퀀스를 생성합니다. 음수를 설정할 수 있으며, 이 경우에는 감소 시퀀스가 됩니다.
증감은 시퀀스의 최댓값 또는 최솟값보다 크거나 작을 수는 없습니다. 예를 들면 시퀀스의 최댓값이 100인데 증가치를 200으로 설정할 수는 없는 것이지요.
START WITH
시퀀스의 첫 시작 번호를 설정합니다. 생략하는 경우 증가 시퀀스는 시퀀스 최솟값, 감소 시퀀스는 최댓값입니다.
MAXVALUE
- MAXVALUE integer => 최댓값 설정
- NOMAXVALUE => 기본값
MINVALUE
- MINVALUE integer => 최솟값 설정
- NOMINVALUE => 기본값
CYCLE
- CYCLE => 시퀀스가 최대치 또는 최소치에 도달하는 경우 시퀀스 값이 처음부터 시작하도록 반복 여부를 설정합니다.
- NOCYCLE => 기본값
CACHE
- CACHE integer => 시퀀스에 대한 빠른 접근을 위하여 메모리에 선할당(preallocated) 하려는 시퀀스 개수를 설정합니다. 시퀀스 캐시 최대 할당 가능 값 : (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
- NOCACHE => 시퀀스를 선할당하지 않습니다. (not preallocated)
캐시를 설정하는 경우, 시스템 오류가 발생할 때 메모리에 할당된 커밋되지 않은 캐시 된 시퀀스 값은 없어지고 그 다음부터 할당됩니다.
캐시 설정을 생략하는 경우에는 시퀀스 생성 시 오라클에서 CACHE 20을 기본값으로 부여합니다.
ORDER
- ORDER => 시퀀스가 순차적으로 발생하도록 보장합니다. 메모리 선할당 캐시 옵션과 조합을 생각할 수 있습니다. CACHE + ORDER, CACHE + NOORDER, NOCACHE + ORDER, NOCACHE + NOORDER
- NOORDER => 기본값
시퀀스 생성 시 어떠한 값도 설정하지 않은 경우의 기본값은 CACHE + NOORDER입니다.
시퀀스 수정
ALTER 데이터 정의 언어로 설정합니다. 생성 시 설정한 구문을 조정할 수 있습니다.
- START WITH 구문을 설정이 불가합니다.
- CACHE integer에서 integer는 1보다 커야 합니다.
(ORA-04010: the number of values to CACHE must be greater than 1) - MAXVALUE가 MINVALUE보다 커야 한다거나, MINVALUE가 MAXVALUE보다는 작아야 한다는 등의 기본적인 사항도 있습니다.
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME INCREMENT BY 1;
--ORA-02283: cannot alter starting sequence number
--ALTER SEQUENCE USER_NAME.SEQUENCE_NAME START WITH 1;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME MAXVALUE 9999;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME NOMAXVALUE;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME MINVALUE -9999;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME NOMINVALUE;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME CYCLE;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME NOCYCLE;
--ORA-04010: the number of values to CACHE must be greater than 1
--ALTER SEQUENCE USER_NAME.SEQUENCE_NAME CACHE 1;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME CACHE 2;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME NOCACHE;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME ORDER;
ALTER SEQUENCE USER_NAME.SEQUENCE_NAME NOORDER;ORA-02283: cannot alter starting sequence number
START WITH 구문은 설정이 불가하기에 초기값을 다시 설정하려면 2가지 방법이 있습니다.
- 시퀀스를 드랍하고 새로 만드는 방법. 드랍 부분은 아래에 예제가 있습니다.
- MAX VALUE 또는 MIN VALUE 및 INCREMENT BY, 그리고 CYCLE 구문을 통해 임의로 시퀀스를 초기화하고 다시 원래 상태로 돌리는 방법이 있습니다.
시퀀스 삭제
시퀀스 삭제는 드랍 구문을 사용하면 됩니다.
DROP SEQUENCE USER_NAME.SEQUENCE_NAME;시퀀스 조회
시퀀스 조회는 NEXTVAL과 CURRVAL 의사칼럼으로 할 수 있습니다. 시퀀스명.의사칼럼을 사용하면 됩니다. 시퀀스가 우선 세션에 정의되어야 하므로 NEXTVAL을 먼저 조회하여야 합니다.
SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;
CURRVAL
----------
1만약 시퀀스를 세션에 정의하지 않고 바로 조회하면 다음처럼 오류가 발생해요! 😭 (ORA-08002: sequence SEQUENCE_NAME.CURRVAL is not yet defined in this session)
SELECT USER_NAME.SEQUENCE_NAME.CURRVAL FROM DUAL;
ERROR at line 1:
ORA-08002: sequence SEQUENCE_NAME.CURRVAL is not yet defined in this session시퀀스를 조회할 때에는 보통 DUAL 테이블로 조회하여 변수에 할당하거나, 바로 다음 값을 조회하여 사용할 수 있어요.
INSERT INTO EMP (EMPNO, ENAME) VALUES (SEQUENCE_NAME.NEXTVAL, 'BEOMSANG');
1 row created.제일 처음에 시퀀스의 사용 목적이 고유하고 순차적인 번호를 획득하는 데 있다고 안내해드렸데요, 인서트 한 값을 롤백하더라도 시퀀스 값은 변경이 됩니다. 커밋과 롤백에 무관하게 유일한 값을 보장하기 위해서랍니다.
다음은 시퀀스를 사용하여 인서트를 했다가 롤백해 보았는데요, 시퀀스의 현재값을 조회하였을 때 값이 변경된 것을 확인할 수 있어요.
INSERT INTO EMP (EMPNO, ENAME) VALUES (SEQUENCE_NAME.NEXTVAL, 'BEOMSANG');
1 row created.
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;
CURRVAL
----------
1
ROLLBACK;
Rollback complete.
INSERT INTO EMP (EMPNO, ENAME) VALUES (SEQUENCE_NAME.NEXTVAL, 'BEOMSANG');
1 row created.
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;
CURRVAL
----------
2