15_시퀀스

[ 시퀀스 목차 ]

1. 시퀀스의 개념 이해와 시퀀스 생성

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

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

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

5. 시퀀스 제거와 수정

 

* sequence : 순서

 

[ 1 ] 시퀀스의 개념 이해와 시퀀스 생성

- 오라클에서는 행을 구분하기 위해 기본 키(PRIMARY KEY)를 두고 있다.

  기본 키는 중복된 값을 가질 수 없어 항상 유일한 값을 가져야만 한다.

- 기본 키가 유일한 값을 갖도록 사용자가 직접 값을 생성해내려면

  부담이 크다.

- 시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성하는

  자동 번호 발생기이므로 시퀀스를 기본 키로 사용하게 되면

  사용자의 부담을 줄일 수 있다.

 

* sequence 는 1, 2, 3, 4 .... 순서대로 번호로 이루어져있다.

  기본키(PK)는 ROW를, 데이터를 식별하는 용도로 사용한다.

  PK = NOT NULL + UNIQUE

  숫자로 PK를 사용하는게 가장 쉬운 방법이다.

  이때 이 PK로 숫자를 넣을 때 사람이 하나씩 넣어주는 것보다

  sequence를 사용해서 순서있게 숫자를 넣어주면 편리하다.

  --> sequence : 오직 PK 값으로 이용하기 위해 사용한다.

  

 

-- 형식
CREATE SEQUENCE sequence_name 
 [START WITH n] ① 
 [INCREMENT BY n] ② 
 [{MAXVALUE n | NOMAXVALUE}] ③
 [{MINVALUE n | NOMINVALUE}] ④
 [{CYCLE | NOCYCLE}] ⑤
 [{CACHE n | NOCACHE}] ⑥ 
 
-- 예
CREATE SEQUENCE DEPT_DEPTNO_SEQ
INCREMENT BY 10
START WITH 10;

 

① START WITH

  : 시퀀스 번호의 시작 값을 지정할 때 사용

   만일 1부터 시작되는 시퀀스를 생성하려면

   START WITH 1이라고적으면 된다.

 

② INCREMENT BY

  : 연속적인 시퀀스 번호의 증가치를 지정할 때 사용

    만일 1씩 증가하는 시퀀스를 생성하려면

    INCREMENT BY 1이라고 적으면 된다.

 

③ MAXVALUE n | NOMAXVALUE

  : MAXVALUE는 시퀀스가 가질 수 있는 최댓값을 지정한다.

   만일 NOMAXVALUE를 지정하게 되면

   ASCENDING 순서일 경우에는 10^27승이고

  DESCENDING 순서일 경우에는 -1로 설정된다.

 

④ MINVALUE n | NOMINVALUE

  : MINVALUE는 시퀀스가 가질 수 있는 최솟값을 지정한다.

   만일 NOMINVALUE를 지정하게 되면

   ASCENDING 순서일 경우에는 1이고

   DESCENDING 순서일 경우에는 10^26승으로 설정된다.

 

⑤ CYCLE | NOCYCLE

  : CYCLE은 지정된 시퀀스 값이 최댓값까지 증가가 완료되게

   되면 다시 START WITH 옵션에 지정한 시작 값에서

   다시 시퀀스를 시작하도록 한다.

   NOCYCLE은 증가가 완료되게 되면 에러를 유발한다.

 

⑥ CACHE n | NOCACHE

  : CACHE 은 메모리 상의 시퀀스 값을 관리하도록 하는 것인데

   기본 값은 20이다.

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

 

 

 

 

 

---

 

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

- USER_SEQUENCES

  : 생성된 시퀀스 객체에 대한 정보를 저장하는 데이터 딕셔너리

 

-- SEQUENCE 조회
DESC USER_SEQUENCES;

SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG
FROM USER_SEQUENCES;
-- 현 계정이 가지고 있는 SEQUENCE들에 대한
-- SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG 정보들 출력

 

[ 현 계정 SCOTT 이 가지고 있는 SEQUENCE들에 대한 정보들 출력한 결과 ]

 

 

---

 

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

- CURRVAL

  : 시퀀스의 현재 값을 알아낼 때 사용

- NEXTVAL

  : 시퀀스의 다음 값을 알아낼 때 사용

 

-- SEQUENCE 값 확인 : CURVAL, NEXTVAL
SELECT DEPT_DEPTNO_SQ.NEXTVAL FROM DUAL; -- 계속 실행할 때마다 10씩 증가하는 결과를 도출한다
-- 실행할 때마다 시퀀스의 다음 값을 알려준다. <-- NEXTVAL

SELECT DEPT_DEPTNO_SQ.CURRVAL FROM DUAL;
-- 현재의 값을 알려준다. <-- CURRVAL

 

[ SELECT DEPT_DEPTNO_SQ.NEXTVAL FROM DUAL 한번 실행 한 결과 ]

 

[SELECT DEPT_DEPTNO_SQ.NEXTVAL FROM DUAL 두번 실행한 결과 ]

 

[ SELECT DEPT_DEPTNO_SQ.NEXTVAL FROM DUAL 세번 실행한 결과 ]

 

[ SELECT DEPT_DEPTNO_SQ.NEXTVAL FROM DUAL 네번 실행한 결과 ]

 

 

[ NEXTVAL 계속 실행하다가 현재의 시퀀스 값 출력한 결과 ]

 

 

 

 

---

 

[ 4 ] 시퀀스 실무에 적용하기

- 시퀀스는 99.9%가 INSERT 연산과 같이 사용되어

  칼럼 값을 자동으로 발생시키는 용도로 사용된다.

