고정 헤더 영역
상세 컨텐츠
본문
1. SEQUENCE
SEQUENCE는 테이블의 행에 대한 SEQUENCE 번호를 자동적으로 생성하기 위해 사용될 수 있다. SEQUENCE는 사용자가 생성한 데이터베이스 객체이다. SEQUENCE에 대한 전형적인 사용은 각행에 대해 유일해야 하는 PRIMARY KEY 값을 생성하기 위해서 입니다. SEQUENCE는 Oracle8에 의해 발생되고 증가(또는 감소) 됩니다.
1.1 SEQUENCE 특징
1) 자동적으로 유일 번호를 생성합니다.
2) 공유 가능한 객체
3) 주로 기본 키 값을 생성하기 위해 사용됩니다.
4) 어플리케이션 코드를 대체합니다.
5) 메모리에 CACHE되면 SEQUENCE 값을 액세스 하는 효율성을 향상시킵니다.
1.2 Syntax
|
CREATE SEQUENCE sequence_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE | NOCACHE}]; |
sequence_name SEQUENCE의 이름입니다.
INCREMENT BY n 정수 값인 n으로 SEQUENCE번호 사이의 간격을 지정.
이 절이 생략되면 SEQUENCE는 1씩 증가.
START WITH n 생성하기 위해 첫번째 SEQUENCE를 지정.
이 절이 생략되면 SEQUENCE는 1로 시작.
MAXVALUE n SEQUENCE를 생성할 수 있는 최대 값을 지정.
NOMAXVALUE 오름차순용 10^27 최대값과 내림차순용-1의 최소값을 지정.
MINVALUE n 최소 SEQUENCE값을 지정.
NOMINVALUE 오름차순용 1과 내림차순용-(10^26)의 최소값을 지정.
CYCLE | NOCYCLE 최대 또는 최소값에 도달한 후에 계속 값을 생성할 지의 여부를
지정. NOCYCLE이 디폴트.
CACHE | NOCACHE 얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고 유지
하는가를 지정. 디폴트로 오라클 서버는 20을 CACHE.
문제1) DEPT 테이블의 PRIMARY KEY에 사용되는 DEPT_DEPTNO SEQUENCE를 생성 합니다.
|
SQL> CREATE SEQUENCE dept_deptno 2 INCREMENT BY 1 3 START WITH 91 4 MAXVALUE 99 5 NOCACHE 6 NOCYCLE; |
♣ 참고
CYCLE OPTION을 사용해서는 안됩니다.(PRIMARY KEY으로 사용될 경우)
1.3 SEQUENCE 학인
한번 SEQUENCE를 생성했으면 데이터 사전에 등록 됩니다. SEQUENCE가 데이터베이스 객체가 된 이후에 USER_OBJECTS DATA DICTIONARY에서 식별할 수 있습니다. 또한 데이터 사전의 USER_SEQUENCES 테이블을 검색함으로써 SEQUENCE의 설정 값을 확인할 수 있다.
문제2) 현재 SESSION을 이루고 있는 사용자가 소유하고 있는 SEQUENCE를 조회하여라.
|
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 ------------------------------ --------- --------- ------------ ----------- CUSTID 1 1.000E+27 1 109 DEPT_DEPTNO 1 99 1 91 ORDID 1 1.000E+27 1 622 PRODID 1 1.000E+27 1 200381 |
1.4 SEQUENCE 사용법
테이블에 사용할 절차적인 번호를 생성하기 위해 SEQUENCE를 사용할 수 있다. NEXTVALUE 와 CURRVALUE 의사열을 사용하여 SEQUENCE값을 참조한다.
1.4.1 NEXTVAL과 CURRVAL 의사열
가) 특징
1) NEXTVAL는 다음 사용 가능한 SEQUENCE 값을 반환 한다.
2) SEQUENCE가 참조될 때 마다, 다른 사용자에게 조차도 유일한 값을 반환한다.
3) CURRVAL은 현재 SEQUENCE값을 얻는다.
4) CURRVAL이 참조되기 전에 NEXTVAL이 사용되어야 한다.
나) NEXTVAL과 CURRVAL의 사용 규칙
1) NEXTVAL과 CURRVAL을 사용할 수 있는 경우
① SUBQUERY가 아닌 SELECT문
② INSERT문dml SELECT문
③ INSERT문의 VALUES절
④ UPDATE문의 SET절
2) NEXTVAL과 CURRVAL사용할 수 없는 경우
① VIEW문의 SELECT문
② DISTINCT 키워드를 사용한 SELECT문
③ GROUP BY, HAVING, ORDER BY를 이용한 SELECT문
④ SELECT, DELETE, UPDATE문장에서의 SUBQUERY
⑤ CREATE TABLE, ALTER TABLE명령문의 DEFAULT절
문제3) DEPT 테이블에 부서명을 영업부, 위치를 분당구 정자동을 입력하여라.
|
SQL> INSERT INTO dept 2 VALUES (DEPT_DEPTNO.NEXTVAL,'영업부','분당구 정자동'); 1 row created. |
문제4) DEPT_DEPTNO SEQUENCE의 현재 값을 확인하시오.
|
SQL> SELECT dept_deptno.CURRVAL 2 FROM dual; CURRVAL --------- 91 |
1.5 SEQUENCE 값 CACHE
SEQUENCE 값에 대해 보다 빠른 액세스를 허용하기 위해 메모리에 SEQUENCE를 CACHE합니다. CACHE는 SEQUENCE를 처음 참조할 때 형성됩니다. 다음 SEQUENCE 값에 대한 요구는 CACHE된 SEQUENCE에서 읽어 들입니다. 마지막 SEQUENCE가 사용된 후에 SEQUENCE에 요구하면 CACHE된 SEQUENCE를 메모리에 갖다 놓습니다.
1.6 SEQUENCE에서 간격의 경계
1) SEQUENCE 값에서 간격(gap)은 아래의 상황에서 발생합니다.
① ROLLBACK
② SYSTEM CRASH
③ SEQUENCE가 다른 테이블에서 사용될 때
1.7 SEQUENCE 수정
INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, CACHE을 변경할 수 있습니다.
1.7.1 Syntax
|
ALTER SEQUENCE sequence_name [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE | NOCACHE}]; |
1.7.2 SEQUENCE 수정 지침
1) SEQUENCE에 대한 ALTER 권한을 가지거나 소유자여야 합니다.
2) 이후의 SEQUENCE번호만 영향을 받습니다.
3) SEQUENCE는 다른 번호에서 SEQUENCE를 다시 시작하기 위해서는 제거하고 다시 생성하여야 합니다.
4) 유효한 검사를 수행합니다.
1.8 SEQUENCE 제거
데이터 사전에서 SEQUENCE를 제거하기 위해 DROP SEQUENCE문장을 사용합니다. SEQUENCE를 제거하기 위해서는 소요자이거나 DROP ANY SEQUENCE권한을 가져야 합니다.
1.8.1 Syntax
|
DROP SEQUENCE sequence_name; |
문제) DEPT_DEPTNO SEQUENCE를 삭제하여라
|
SQL> DROP SEQUENCE dept_deptno; Sequence dropped. |
◈ 연 습 문 제 ◈
1. 초기값1부터 최대값999,999까지 1씩 증가하는 TEST_SEQ SEQUENCE를 생성하여라.
2. 현재 SESSION을 이루고 있는 사용자가 사용할 수 있는 SRQUENCE를 조회하여라.
3. 1번에서 작성한 SRQUENCE의 현재 값을 조회하여라.
4. CURRVAL과 NEXTVAL을 설명하여라.
5. CACHE와 NOCACHE의 차이점을 설명하여라.
6. CYCLE와 NOCYCLE의 차이점을 설명하여라.
7. 1번에서 생성한 SRQUENCE를 삭제하여라.
