11. SEQUENCE와 SYNONYM(동의어) _ 실습해보고다시정리

11장. SEQUENCE와 SYNONYM(동의어)를 배웁니다.

 

1. SEQUENCE (시퀀스)

: 은행의 번호표처럼 연속적인 일련 번호를 만들어주는 기능. (연속적으로 증가하거나, 연속적으로 감소)

* 문법

 

CREATE  SEQUENCE sequence_name     [INCREMENT BY n]                      <- 시퀀스 번호의 증가 값으로 기본값은  1   [START WITH n]                           <- 시퀀스 시작번호로 기본값은  1   [MAXVALUE n | NOMAXVALUE]   <- 생성 가능한 시퀀스 최대값 (38자리까지 나옴.)   [MINVALUE n | NOMINVALUE]      <-CYCLE일 경우 새로 시작되는 값과 감소하는 시퀀스일 경우 최소값   [CYCLE | NOCYCLE]                  <- 시퀀스 번호를 순환 사용할 것인지 지정   [CACHE n | NOCACHE]               <- 시퀀스 생성속도를 개선하기 위해 캐싱 여부 지정                                                      (cache는 미리 번호를 뽑아놔서 시퀀스의 생성속도를 개선하는 것이므로 꼭 써야함.)

 

*면접tip

- 시퀀스가 롤백이 되느냐? : 시퀀스는 롤백이 되지 않는다. (은행 번호표를 다시 집어 넣을 수 없듯이)

- cache : 작업이 한꺼번에 몰려왔을 때를 대비해서 미리 저장해두는 것.

( 은행을 예를들면, 사람이 한꺼뻔에 몰려왔을 때를 대비해서 번호표를 미리 다 뽑아놓고, 사람들 들어오면 바로바로 주는 것.)

 

 

 

1) SEQUENCE 생성 및 옵션 확인하기

Step 1: 예제로 사용할  jumun 테이블을 아래와 같이 생성하고 데이터를 입력.

  SCOTT>CREATE TABLE jumun
   2  ( j_no  number(4) ,
   3     j_name varchar2(10)) ;

Step 2. 데이터를 입력합니다.
이때  SEQUENCE 를 어떻게 활용해서 데이터를 입력하는지 잘 보세요.

 

SCOTT>INSERT INTO jumun    2  VALUES(seq_jumun_no.NEXTVAL,'AAAA');   1 row created.   SCOTT>SELECT * FROM jumun ;       J_NO     J_NAME ---------- ------------     1000    AAAA   SCOTT>INSERT INTO jumun    2  VALUES(seq_jumun_no.NEXTVAL,'BBBB') ;   1 row created.   SCOTT>SELECT * FROM jumun ;       J_NO     J_NAME ---------- ----------     1000    AAAA      1001    BBBB   SCOTT>SELECT seq_jumun_no.CURRVAL FROM dual;      CURRVAL ---------------        1001

 

이 예에서 볼 수 있듯이
SEQUENCE 의 사용법은
“SEQUENCE이름.함수” 입니다.
NEXTVAL  이란  함수는  현재
값의 다음 값을 가져오라는 함
수라서 계속 값이 증가함을 볼
수 있습니다.  

 

Seq_jumun_no.CURRVAL 값을 조회하니까 현재  SEQUENCE 에서 발생한 마지막 값을 볼 수 있습
니다.

 

Step 3. MAXVALUE / MINVALUE 항목과 CYCLE 값을 테스트 합니다.
이번에는 MAXVALUE 값만큼 다 사용을 할 경우 어떻게 되는지 와  CYCLE 항목과 MINVALUE 항
목이 어떤 의미인지 살펴보겠습니다.

 

SCOTT> BEGIN    2   FOR i in 1..9 LOOP    3    INSERT INTO jumun VALUES(seq_jumun_no.NEXTVAL ,    4                                DBMS_RANDOM.STRING('A',4) );    5    END LOOP;    6  COMMIT ;    7    END ;    8 /   PL/SQL procedure successfully completed.   SCOTT>SELECT * FROM jumun ;      J_NO     J_NAME ---------- ----------     1000   AAAA     1001    BBBB     1002    ldXB     1003   UEJk     1004    ywWy     1005   hHAy     1006   wvhJ     1007   pWvQ     1008   ueod     1009    Iqeg     1010   UwuX   11 rows selected.  11건의 데이터가 모두 입력되고  SEQUENCE 번호도  1000 – 1010번 까지 모두 소진되었습니다. 이 상태에서 데이터를 한 건 더 입력하고 조회 해 보겠습니다.     SCOTT>INSERT INTO jumun    2  VALUES(seq_jumun_no.NEXTVAL,'DDDD') ;   1 row created.   SCOTT>SELECT * FROM jumun ;      J_NO      J_NAME ---------- ----------     1000   AAAA     1001    BBBB     1002    ldXB     1003   UEJk     1004    ywWy     1005   hHAy     1006   wvhJ     1007   pWvQ      1008   ueod     1009    Iqeg     1010   UwuX      990   DDDD    12 rows selected.

 

