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 속성
- currval : 현재 지정된 시퀀스 값 반환
현재 시퀀스 값을 확인하거나 현재 시퀀스 값을 적용할 때 사용 - 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 사용 불가 경우
-
VIEW의 SELECT 절
create view 뷰명 as select ~ (X) - DISTINCT 키워드가 있는 SELECT 문
-
GROUP BY, HAVING, ORDER BY 절이 있는 SELECT 문
그룹함수를 사용하는 경우 - SELECT, DELETE, UPDATE의 서브 쿼리
-
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 |
| ... | ... | ... |
※ 주의사항
- 시퀀스를 여러 개의 테이블에 연결할 때 nextval과 currval을
정확히 사용하지 않으면 시퀀스 번호를 제대로 설정할 수 없음 - crash : 중간에 번호가 갑자기 넘어가는 경우가 발생
요즘에는 시퀀스를 사용하기보다 레코드에 번호를 매기는 필드를 설정하고
해당 필드의 최댓값을 구해서 거기에 1을 더해가는 방식을 선호한다고 하니 참고 바랍니다.