오라클 시퀀스(sequence)

시퀀스(SEQUENCE)

---

시퀀스란 기존의 테이블에 대해 기본키나 유니크 키를 사용하여 부가하는 일종의 새로운 컬럼처럼 사용할 수 있는 일련번호를 매김하기 위한 하나의 컬럼으로 구성된 테이블과 같다.

• 시퀀스라는 객체 스스로만 사용할 의미가 없으며, 기존의 테이블과 연계해서 사용하게 된다.

# 시퀀스 개념

scott.dept 테이블의 deptno 행을 보면 일련의 숫자로 이루어진 것을 알 수 있다. 시퀀스가 그러한 역할을 하는데 즉, 테이블의 행에 유일한 정수를 부여함으로써 기본키(primary key) 값을 생성하는 객체이다

• 시퀀스는 Oracle server에 의해 자동으로 발생하고 증가 또는 감소된다.

• 테이블에 독립적으로 적용되기 때문에 다양한 사용자의 접근이 가능하다.

# 시퀀스 특징

• 유일한 연속번호를 자동으로 생성

• 기본키값 생성을 위해 사용

• Application Code를 대채

• 메모리에 Cache되어 access 효율성을 증가

• 시퀀스는 여러 테이블에 의해 공유될 수 있다.

# 시퀀스 생성

다른 객체의 생성과 마찬가지로 create 문을 사용한다. 옵션이 다양하므로 각 옵션의 순서와 옵션 생략시의 디폴트 값을 알아 둘 필요가 있다.

【형식】

CREATE SEQUENCE 시퀀스명

[ INCREMENT BY 정수]

[ START WITH 정수]

[ MAXVALUE n ¦ NOMAXVALUE]

[ MINVALUE n ¦ NOMINVALUE]

[ CYCLE ¦ NOCYCLE]

[ CACHE n ¦ NOCACHE];

옵션	설명
INCREMENT BY 정수	시퀀스 번호를 정수만큼씩 증가(디폴트=1)
START WITH 정수	시작값을 지정(디폴트=1) cycle 옵션을 사용한 경우 다시 값을 생성할 때 minvalue에 설정한 값부터 시작
MAXVALUE 정수	증가할 수 있는 최대값
NOMAXVALUE(default)	시퀀스의 최대값이 없음을 정의, 오름차순은 10^27까지 커질 수 있고, 내림차순으로 1까지 작아질 수 있음
MINVALUE 정수	생성할 수 있는 최소값
NOMINVALUE(default)	시퀀스의 최소값이 없음을 정의, 오름차순은 최소 1까지, 내림차순으로 -(10^26)까지 간다.
CYCLE	최대 또는 최소값에 도달한 후 값을 다시 생성
NOCYCLE(default)	최대 또는 최소값에 도달한 후 값을 다시 재시작할 수 없음
CACHE	빠른 access를 위해 시퀀스의 값을 메모리에 저장(기본 20)
NOCACHE	어떤 시퀀스값도 캐싱되지 않음

【예제】

SQL> create sequence dept_deptno

2 increment by 10

3 start with 50

4 maxvalue 10000

5 nocache

6 nocycle;

시퀀스가 생성되었습니다.

SQL>

# Pseudo 컬럼을 이용한 시퀀스의 사용

sequence는 currval과 nextval이라는 pseudo 컬럼을 사용하여 값을 리턴한다. 

CURRVAL이 참조되기 전에 NEXTVAL이 먼저 사용되어야 한다. 이는 pseudo 컬럼의 CURRVAL의 값은 NEXTVAL 컬럼 값을 참조하기 때문이다. 그러므로 NEXTVAL 컬럼이 사용되지 않은 상태에서 CURRVAL을 사용하면 아무런 값이 없기 때문에 error를 출력한다.

Pseudo column	사용형식	설명
NEXTVAL	시퀀스명.NEXTVAL	새로 작성된 시퀀스의 다음 값을 반환
CURRVAL	시퀀스명.CURRVAL	새로 작성된 시퀀스의 현재 값을 반환

유의사항

1)NEXTVAL과 CURRVAL을 사용할 수 있는 경우

- subquery가 아닌 SELECT 문