위 테스트에서 알  수 있듯이  MAXVALUE 값을 다 사용한 상태에서  CYCLE  로 설정이  되면 다시
돌아가는 데 그 시작번호는 MINVALUE로 설정된 값임을 알 수 있습니다.  

 

 

Step 4. NOCYCLE 설정과 CACHE 설정을 테스트 합니다.
이 실습을 하기 위해  SEQUENCE 와 테이블을 하나씩 더 생성하겠습니다.

 

SCOTT>CREATE TABLE jumun2    2  ( j_no  number(4) ,    3    j_name varchar2(4) );   Table created.   SCOTT>CREATE SEQUENCE seq_jumun2_no    2  INCREMENT BY 1    3  START WITH 100    4  MAXVALUE 105    5  CACHE 2 ;   Sequence created.

 

데이터를  1건 만 입력한 후 데이터베이스를 강제 종료하겠습니다.
CACHE 속성으로 인해 캐싱된  Sequence 번호는 서버가 종료되면 모두 손실되는 문제가 있습니다.
이번 실습에서  DB를 강제 종료 하는 이유는  2 개의 번호를  (100,101) 캐싱한 후  100 번은 사용
하고  101 번을 사용하지 않은 상태에서 DB 를 강제종료 시켜 정말 손실되는지 테스트를 하기 위
함입니다.

 

 

SCOTT>INSERT INTO jumun2    2  VALUES(seq_jumun2_no.NEXTVAL,'AAAA') ; 1 row created.   SCOTT>COMMIT; Commit complete.   SCOTT>CONN / AS SYSDBA ; Connected.   SYS>SHUTDOWN ABORT; ORACLE instance shut down.   SYS>STARTUP ORACLE instance started. … Database opened.   SYS>CONN scott/tiger   Connected.   SCOTT>SELECT * FROM jumun2;       J_NO    J_NAME ---------- ----------       100   AAAA   SCOTT>INSERT INTO jumun2    2  VALUES(seq_jumun2_no.NEXTVAL,'BBBB') ;   1 row created.   SCOTT>SELECT * FROM jumun2;        J_NO    J_NA ---------- ---------     100   AAAA       102   BBBB   원래  101  번이  입력되어야  하지만  시퀀스  생성할 때  CACHE  값을  2로  주어서  100, 101 번이 캐싱되 어있다가  서버가  비정상  종료되어서  캐싱되었던 101 번이 사라지고  102 번이 입력이 된 것입니다.

 

Seq_jumun2_no 시퀀스는 생성될 때  CYCLE 라는 옵션을 주지 않았습니다. 이 옵션을 주지 않고
생성하면 기본값은 NOCYCLE 입니다.
 
아래와 같이 MAXVALUE 를 초과하여 값을 요청하면 에러를 발생하게 됩니다.

 

 

SCOTT>INSERT INTO jumun2    2  VALUES(seq_jumun2_no.NEXTVAL , 'FFFF') ; VALUES(seq_jumun2_no.NEXTVAL , 'FFFF')         * ERROR at line 2: ORA-08004: sequence SEQ_JUMUN2_NO.NEXTVAL exceeds MAXVALUE and cannot be instantiated

 

2) 값이 감소하는  SEQUENCE 생성 및 사용하기
 
SCOTT>CREATE SEQUENCE s_rev
   2   INCREMENT BY -1
   3  MINVALUE 0
   4  MAXVALUE 10
   5   START WITH 5 ;
 
Sequence created.
 
SCOTT>INSERT INTO s_test1 VALUES (s_rev.NEXTVAL);
 
1 row created.
 
SCOTT>/
 
1 row created.
 
SCOTT>/
 
1 row created.
 
SCOTT>/
 
1 row created.
 
SCOTT>/
 
1 row created.
 
SCOTT>/
 
1 row created.
 

SCOTT>/
insert into s_test1 values (s_rev.nextval)
*
ERROR at line 1:
ORA-08004: sequence S_REV.NEXTVAL goes below MINVALUE and cannot be instantiated

 

SCOTT>SELECT    *    FROM s_test1;
 
        NO
----------------
          5
          4
          3
          2
          1
          0
 
6 rows selected.
 
위 실습으로 감소하는 시퀀스를 생성하고 사용하는 방법을 살펴 보았습니다.  

 

 

3) Sequence 초기화 하기
일반적으로  Sequence 는 초기화 하는 경우도 드물지만 정말 업무상 반드시 초기화를 해야 할 경
우도 있습니다. 그런 경우에는 아래와 같이 프로시저를 생성해서 수행하면 됩니다.
아래 프로시저를 참고하세요.
 
