[SQL] Oracle 시퀀스(Sequence) 정리 – CREATE / NEXTVAL / CURRVAL / IDENTITY 컬럼

[ 시퀀스 Sequence ]

1. 시퀀스(Sequence)란?

• 자동으로 유일한(unique) 번호 생성하는 객체
• 주로 PRIMARY KEY 값을 자동 증가시킬 때 사용

[Oracle의 객체(Object) 관리]

⚠️ 오라클이 생성하는 것들은 전부 객체(Object)로 관리됨

• 객체 관리

CREATE 객체종류 이름 ...   -- 생성
ALTER  객체종류 이름 ...   -- 수정
DROP   객체종류 이름;      -- 삭제

• 데이터 딕셔너리에 자동 등록

SELECT * FROM USER_OBJECTS;  -- 내 모든 객체 한번에 조회 가능

주요 객체	설명
TABLE	데이터 저장
VIEW	가상 테이블
SEQUENCE	자동 번호 생성
INDEX	검색 속도 향상
SYNONYM	객체 별칭
PROCEDURE	절차형 SQL
FUNCTION	반환값 있는 절차형 SQL
TRIGGER	자동 실행 SQL
PACKAGE	프로시저/함수 묶음
TYPE	사용자 정의 타입

 

 

2. 시퀀스(Sequence) 생성

CREATE SEQUENCE 시퀀스명
[START WITH n]       -- 시작 번호 (기본값 1)
[INCREMENT BY n]     -- 증가값 (기본값 1)
[MAXVALUE n]         -- 최대값
[MINVALUE n]         -- 최소값
[CYCLE | NOCYCLE]    -- 최대값 도달 시 순환 여부
[CACHE n | NOCACHE]; -- 메모리에 미리 할당할 번호 수

옵션	설명	기본값
START WITH	시작 번호 지정	1
INCREMENT BY	증가값 지정	1
MAXVALUE	최대값 지정	10^27 (거의 무한)
MINVALUE	최소값 지정	1
CYCLE	최대값 도달 시 다시 처음부터 순환	-
NOCYCLE	최대값 도달 시 오류 발생	기본값
CACHE	메모리에 번호 미리 할당	20
NOCACHE	캐시 사용 안 함	-

-- 기본 (1부터 1씩 증가)
CREATE SEQUENCE seq_emp;

-- 옵션 전체 사용
CREATE SEQUENCE seq_emp
START WITH 1       -- 1부터 시작
INCREMENT BY 1     -- 1씩 증가
MAXVALUE 1000      -- 최대 1000
MINVALUE 1         -- 최소 1
NOCYCLE            -- 최대값 도달 시 오류
CACHE 20;          -- 20개 미리 캐시

 

3. 시퀀스(Sequence) 수정

• START WITH 는 수정 불가 (시작값 바꾸려면 DROP 후 재생성)

ALTER SEQUENCE 시퀀스명
[INCREMENT BY n]
[MAXVALUE n]
[MINVALUE n]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];

 

4. 시퀀스(Sequence) 삭제

DROP SEQUENCE 시퀀스명;

 

5. 시퀀스(Sequence) 정보 확인

-- 내 시퀀스만 조회
SELECT * FROM USER_SEQUENCES;

-- 내 모든 객체 조회 (시퀀스 포함)
SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'SEQUENCE';

 

6. 시퀀스(Sequence) 사용

시퀀스명.NEXTVAL  -- 다음 번호 생성 (증가)
시퀀스명.CURRVAL  -- 현재 번호 조회 (증가 안 함)

-- NEXTVAL : 호출할 때마다 번호 증가
INSERT INTO emp (empno, ename) VALUES (seq_emp.NEXTVAL, 'HONG'); -- 1
INSERT INTO emp (empno, ename) VALUES (seq_emp.NEXTVAL, 'KIM');  -- 2
INSERT INTO emp (empno, ename) VALUES (seq_emp.NEXTVAL, 'LEE');  -- 3

-- CURRVAL : 현재 값 조회
SELECT seq_emp.CURRVAL FROM DUAL;  -- 3

⚠️ CURRVAL 주의사항
- 시퀀스 생성 직후 바로 CURRVAL 조회하면 오류 발생( NEXTVAL을 한 번 이상 호출한 후에만 사용 가능)

 

7. IDENTITY 컬럼

• 오라클 12c부터 추가된 기능
• 시퀀스를 따로 만들지 않고 컬럼에 자동 증가를 바로 설정
• 열 레벨 방식으로 지정 가능

CREATE TABLE 테이블명 (
    컬럼명 숫자타입 GENERATED [ALWAYS | BY DEFAULT [ON NULL]] AS IDENTITY [(
        [START WITH n]       -- 시작 번호 (기본값 1)
        [INCREMENT BY n]     -- 증가값 (기본값 1)
        [MAXVALUE n]         -- 최대값
        [MINVALUE n]         -- 최소값
        [CYCLE | NOCYCLE]    -- 최대값 도달 시 순환 여부
        [CACHE n | NOCACHE]  -- 메모리에 미리 할당할 번호 수
    )]
);

옵션	설명
GENERATED ALWAYS	항상 자동 생성, 직접 값 입력 불가(기본값)
GENERATED BY DEFAULT	자동 생성이지만 직접 값 입력도 가능
GENERATED BY DEFAULT ON NULL	NULL 입력 시에만 자동 생성

 

1) GENERATED ALWAYS

• 해당 컬럼의 값을 항상 자동으로 생성
• 직접 값 입력 불가

CREATE TABLE emp (
    empno NUMBER GENERATED AS IDENTITY  -- 기본값 : ALWAYS 
);

CREATE TABLE emp (
    empno NUMBER GENERATED ALWAYS AS IDENTITY,
    ename VARCHAR2(20)
);

INSERT INTO emp (ename) VALUES ('HONG');      --  empno 자동 생성
INSERT INTO emp VALUES (999, 'HONG');         -- ❌ 오류 (직접 입력 불가)

 

2) GENERATED BY DEFAULT

• 해당 컬럼의 값을 항상 자동으로 생성
• 직접 값을 입력할 수 있음.

CREATE TABLE emp (
    empno NUMBER GENERATED BY DEFAULT AS IDENTITY,
    ename VARCHAR2(20)
);

INSERT INTO emp (ename) VALUES ('HONG');      -- ✅ 자동 생성
INSERT INTO emp VALUES (999, 'HONG');         -- ✅ 직접 입력도 가능

⚠️BY DEFAULT로 지정하고 해당 값을 직접 입력한 경우 주의사항
- IDENTITY가 자동으로 갱신되지 않기 때문에 나중에 자동 증가값과 충돌하여 PK 중복 등의 오류가 발생할 수 있음
- 직접 입력 후 IDENTITY 시작값을 재설정하여 오류 예방

 

 

Mysql의 AUTO_INCREMENT와 Oracle의 시퀀스/IDENTITY 비교

기능	MySQL AUTO_INCREMENT	Oracle 시퀀스	Oracle IDENTITY
시작값 설정	✅ 테이블 단위	✅	✅
증가값 설정	✅ 서버 전체 적용	✅ 시퀀스 단위	✅ 컬럼 단위
최대값 설정	❌	✅	✅
최소값 설정	❌	✅	✅
순환 (CYCLE)	❌	✅	✅
직접 값 입력	✅	✅	옵션에 따라 다름
여러 테이블 공유	❌	✅	❌ 컬럼 전용
별도 객체 생성	❌	✅ 필요	❌
캐시 설정	❌	✅	✅
사용 버전	전 버전	전 버전	12c 이상