[Oracle/SQL] 11. 시퀀스 | SEQUENCE | CURRVAL

Database

[Oracle/SQL] 11. 시퀀스 | SEQUENCE | CURRVAL | NEXTVAL

@reminder 2022. 1. 27. 20:15

학습점검

✅ ~~SEQUENCE 객체의 사용 목적에 대해 이해할 수 있다.~~
✅ ~~SEQUENCE 객체에 대해 이해할 수 있다.~~
✅ ~~CREATE SEQUENCE 구문을 이용하여 SEQUENCE 객체를 생성할 수 있다.~~
✅ ~~SEQUENCE 객체를 생성할 시 지정할 수 있는 옵션에 대해 이해할 수 있다.~~
✅ ~~SEQUENCE를 사용할 수 있는 구문에 대해 이해하고 사용할 수 있다.~~

1. 시퀀스(SEQUENCE)

시퀀스(SEQUENCE)란, 자동 번호 발생기 역할을 하는 객체이다.
순차적인 정수값을 자동 생성한다.

1-1. CREATE SEQUENCE

✅ CREATE SEQUENCE 시퀀스이름
　 ~~[INCREMENT BY 숫자]~~ -- 증가치, 생략하면 자동 1 기본
　 ~~[START WITH 숫자]~~ -- 처음 발생시킬 값, 생략하면 자동 1 기본, 추후 변경 불가
　 ~~[MAXVALUE 숫자 | NOMAXVALUE]~~ -- 발생시킬 최대값(10의 27승까지 가능)
　 ~~[MINVALUE 숫자 | NOMINVALAUE]~~ -- 발생시킬 최소값(-10의 26승까지 가능)
　 ~~[CYCLE | NOCYCLE]~~ -- 값의 순환 여부
　 ~~[CACHE 바이트크기 | NOCACHE]~~ -- 캐시메모리(기본값 20 byte, 최소 2 byte)

CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

시퀀스 생성 구문 예시는 위와 같다.
① INCREMENT BY, ② START WITH, ③ MAXVALUE, ④ MINVALUE, ⑤ CYCLE, ⑥ CACHE는 시퀀스 옵션으로 생략 가능하며, 필요에 따라 명시해 지정할 수 있다.

1-2. NEXTVAL과 CURRVAL

✅ 시퀀스명.CURRVAL -- 해당 시퀀스가 가지고 있는 현재 값(CURRENT VALUE)
✅ 시퀀스명.NEXTVAL -- 해당 시퀀스가 가질 다음 값(NEXT VALUE)

CURRVAL과 NEXTVAL은 각각 CURRENT VALUE, NEXT VALUE의 줄임말이다.

SELECT SEQ_EMPID.CURRVAL FROM DUAL; NEXTVAL 선행되어야 함

SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 300
SELECT SEQ_EMPID.CURRVAL FROM DUAL; -- 300

SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 305
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 310
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- MAXVALUE 초과(exceeds) 오류 발생
▶시퀀스 SEQ_EMPID.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다

이때 반드시 NEXTVAL 먼저 최소 1회 수행 후 → CURRVAL 조회가 가능하다.
생성 시 지정한 MAXVALUE를 넘어서면 초과(exceeds)라는 설명과 함께 오류 발생한다.

1-3. 데이터 딕셔너리

✅ SELECT * FROM USER_SEQUENCES;

데이터 딕셔너리 통해 만들어진 시퀀스 목록을 조회한다.

생성 시 설정한 조건마다 컬럼으로 조회할 수 있다.
이때 LAST_NUMBER라는 컬럼에서는 CURRVAL~NEXTVAL 조회로 계속 쌓아온 수치에 대해 보여준다. MAXVALUE 초과로 값이 반영되지는 못했지만, 사용자가 조회하고자 했던 LAST_NUMBER로서 315라는 값을 보여주고 있다.

2. 시퀀스 변경 및 삭제

❗ START WITH 옵션만 변경 불가하다.

-- 시퀀스 변경
ALTER SEQUENCE SEQ_EMPID
INCREMENT BY 10
MAXVALUE 400
NOCYCLE
NOCACHE;

예시에서처럼 나머지 옵션은 모두 변경 가능한 반면, 처음 발생시킬 값 [START WITH]에 대해서는 변경 불가하다.
START WITH 수치를 변경하려거든 DROP 명령어로 SEQUENCE 자체를 삭제 후 다시 생성해야 한다.

-- 시퀀스 삭제
DROP SEQUENCE SEQ_EMPID;

시퀀스 삭제 구문은 위와 같다.

3. 시퀀스 사용 가능 여부

NEXTVAL/CURRVAL 사용 가능 여부
사용가능	사용불가
서브쿼리가 아닌 SELECT문	서브쿼리의 SELECT문
INSERT문의 SELECT절	VIEW의 SELECT절
INSERT문의 VALUES절	DISTINCT 키워드가 있는 SELECT문
UPDATE문의 SET절	GROUP BY, HAVING절이 있는 SELECT문
-	ORDER BY절이 있는 SELECT문
-	CREATE TABLE, ALTER TABLE의 DEFAULT값

❗ 시퀀스(SEQUENCE) 사용 가능
① SELECT문에서 사용 가능
② INSERT문의 SELECT절에서 사용 가능
③ INSERT문의 VALUES절에서 사용 가능
④ UPDATE문의 SET절에서 사용 가능

학습 과정에서 주로 사용하게 될 용법은 '③ INSERT문의 VALUES절에서 사용'이다.
PRIMARY KEY(PK)는 행을 구분할 수 있는 컬럼의 고유 식별자이자 NOT NULL + UNIQUE의 의미를 가진 제약 조건이다. 회원 번호나 게시글 번호를 부여하는 데 필요한 요소이다.
예를 들어 회원가입 시 회원 번호를 1번부터 차례로 부여하게 될 텐데, 이때 번호 부여 과정을 SEQUENCE 오브젝트 통해 행할 것이다. SEQUENCE는 마지막 번호를 기억할 수 있고, 그 다음 번호를 생성할 수 있는 객체이기 때문이다.

❗ 시퀀스(SEQUENCE) 사용 불가
① 서브쿼리의 SELECT문에서 사용 불가
② VIEW의 SELECT절에서 사용 불가
③ DISTINCT 키워드가 있는 SELECT문에서 사용 불가
④ GROUP BY, HAVING절이 있는 SELECT문에서 사용 불가
⑤ ORDER BY절에서 사용 불가
⑥ CREATE TABLE, ALTER TABLE의 DEFAULT값으로 사용 불가

4. 사용 예제

-- INSERT~VALUES에서 SEQUENCE 사용
-- SEQUENCE 생성
CREATE SEQUENCE SEQ_EID
START WITH 300
INCREMENT BY 1
MAXVALUE 10000
NOCYCLE
NOCACHE;

-- SEQUENCE로 INSERT
INSERT
INTO EMPLOYEE A
(
A.EMP_ID, A.EMP_NAME, A.EMP_NO, A.EMAIL, A.PHONE
, A.DEPT_CODE, A.JOB_CODE, A.SAL_LEVEL, A.SALARY, A.BONUS
, A.MANAGER_ID, A.HIRE_DATE, A.ENT_DATE, A.ENT_YN
)
VALUES
(
SEQ_EID.NEXTVAL, '김화창', '991231-2345678', 'kimb@reminder.com', '010-0001-0001'
, 'D1', 'J5', 'S2', 5000000, 0.3
, 200, SYSDATE, NULL, DEFAULT
);