================================================================
SCOTT> CREATE OR REPLACE PROCEDURE re_seq
   2  (
   3      SNAME IN VARCHAR2
   4  )
   5  IS
   6    VAL NUMBER;
   7  BEGIN
   8    EXECUTE IMMEDIATE 'SELECT ' || SNAME || '.NEXTVAL FROM DUAL ' INTO  VAL;
   9    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SNAME || ' INCREMENT BY -' || VAL || ' MINVALUE 0';
  10    EXECUTE IMMEDIATE 'SELECT ' || SNAME || '.NEXTVAL FROM DUAL ' INTO VAL;
  11    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SNAME || ' INCREMENT BY 1 MINVALUE 0';
  12 END;
  13 /
 
Procedure created.
 
SQL> CREATE  SEQUENCE    seq_test;   <---- 테스트용 시퀀스를 생성합니다.
 
Sequence created.
 

SQL> select seq_test.nextval from dual;
 
   NEXTVAL
------------
          1
 
SQL> /
 
   NEXTVAL
------------
          2
 
SQL> /
 
   NEXTVAL
------------
          3
 
SQL> /
 
   NEXTVAL
------------
          4
 
SQL> /
 
   NEXTVAL
------------
          5   <-  마지막 추출된 값이  5 입니다.

 

SQL> EXEC re_seq('SEQ_TEST');   <- 시퀀스 이름을 꼭 홑따옴표 안에 대문자로 쓰세요.
 
PL/SQL procedure successfully completed.
 
SQL> select seq_test.currval from dual;
 
   CURRVAL
----------------
          0    <--- 초기화 된 것이 확인됩니다.

 

4)SEQUENCE 조회 및 수정하기
 
위에서 생성한  seq_jumun2_no 시퀀스를 조회 한 후 최대값을  110 번으로 하고  CACHE 값을  5로
수정하겠습니다.
먼저 현재 생성되어 있는  SEQUENCE 내용을 조회하겠습니다.

 

 

SCOTT>COL 이름  FOR a15 ; SCOTT>SELECT sequence_name "이름" ,    2          MIN_VALUE "MIN 값" ,    3          MAX_VALUE "최대값" ,    4          INCREMENT_BY "증가값" ,    5          CYCLE_FLAG "CYCLE?" ,    6          ORDER_FLAG "정렬" ,    7          CACHE_SIZE "캐싱값",    8          LAST_NUMBER "마지막생성값"    9  FROM user_sequences   10  WHERE sequence_name='SEQ_JUMUN2_NO' ;   이름                   MIN 값   최대값   증가값   C      캐싱값    마지막생성값 ---------------------- ------------ ---------- ---------- --- --- ---------- ------------------ SEQ_JUMUN2_NO          1      105       1    N   N      2          106   SCOTT>ALTER SEQUENCE seq_jumun2_no    2  MAXVALUE 110    3  CACHE 5 ;   Sequence altered.

 
SEQUENCE 를 변경할 때  START WITH 값은 변경이 안 됩니다.
 
5) SEQUENCE 삭제하기
 
  SCOTT>DROP SEQUENCE seq_jumun2_no ;

 

SEQUENCE를 사용하는  DML이  ROLLBACK 되어도  SEQUENCE는 다시  ROLLBACK 되지 않기 때문
에 만약 이럴 경우 입력되는 번호의 차이가 생길 수 있다는 점도 꼭 기억하시길 바랍니다.

 

 

 

2. SYNONYM (동의어) : 테이블에 붙이는 별명 (보안상 목적이나 사용자의 편의성 때문)

1) 문법

CREATE [PUBLIC] SYNONYM  synonym이름  FOR [schema.]대상객체  ;

- PUBLIC : 다른 사용자도 다 쓸 수 있게 생성 (대부분 PUBLIC으로 만든다.)

- CREATE SYNONYM 이라는 권한이 필요하므로 권한을 먼저 할당 해야 한다.

SCOTT>CONN / AS SYSDBA Connected.   SYS>GRANT create synonym TO scott ; SYS>GRANT create public synonym TO scott ;   SYS>CONN scott/tiger Connected.

 

 

2) 생성 예

 (1) Scott 사용자의  emp2 테이블의 동의어를  e2 로 생성하세요. 
   SCOTT>CREATE SYNONYM e2 FOR emp2 ;

 
 (2) Scott 사용자의  department 테이블의 동의어를  d2 로 생성하되 모든 사용자들이 사용 할 수 있도록 생성.
   SCOTT>CREATE PUBLIC SYNONYM d2 FOR department ;

 
(3) SYNONYM 조회하기 
   SCOTT>SELECT synonym_name, table_owner , table_name
      2  FROM user_synonyms
      3  WHERE table_name='EMP2' ; 
   SYNONYM_NAME     TABLE_OWNER      TABLE_NAME
   --------------------- --------------------   ------------------
   E2                 SCOTT              EMP2 
  
(4) SYNONYM 삭제하기 
SCOTT>DROP SYNONYM e2 ;

 

 

 

 

---------------------------------------------------------------------------------------

<면접>

* 시퀀스번호를 뽑고 롤백을하면 시퀀스는 롤백되지않고, 이미 뽑히면 끝이다!

* dba는 PLSQL을 꼭배워야됨.