제목없음

과정목적

본 과정에서는 일반적으로 사용되는 다른 데이터베이스 객체 중 일부를 생성하고 유지하는 법을 배우게 됩니다. 이 객체는 시퀀스 , 인덱스, 동의어를 포함합니다.

데이터베이스 객체

많은 어플리케이션들은 유일한 숫자 값을 기본 키 값으로 사용합니다. 이 요구사항을 처리하기 위해 코드를 어플리케이션에서 구축하거나 유일한 번호를 생성하기 위해 시퀀스를 사용할 수 있습니다.

일부 질의의 성능을 향상시키길 원한다면, 인덱스의 생성을 고려해야 합니다. 또한 열 또는 열집합에서 유일성을 강화시키기 위해 인덱스를 사용할 수 있습니다. 동의어를 사용하여 객체를 위한 대체명을 제공할 수 있습니다.

시퀀스란?

시퀀스는 테이블의 행에 대한 시퀀스 번호를 자동적으로 생성하기 위해 사용될 수 있습니다. 시퀀스는 사용자가 생성한 데이터베이스 객체이며 여러 사용자가 공유할 수 있습니다. 시퀀스에 대한 전형적인 사용은 각 행에 대해 유일해야 하는 기본 키 값을 생성하기 위해서 입니다. 시퀀스는 내부 Oracle8 루틴에 의해 발생되고 증가(또는 감소)됩니다.

시퀀스 번호는 테이블과 관계없이 생성되고 저장됩니다. 그러므로 동일한 시퀀스는 다중 테이블에 대해 사용될 수 있습니다.

시퀀스 생성

CREATE SEQUENCE 문장을 사용하여 절차적인 번호를 자동적으로 생성합니다.

구문에서:

sequence	시퀀스의 이름입니다.
INCREMENT BY n	정수 값인 n으로 시퀀스 번호 사이의 간격을 지정합니다. 이 절이 생략되면 시퀀스는 1씩 증가합니다.
START WITH n	생성하기 위해 첫번째 시퀀스를 지정합니다. 이 절이 생략되면, 시퀀스는 1로 시작됩니다.
MAXVALUE n	시퀀스를 생성할 수 있는 최대값을 지정합니다.
NOMAXVALUE	오름차순용 10^27 과 내림차순용 -1의 최대값을 지정합니다. 이것은 디폴트 옵션입니다.
MINVALUE n	최소 시퀀스 값을 지정합니다.
NOMINVALUE	오름차순용 1과 내림차순용 -(10^26)의 최소값을 지정합니다. 이것은 디폴트 옵션입니다.
CYCLE \| NOCYCLE	최대 또는 최소값에 도달한 후에 계속 값을 생성할 지의 여부를 지정합니다. NOCYCLE이 디폴트 옵션입니다.
CACHE n \| NOCACHE	얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고 유지하는가를 지정합니다. 디폴트로 오라클 서버는 20을 캐쉬합니다.

주: NOMINVALUE, NOMAXVALUE에서의 "오름차순"과 "내림차순"은 INCREMENT BY n에서 n이 양수(오름차순), 혹은 음수(내림차순)일 경우를 나타냅니다.

시퀀스작성(계속)

위 예에서 DEPT 테이블의 DEPTNO에 대해 사용되는 DEPT_DEPTNO라는 시퀀스를 생성합니다. 시퀀스는 91에서 시작하며, 캐쉬를 허용치 않으며, 사이클을 허용치 않습니다.

시퀀스 사이클보다 더 빨리 오래된 행을 지우는 신뢰할 만한 메커니즘을 가지고 있지 않은 경우에, 기본 키 값을 생성하기 위해 시퀀스가 사용된다면 CYCLE 옵션을 사용해서는 안됩니다.

자세한 내용은 Oracle Server SQL Reference, Release 7.3 또는 8.0, “CREATE SEQUENCE.”를 참조하십시오.

시퀀스 확인

한번 시퀀스를 생성했으면, 그것은 데이터 사전에 문서화됩니다.시퀀스가 데이터베이스 객체가 된 이후에 USER_OBJECTS 데이터 사전 테이블에서 식별할 수 있습니다.
또한 데이터 사전의 USER_SEQUENCES 테이블을 검색함으로써 시퀀스의 설정을 확인할 수 있습니다.

  SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
  ------------- --------- --------- ------------ -----------
  CUSTID                 1 1.000E+27            1         109
  DEPT_DEPTNO            1       100            1          91
  ORDID                  1 1.000E+27            1         622
  PRODID                 1 1.000E+27            1      200381

시퀀스 사용

일단 시퀀스를 작성했다면, 테이블에 사용할 절차적인 번호를 생성하기 위해 시퀀스를 사용할 수 있습니다. NEXTVAL과 CURRVAL 의사열을 사용하여 시퀀스 값을 참조하십시오.

NEXTVAL과 CURRVAL 의사열

NEXTVAL pseudocolumn은 지정된 시퀀스에서 성공적으로 시퀀스 번호를 빼내기 위해 사용됩니다. 시퀀스 명으로써 NEXTVAL을 참조합니다. sequence.NEXTVAL을 참조할 때는 새 시퀀스 번호가 생성되고 현재 시퀀스 번호는 CURRVAL에 위치하게 됩니다.

CURRVAL pseudocolumn은 막 생성된 현재 사용자의 시퀀스 번호를 참조하기 위해 사용됩니다. 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 표현식

자세한 내용을 보시려면, Oracle Server SQL Reference, Release 7.3 또는 8.0, "Pseudocolumns" 섹션과 "CREATE SEQUENCE."를 참조하십시오.

시퀀스 사용