- INSERT 문의 DML SELECT 문

- INSERT 문의 DML VALUES 절

- UPDATE 문의 SET 절

2)NEXTVAL과 CURRVAL을 사용할 수 없는 경우

- VIEW 문의 SELECT 문

- DISTINCT 키워드를 사용한 SELECT 문

- GROUP BY, AVING, ORDER BY를 이용한 SELECT 문

- SELECT, DELETE, UPDATE 문에서의 subquery

- CREATE TABLE, ALTER TABLE 명령문의 DEFAULT 절

【예제】

SQL> CREATE SEQUENCE dept_deptno

2 INCREMENT BY 10

3 START WITH 50

4 MAXVALUE 10000

5 NOCACHE

6 NOCYCLE;

시퀀스가 생성되었습니다.

SQL> INSERT INTO dept(deptno,dname,loc)

2 VALUES(dept_deptno.NEXTVAL,'DEVELOP','COREA');

1 개의 행이 만들어졌습니다.

SQL> SELECT * FROM dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 DEVELOP COREA

SQL> SELECT dept_deptno.CURRVAL FROM DUAL;

CURRVAL

----------

50

SQL> SELECT dept_deptno.NEXTVAL FROM DUAL;

NEXTVAL

----------

60

SQL>

# 시퀀스 수정

ALTER SEQUENCE 명령어를 사용하여 시퀀스의 각 옵션을 수정할 수 있다. 하지만, 모든 값이 수정 가능한 것은 아니므로 미리 잘 알아 둬야 한다.

• START WITH 값은 시퀀스가 생성된 직후의 시작 값을 의미하므로 변경할 수 없다. 그러므로 START WITH 값을 변경하려면 시퀀스를 다시 생성해야 한다.

• 시퀀스가 MAXVALUE 또는 MINVALUE에 도달하면 시퀀스값을 할당받지 못하므로 error가 발생한다.

• 수정은 시퀀스의 소유자나 alter 권한을 가진자만 가능하다.

• 변경 이후의 시퀀스 번호만 영향을 받는다.

• 다른 번호로 다시 시작하려면, 시퀀스를 삭제한 후 다시 생성해야 한다.

• cycle 옵션은 시퀀스의 고유특성을 위반하므로 기본 키(PRIMARY KEY)가 정의된 컬럼에서는 사용하지 않는다.

【형식】

ALTER SEQUENCE 시퀀스명

[ INCREMENT BY 정수]

[ MAXVALUE n | NOMAXVALUE]

[ MINVALUE n | NOMINVALUE]

[ CYCLE | NOCYCLE]

[ CACHE n | NOCACHE];

【예제】

SQL> ALTER SEQUENCE dept_deptno

2 INCREMENT BY 20

3 MAXVALUE 500

4 NOCACHE

5 NOCYCLE;

시퀀스가 변경되었습니다.

SQL>

# 시퀀스 삭제

DROP 문을 사용하여 시퀀스를 삭제한다.

• 삭제는 시퀀스소유자나 DROP ANY SEQUENCE 권한을 가진자만 가능하다.

【형식】

DROP SEQUENCE 시퀀스이름;

【예제】

SQL> DROP SEQUENCE dept_deptno;

시퀀스가 삭제되었습니다.

SQL>

시퀀스 값의 간격이 발생하는 경우

• ROLLBACK이 발생한 경우

• system crash가 발생한 경우

• 다른 테이블에서 같은 시퀀스를 사용할 때

# 시퀀스에 대한 정보 확인

user_sequences	사용자가 만든 시퀀스에 대한 정보
dba_sequences	데이터베이스에 설정된 시퀀스에 대한 정보

【예제】

SQL> SELECT sequence_name,min_value,max_value,increment_by,last_number

2 FROM USER_SEQUENCES;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER

--------------- ---------- ---------- ------------ -----------

DEPT_DEPTNO 1 10000 10 50

SQL>

다음은 잘정리되어서 가져왔지만 몇가지 주의할점이있다.

1. SELECT * from user_sequences where sequence_name = 여기서 대문자로해야함!!!

그리고 이렇게검색했으나 먼가 이상함.. 일단 last_number가 cache_size때문에 바로바로 증가하지않음..