SQL 12-1 시퀀스의 사용및 편집
관련링크
본문
① 시퀀스 확인
생성된 시퀀스는 데이터 딕셔너리에 기록된다.
시퀀스는 데이터베이스 객체이므로 USER_OBJECTS 데이터 딕셔너리 테이블에서 식별할 수 있다.
․ USER_SEQUENCES 데이터 딕셔너리 테이블에서 시퀀스 값을 확인한다.
․ NOCACHE 가 지정된 경우 LAST_NUMBER 열에는 사용 가능한 다음 시퀀스 번호가 표시된다.
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
② NEXTVAL 및 CURRVAL 의사 열
․ NEXTVAL은 사용 가능한 다음 시퀀스 값을 반환하며, 참조될 때마다(서로 다른 사용자일지라도)고유한 값을 반환한다.
․ CURRVAL은 현재 시퀀스 값을 반환한다.
․ CURRVAL이 값을 포함하려면 먼저 해당 시퀀스에 대해 NEXTVAL이 실행되어야 한다.
③ 시퀀스 사용
시퀀스를 생성하며 시퀀스가 테이블에서 사용할 시퀀스 번호를 생성한다.
NEXTVAL 및 CURRVAL 의사 열을 사용하여 시퀀스 값을 참조한다.
☞ NEXTVAL 및 CURRVAL 의사 열
NEXTVAL 의사 열은 지정한 시퀀스에서 연속적인 시퀀스 번호를 추출하는 데 사용되며, NEXTVAL에는 시퀀스 이름을 지
정해야 한다.
sequence.NEXTVAL을 참조하면 새 시퀀스 번호가 생성되며 현재 시퀀스 번호는 CURRVAL에 위치하게 된다.
CURRVAL 의사 열은 현재 사용자가 방금 생성한 시퀀스 번호를 참조하는 데 사용된다.
CURRVAL을 참조하려면 그 전에 NEXTVAL을 사용하여 현재 사용자 세션에서 시퀀스 번호를 생성해야 하며, CURRVAL에
는 시퀀스 이름을 지정해야 한다.
sequence.CURRVAL을 참조하면 해당 사용자 프로세스에 마지막으로 반환된 값이 표시된다.
☞ NEXTVAL 및 CURRVAL 사용 규칙
NEXTVAL 및 CURRVAL을 사용하는 경우.
․ 서브 쿼리에 속하지 않은 SELECT 문의 SELECT 목록
․ INSERT 문에 있는 서브 커리의 SELECT 목록
․ INSERT 문의 VALUES 절
․ UPDATE 문의 SET 절
NEXTVAL 및 CURRVAL을 사용 못 하는 경우.
․ 뷰의 SELECT 목록
․ DISTINCT 키워드가 있는 SELECT 문
․ GROUP BY,HAVING 또는 ORDER BY 절이 있는 SELECT 문
․ SELECT,DELETE,UPDATE 문의 서브 쿼리
․ CREATE TABLE 또는 ALTER TABLE 문의 DEFAULT 표현식
④ 시퀀스 사용
․ 위치 ID 2500에 “Support”라는 이름의 새 부서를 추가 합니다.
․ DEPT_DEPTID_SEQ 시퀀스의 현재 값을 봅니다.
오라클 디비 예제 :
INSERT INTO departments(department_id,
department_name, location_id)
VALUES(dept_deptid_seq.NEXTVAL,
'Support', 2500);
1 row creadted.
SELECT dept_deptid_seq.CURRVAL
FROM dual;
MSSQL 디비 예제 :
--오라클에서의 시퀀시는 MSSQL의 identity 와 비슷하다.
create table tbl_identity(
rid int identity primary key,
ename varchar(20),
entdate datetime default getdate()
)
insert tbl_identity values(1, '홍길동', getdate()) --identity 속성이 있는 경우 값을 명시할 수 없다.
insert tbl_identity values('홍길동',getdate())
select * from tbl_identity insert tbl_identity
values('강감찬',getdate())
select * from tbl_identity
select @@identity -- 오라클에서의 currval와 같은 의미이다.
insert tbl_identity
values (30, '이순신', '2001-08-01')
set identity_insert tbl_identity on -- identity 속성에 값을 직접 명시하고 싶을 때
insert tbl_identity (rid,ename, entdate)
values (30, '이순신', '2001-08-01')
☞설명)
예제는 새 부서를 DEPARTMENTS 테이블에 삽입하며 새 부서 번호 생성을 위해 DEPT_DEPTID_SEQ 시퀀스를 사용한다.
시퀀스의 현재 값을 볼 수 있다.
SELECT dept_deptid_seq.CURRVAL
FROM dual;
이제 신입 사원을 고용하여 새 부서를 배치한다고 가정하자.
신입 사원 모두에 대해 실행될 INSERT 문에 다음 코드를 포함시킬 수 있다.
INSERT INTO employees (employee_id, department_id, ...)
VALUES (employees_seq.NEXTVAL, dept_deptid_seq.CURRVAL, ...);
참고 : 앞의 예제에서는 새 사원 번호를 생성하기 위해 EMPLOYEE_SEQ라는 시퀀스가 이미 생성되었다고 가정한다.
⑤ 시퀀스 값 캐시
․ 시퀀스 값을 메모리에 캐시하면 해당 값을 더 빠르게 액세스할 수 있다.
해당 시퀀스를 처음 참조할 때 캐시가 채워지면 다음 시퀀스 값을 요청할 때마다 캐시된 시퀀스에서 값이 검색된다.
마지막 시퀀스 값을 사용한 이후에 시퀀스를 요청하면 메모리에 시퀀스의 또 다른 캐시가 만들어 진다 ․ 다음과 같은 경우 시퀀스 값 사이에 공백(gap)이 생긴다.
- 롤백이 발생하는 경우
시퀀스 생성기는 공백(gap)없이 차례로 시퀀스 번호를 발행하지만 작업은 커밋이나 롤백과는 별도로 이루어진다.
따라서 시퀀스를 포함하는 명령문을 롤백하면 해당 번호를 잃게 된다.
- 시스템이 고장난 경우
시퀀스가 메모리에 값을 캐시한 후에 시스템이 고장나면 해당 값을 잃게 된다
- 시퀀스가 다른 테이블에서 사용되는 경우
시퀀스가 하나의 테이블에 전적으로 연결되어 있는 것이 아니므로 동일한 시퀀스가 여러 테이블에서 사용된다
따라서 개별 테이블 내에서는 시퀀스 번호에 공백이 생길 수 있다. ․ NOCACHE를 사용하여 시퀀스를 생성한 경우 USER_SEQUENCES 테이블을 질의하면 시퀀스를 증가시키지 않고 사용
가능한 다음 시퀀스 값을 볼 수 있다 (시퀀스 값을 증가시키지 않고 사용 가능한 다음 시퀀스 값 보기)
select * from tbl_identity
select @@identity
insert tbl_identity
values ('을지문덕', getdate()) -- set identity_insert tbl_identity on을 해주면 값을 명시해줘야 한다
set identity_insert tbl_identity off -- identity 속성을 사용하고자 할 때
insert tbl_identity
values ('김좌진', getdate())
create table tbl_identity2
(
rid int identity(2,2) primary key,
ename varchar(20),
entdate datetime default getdate()
)
insert tbl_identity2
values('홍길동', getdate())
select * from tbl_identity2
insert tbl_identity2
values('이순신', getdate())
select * from tbl_identity2
select @@identity
⑥ 시퀀스 수정
증분 값, 최대값, 최소값, CYCLE 옵션 또는 CACHE 옵션을 변경한다.
시퀀스가 MAXVALUE 한계에 도달하면 해당 시퀀스가 추가 값을 할당할 수 없으며 시퀀스가 MAXVALUE를 초과함을 알리
는 오류 메시지가 나타난다.
시퀀스를 계속 사용하려면 ALTER SEQUENCE 문을 사용하여 수정한다.
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
sequence altered.
구문
ALTER SEQUENCE sequence_name
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
☞ 시퀀스 수정에 대한 지침
․ 시퀀스를 수정하려면 시퀀스 소유자이거나 시퀀스에 대한 ALTER 권한이 있어야 한다.
․ ALTER SEQUENCE 문은 이후 시퀀스 번호에만 영향을 준다.
․ START WITH 옵션은 ALTER SEQUENCE를 사용하여 변경할 수 없다.
시퀀스를 다른 번호로 다시 시작하려면 시퀀스를 삭제한 후 다시 생성해야 한다.
․ 일부 검증이 수행된다.예를 들어, 새로운 MAXVALUE 값을 현재 시퀀스 번호보다 작게 지정할 수 없다.
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 90
NOCACHE
NOCYCLE;
ALTER SEQUENCE dept_deptid_seq
*
ERROR at line 1 :
ORA-04009 : MAXVALUE cannot be made to be less than the current value
⑦ 시퀀스 제거
․ DROP SEQUENCE 문을 사용하여 데이터 딕셔너리에서 시퀀스를 제거합니다.
․ 제거된 시퀀스는 더 이상 참조할 수 없습니다.
시퀀스를 제거하려면 시퀀스 소유자이거나 DROP ANY SEQUENCE 권한이 있어야 한다.
구문
DROR SEQUENCE sequence;
구문 설명 :
sequence는 시퀀스 생성기 이름입니다.
DROP SEQUENCE dept_deptid_seq;
SEquence dropped.