슬라이드 예에서 DEPT 테이블에 새 부서를 삽입합니다. 새 부서번호를 생성하기 위해 DEPT_DEPTNO 시퀀스를 사용합니다.

SQL> SELECT   dept_deptno.CURRVAL
  2 FROM     dual;

CURRVAL
-------
      91

지금 새부서의 스탭이 되는 종업원을 고용하기를 원한다고 가정하십시오. 모든 새 종업원에 대해 반복적으로 실행될 수 있는 INSERT 문장은 아래 코드를 포함할 수 있습니다.

SQL> INSERT INTO emp ...
2 VALUES (emp_empno.NEXTVAL, dept_deptno.CURRVAL, ...

주: 위의 예는 시퀀스 EMP_EMPNO가 새 종업원 번호 생성을 이미 완료했음을 가정합니다.

시퀀스 값 캐쉬

시퀀스 값에 대해 보다 빠른 액세스를 허용하기 위해 메모리에 시퀀스를 캐쉬합니다. 캐쉬는 시퀀스를 처음에 참조할 때 형성됩니다. 다음 시퀀스 값에 대한 각 요구는 캐쉬된 시퀀스에서 읽어들입니다. 마지막 시퀀스가 사용된 후에 시퀀스에 요구하면 캐쉬된 시퀀스를 메모리에 갖다 놓습니다.

시퀀스에서 간격의 경계

시퀀스 생성자는 간격(gap)없이 절차적인 번호를 발생하는데, 이 작업은 커밋 또는 롤백과 관계없이 발생합니다. 그러므로, 시퀀스를 포함한 문장을 롤백한다면, 번호는 손실됩니다. 시퀀스에서 간격(gap)의 또다른 원인이 될 수 있는 경우는 시스템 실패(crash)입니다. 메모리에서 시퀀스 값을 캐쉬한다면, 시스템 실패(crash)일 때 이 값들은 손실됩니다.

시퀀스가 직접적으로 테이블에 고정되어 있지 않기 때문에 동일 시퀀스가 다중 테이블에서 사용되어 질 수 있습니다. 이 상황이 발생한다면, 각 테이블은 순차적인 번호에 간격이 생길 수 있습니다.

증가 없이 다음 사용 가능 시퀀스 값 뷰

USER_SEQUENCES 테이블을 질의하여 NOCACHE로 시퀀스가 생성된 경우에만, 증가 없이 다음 사용 가능 값을 볼 수 있습니다.

시퀀스 수정

시퀀스에 대해 MAXVALUE 한계에 도달한다면, 더 이상 시퀀스 값을 할당 받을 수 없을 것이고, MAXVALUE를 초과하는 시퀀스를 나타내는 에러를 받을 것입니다. 시퀀스를 계속 사용하기 위해, ALTER SEQUENCE 문장을 사용하여 수정할 수 있습니다.

구문

ALTER SEQUENCE  sequence
        [INCREMENT BY n]
        [{MAXVALUE n | NOMAXVALUE}]
        [{MINVALUE n | NOMINVALUE}]
        [{CYCLE | NOCYCLE}]
        [{CACHE n | NOCACHE}];

여기서: sequence 시퀀스 생성자의 이름입니다.

보다 자세한 내용은 Oracle Server SQL Reference, Release 7.3 또는 8.0, “ALTER SEQUENCE.”를 참조하십시오

시퀀스 제거

데이터 사전에서 시퀀스를 제거하기 위해, DROP SEQUENCE 문장을 사용합니다. 시퀀스를 제거하기 위해 시퀀스의 소유자이거나 DROP ANY SEQUENCE 권한을 가져야 합니다.

구문

DROP SEQUENCE sequence;

여기서: sequence 시퀀스 생성자의 이름입니다.

자세한 내용은 Oracle Server SQL Reference, Release 7.3 또는 8.0, “DROP SEQUENCE.” 를 참조하십시오.

지침(음성 설명은 없습니다)

시퀀스를 수정하기 위해서는 시퀀스에 대한 ALTER 권한을 가지거나 소유자이어야 합니다.
이후 시퀀스 번호만이 ALTER SEQUENCE 문장에 의해 영향을 받습니다.
START WITH 옵션은 ALTER SEQUENCE를 사용하여 변경될 수 없습니다. 시퀀스는 다른 번호에서 시퀀스를 다시 시작하기 위해서는 제거하고 다시 생성되어야 합니다.
유효성 검사를 수행합니다. 예를 들면, 새 MAXVALUE는 현재 시퀀스 번호보다 작게 될 수 없습니다.

SQL> ALTER SEQUENCE dept_deptno
  2        INCREMENT BY 1
  3        MAXVALUE 90
  4        NOCACHE
  5        NOCYCLE;
ALTER SEQUENCE dept_deptno
*
ERROR at line 1:
ORA-04009: MAXVALUE cannot be made to be less than the current value

SEQUENCE( test_seq)를 사용하여 EMP table의 Primary Key인 empno에 값을 입력하는 경우로 적당한 것은?)
A. insert into emp ( empno) values( test_seq.sequence);
B. insert into emp ( empno) values( test_seq.currentval);
C. insert into emp ( empno) values( test_seq.currval);
D. insert into emp ( empno) values( test_seq.nextval);
E. insert into emp ( empno) values( nextval.test_seq);
답

X 정답:D

SEQUENCE를 생성할 때 START WITH n 을 지정하지 않으면 해당 SEQUENCE의 시작번호는?
답

X 정답:1

다음 명령문을 실행하면 결과로 나오는 값은?
SELECT s_dept_id.CURRVAL
FROM SYS.dual;

A. s_dept_id sequence의 현재 값
B. s_dept_id cursor의 현재 값
C. s_dept_id index의 현재 값
D. dual table의 s_dep_id column의 값
답

X 정답:A