- 아래의 예시는 사원 테이블을 생성하면서

  사원 번호를 기본 키로 설정한 것이다.

- 기본 키는 반드시 유일한 값을 가져야 한다.

  사용자가 새로운 사원을 추가할 때마다

  유일한 사원번호를 INSERT 해야 하는

  번거로움이 있다.

- 사원 번호를 생성하는 시퀀스 객체를 사용하여

  사원 번호가 자동으로 생성되도록 한다면

  이러한 번거로움을 덜어 줄 수 있다.

 

-- 예: 시퀀스를 테이블의 기본 키에 접목하기
-- SEQUENCE 실무
CREATE SEQUENCE EMP_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 100000;

DROP TABLE EMP01;

CREATE TABLE EMP01(
    EMPNO NUMBER(4) PRIMARY KEY,
--    ENAME VARCHAR2(10),  -- ORACLE 문자열 자료형
    ENAME VARCHAR(10),     -- ANSI 문자열 자료형
    HIREDATE DATE
);
-- VARCHAR2 = VARCHAR

INSERT INTO EMP01 VALUES (EMP_SEQ.NEXTVAL, 'JULIA', SYSDATE);
-- EMPNO 에는 우리가 만든 EMP_SEQ 시퀀스의 다음 값을 넣어준다.

SELECT * FROM EMP01;
-- INSERT INTO EMP01 값 추가를 5번 해줬더니 다음과 같이 출력된다.

 

[ INSERT INTO EMP01 VALUES (EMP_SEQ.NEXTVAL, 'JULIA', SYSDATE) 한번실행한 후 EMP01 출력한 결과 ]

 

[ INSERT INTO EMP01 VALUES 두번 실행한 후 EMP01 출력한 결과 ]

 

 

[ INSERT INTO EMP01 VALUES 세번 실행한 후 EMP01 출력한 결과 ]

 

 

[ INSERT INTO EMP01 VALUES 다섯번 실행한 후 EMP01 출력한 결과 ]

 

 

* 이렇게 데이터를 계속 추가해줄 때마다 NEXTVAL 값은 1씩 증가한

  값으로 되어 EMPNO 칼럼에 추가된다.

 

 

---

 

[ 5 ] 시퀀스 제거와 수정

 

1. 시퀀스를 삭제하는 방법

- DROP SEQUENCE 문

  : 생성되어 있는 시퀀스 제거할 때 사용

 

-- SEQUENCE 제거와 수정
SELECT SEQUENCE_NAME, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG
FROM USER_SEQUENCES;
-- 삭제하고 싶은 SEQUENCE를 찾기 위해 먼저 SEQUENCE들을 조회한다.

-- DEPT_DEPTNO_SQ 삭제
DROP SEQUENCE DEPT_DEPTNO_SQ;

 

 

[ 삭제하고 싶은 SEQUENCE를 찾기 위해 먼저 SEQUENCE들을 조회한다. ]

 

 

[ DEPT_DEPTNO_SQ 삭제 후 다시 SEQUENCE 조회한 결과 ]

 

 

 

2. 시퀀스를 수정하는 방법

- ALTER SEQUENCE 문

  : 시퀀스를 변경할 때 사용

- ALTER SEQUENCE는 START WITH 절이

  없다는 점을 빼고는 CREATE SEQUENCE와

  구조가 동일하다.

- START WITH 옵션은 ALTER SEQUENCE를

  사용해서 변경할 수 없다.

- 다른 번호에서 다시 시작하려면 이전 시퀀스를

  삭제하고 다시 생성해야 한다.

  

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

-- 예 : 시퀀스 최대값을 변경하기
DROP SEQUENCE DEPT_DEPTNO_SEQ;
CREATE SEQUENCE DEPT_DEPTNO_SEQ
START WITH 10
INCREMENT BY 10 
MAXVALUE 30; -- 10부터 10씩 증가하면서 최대 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; -- 부서 번호를 계속 생성하다 보면 최대값을 넘게 된다.

-- EMP_SEQ 의 MAXVALUE 값을 수정
ALTER SEQUENCE DEPT_DEPTNO_SEQ
MAXVALUE 100; -- 최대값을 넘을 때까지 시퀀스를 생성한다.
-- MAXVALUE 값 100000 에서 100 으로 수정한다.

SELECT SEQUENCE_NAME, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG
FROM USER_SEQUENCES; -- USER_SEQUENCES 를 조회하면 시퀀스가 수정되었는지 확인할 수 있다.

 

 

[ EMP_SEQ 시퀀스의 MAXVALUE값을 100000 에서 100으로 수정한 결과 출력 (이전에 100000였음은 위의 삭제 파트 사진을 보면 알 수 있다.) ]

 

 

 

 

---

 

 

[ 과제 - 15 - 01. TXT ]

 

Q. 최소값 1, 최대값 99999999, 1000부터 시작해서 1씩 증가하는 ORDERS_SEQ 라는 시퀀스를 만들어보자.

A.

CREATE SEQUENCE ORDERS_SEQ
START WITH 1000
INCREMENT BY 1
MAXVALUE 99999999
MINVALUE 1;

SELECT ORDERS_SEQ.NEXTVAL FROM DUAL;
SELECT ORDERS_SEQ.CURRVAL FROM DUAL;

 

[ NEXTVAL 한번 실행한 후 CURRVAL 출력한 결과 ]

 

 

[ NEXTVAL 두번 실행한 후 CURRVAL 출력한 결과 ]

 

 

[ NEXTVAL 세번 실행한 후 CURRVAL 출력한 결과 ]