SQL_SEQUENCE

SEQUENCE

특정 필드(주로 PK가 설정된 필드)에 연결해서 데이터를 입력할 때
지정한 번호를 자동으로 입력하는 오라클 객체

• 중복을 방지하기 위해 사용
• 시퀀스는 유일(UNIQUE)한 값을 생성해주는 오라클 객체
• 시퀀스를 생성하면 기본 키와 같이
  순차적으로 증가하는 칼럼을 자동적으로 생성할 수 있음

↔ rownum : 화면에 출력할 때 레코드를 구분하기 위해 사용

SEQUENCE 생성

§ 형식

{ SQL> CREATE SEQUENCE sequence명

START WITH n ①

INCREMENT BY n ②

MAXVALUE n | NOMAXVALUE ③

MINVALUE n | NOMINVALUE ④

CYCLE | NOCYCLE ⑤

CACHE n | NOCACHE}] ; ⑥

 

① 시퀀스 시작 값 지정, default = 1
② 시퀀스 증가 값, n을 2로 지정하면 시퀀스가 2씩 증가함(1, 3, 5,,,)
③ 시퀀스 최댓값 설정, NONMAXVALUE는 최댓값을 무한대로 지정
④ 시퀀스 최솟값 설정, NOMINVALUE 최솟값을 설정하지 않음
⑤ 시퀀스 순환 설정, 시퀀스가 최대값까지 간 뒤에 순환을 할 것인지 결정
⑥ 시퀀스를 서버 메모리에 저장할 것인가에 대한 설정

# 예제

▶ b_emp5_empno 이름을 갖는 default sequence 생성
[ SQL> create sequence b_emp5_empno;
시퀀스가 생성되었습니다. ]

※ 아무런 옵션을 주지 않으면 1부터 시작해서 1씩 증가하며 번호를 매김

SEQUENCE 수정

§ 형식

{ SQL> ALTER SEQUENCE sequence명

[START WITH n] ①

[INCREMENT BY n] ②

[{MAXVALUE n | NOMAXVALUE}] ③

[{MINVALUE n | NOMINVALUE}] ④

[{CYCLE | NOCYCLE}] ⑤

[{CACHE n | NOCACHE}] ; } ⑥

※ 생성과 create / alter 만 다름

# 에러

▷ b_dept2_deptno 시퀀스 확인
[ SQL> select sequence_name, min_value, max_value, increment_by, last_number from user_sequences; ]

SEQUENCE_NAME	MIN_VALUE	MAX_VALUE	INCREMENT_BY	LAST_NUMBER
B_DEPT2_DEPTNO	1	10000	10	70

▶ 위의 시퀀스를 2씩 증가하며 최댓값이 23, 최솟값이 1이고
순환하지 않으면서 메모리에 저장되지 않는 시퀀스로 변경
[ SQL> alter sequence b_dept2_deptno
increment by 2
maxvalue 23 
minvalue 1
nocycle
nocache;
1행에 오류: ORA-04009: MAXVALUE 에 현재치보다 작은 값을 지정할 수 없습니다 ]

 

※ 현재 시퀀스의 값이 수정하려는 MAXVALUE보다 큰 경우에 발생하는 에러

 

 

▶ 위의 시퀀스를 1부터 시작해서 2씩 증가하며 최댓값이 23, 최솟값이 1고
순환하지 않으면서 메모리에 저장되지 않는 시퀀스로 변경
[ SQL> alter sequence b_dept2_deptno
start with 1 
increment by 2
maxvalue 23
minvalue 1
nocycle
nocache;
1행에 오류: ORA-02283: 시퀀스 시작 번호는 변경할 수 없습니다. ]

 

※ 시퀀스의 시작 번호는 변경할수 없기 때문에 발생하는 에러

SEQUENCE 속성

1. currval : 현재 지정된 시퀀스 값 반환
   현재 시퀀스 값을 확인하거나 현재 시퀀스 값을 적용할 때 사용
