No.011 SEQUENCE [2017-06-07]

1, SEQUENCE

 SEQUENCE는 테이블의 행에 대한 SEQUENCE 번호를 자동적으로 생성하기 위해 사용될 수 있다. SEQUENCE는 사용자가 생성한 데이터베이스 객체이다. SEQUENCE에 대한 전형적인 사용은 각행에 대해 유일해야 하는 PRIMARY KEY 값을 생성하기 위해서 입니다. SEQUENCE 는 Oracle8에 의해 발생되고 증가(또는 감소) 됩니다.

 

1.1 SEQUENCE 특징

 1) 자동적으로 유일 번호를 생성합니다.

 2) 공유 가능한 객체

 3) 주로 기본 키 값을 생성하기 위해 사용됩니다.

 4) 어플리케이션 코드를 대체합니다.

 5) 메모리에 CACHE되면 SEQUENCE 값을 액세스 하는 효율성을 향상시킵니다.

 

1.2 Syntax

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를 생성 합니다.

 

참고

 CYCLE OPTION을 사용해서는 안됩니다. (PRIMARY KEY으로 사용될 경우)

 

1.3 SEQUENCE 확인

 한번 SEQUENCE 를 생성했으면 데이터 사전에 등록 됩니다. SEQUENCE 가 데이터베이스 객체가 된 이후에 USER_OBJECTS DATA DICTIONARY에서 식별할 수 있습니다. 또한 데이터 사전의 USER_SEQUENCES 테이블을 검색함으로써 SEQUENCE의 설정 값을 확인할 수 있다.

 

문제 2) 현재 SESSION을 이루고 있는 사용자가 소유하고 있는 SEQUENCE를 조회하여라.

 

1.4 SEQUENCE 사용법

 테이블에 사용할 절차적인 번호를 생성하기 위해 SEQUENCE를 사용할 수 있다. NEXTVALUE와 CURRVALUE 의사열을 사용하여 SEQUENCE 값을 참조한다.

 

1.4.1 NEXTVAL 과 CURRVAL 의사열

가) 특징

 1) NEXTVAL는 다음 사용 가능한 SEQUENCE 값을 반환 한다.

 2) SEQUENCE가 참조될 때 마다, 다른 사용자에게 조차도 유일한 값을 반환한다.

 3) CURRVAL은 현재 SEQUENCE 값을 얻는다.

 4) CURRBAL이 참조되기 전에 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 테이블에 부서명을 영업부, 위치를 분당구 정자동을 입력하여라.

 

문제 4) DEPT_DEPTNO SEQUENCE의 현재 값을 확인하시오.

 

1.5 SEQUENCE 값 CACHE

 SEQUENCE 값에 대해 보다 빠른 액세스를 허용하기 위해 메모리에 SEQUENCE를 CACHE 합니다. 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

 

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

 

문제 ) DEPT_DEPTNO SEQUENCE를 삭제하여라