2. nextval : 현재 지정된 시퀀스의 다음 값을 반환
   시퀀스를 초기화 하거나 다음 값으로 넘어갈 때 사용

§ 형식

[ SQL> ~ 시퀀스명.currval or nextval from 테이블명; ]

# 예제

{ 에러 }
▷ DUAL 테이블에서 b_emp5_empno시퀀스의 현재 값 반환
[ SQL> select b_emp5_empno.currval from dual;
1행에 오류:
ORA-08002: 시퀀스 B_EMP5_EMPNO.CURRVAL은
이 세션에서는 정의 되어 있지 않습니다 ]

 

※ 현재 설정된 값이 없어서 currval의 값을 출력할 수 없기 때문에 발생하는 오류

 

{ NEXTVAL_시퀀스 초기화 }
▷ DUAL 테이블에서 select b_emp5_empno시퀀스의 다음 값 반환
[ SQL> select b_emp5_empno.nextval from dual; ]

NEXTVAL
1

※ 맨 처음에 넣어줄 값을 NEXTVAL을 통해서 초기화 해야 함

 

{ CURRVAL }
▷ DUAL 테이블에서 b_emp5_empno시퀀스의 현재 값 반환
[ SQL> select b_emp5_empno.currval from dual; ]

CURRVAL
1

※ NEXTVAL로 시퀀스를 초기화했기 때문에 CURRVAL로 1이 반환됨

# 예제 _ 필드에 적용

▷ b_dept2 테이블 확인

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON
50	SUPPORT	KWANGJU

▶ 60부터 시작해서 10씩 증가하고 최댓값은 10000이며
순환하지 않고 메모리에 저장하지 않는 시퀀스 생성
[ SQL> create sequence b_dept2_deptno
start with 60
increment by 10
maxvalue 10000
nocycle
nocache;
시퀀스가 생성되었습니다. ]

 

▶ 부서번호 60, 부서명 TESTING,위치 SEOUL 입력
[ SQL> insert into b_dept2 values(b_dept2_deptno.nextval, 'TESTING', 'SEOUL'); ]

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON
50	SUPPORT	KWANGJU
60	TESTING	SEOUL

CURRVAL, NEXTVAL 사용 불가 경우

1. VIEW의 SELECT 절
   create view 뷰명 as select ~ (X)
2. DISTINCT 키워드가 있는 SELECT 문
3. GROUP BY, HAVING, ORDER BY 절이 있는 SELECT 문
   그룹함수를 사용하는 경우
4. SELECT, DELETE, UPDATE의 서브 쿼리
5. CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값
   default값은 고정된 값을 넣어줄 때 사용하는데
   시퀀스는 값이 계속 바뀌기 때문에 서로 성격이 맞지 않음

※ 시퀀스는 데이터 입력과 관련되어 있기 때문에 SELECT문과는 거리가 있음

SEQUENCE_Data Dictionary

※ user_sequences
시퀀스 이름, 최솟값, 최댓값, 증가량 등의 정보를 확인할 수 있는 Oracle 자체 테이블

이름	널?	유형
SEQUENCE_NAME	NOT NULL	VARCHAR2(30)
MIN_VALUE		NUMBER
MAX_VALUE		NUMBER
INCREMENT_BY	NOT NULL	NUMBER
...	...	...

※ 주의사항

1. 시퀀스를 여러 개의 테이블에 연결할 때 nextval과 currval을
   정확히 사용하지 않으면 시퀀스 번호를 제대로 설정할 수 없음
2. crash : 중간에 번호가 갑자기 넘어가는 경우가 발생

---

요즘에는 시퀀스를 사용하기보다 레코드에 번호를 매기는 필드를 설정하고
해당 필드의 최댓값을 구해서 거기에 1을 더해가는 방식을 선호한다고 하니 참고 바랍니다.