[11장] SEQUENCE와 SYNONYM(동의어)

 

 

- 데이터베이스의 데이터들 중 순서가 필요할 경우가 있음 (EX. 주문번호, 학번, 게시글 번호 등)

 

- SEQUENCE : 연속적인 번호의 생성이 필요할 경우를 위해서 오라클에서도 연속적인 번호를

   자동으로 만들어 주는 기능

- SYNONYM : 테이블에 별명을 만들어서 부르는 기능

 

1. SEQUENCE (시퀀스)

 - ​SEQUENCE 는 마치 은행의 번호표처럼 연속적인 일련 번호를 만들어 주는 기능

- 문 법:

CREATE SEQUENCE sequence_name    [INCREMENT BY n]                       ← 시퀀스 번호의 증가 값으로 기본값은 1    [START WITH n]                          ← 시퀀스 시작번호로 기본값은 1    [MAXVALUE n | NOMAXVALUE]     ← 생성 가능한 시퀀스 최대값    [MINVALUE n | NOMINVALUE]        ← CYCLE일 경우 새로 시작되는 값    [CYCLE | NOCYCLE]                      ← 시퀀스 번호를 순환 사용할 것인지 지정    [CACHE n | NOCACHE]                   ← 시퀀스 생성속도를 개선하기 위해 캐싱여부 지정

 

  

 - 사용 예)  아래의 조건으로 제품 주문번호를 생성하기 위해 사용할 SEQUENCE 를 만드세요.
       * SEQUENCE 명: seq_jumun_no
       * 시작 번호: 1000       
       * 끝 번 호: 1010
       * 증 가 값: 1
       * 반복 되고 캐싱은 2개씩 되도록 하세요.

 

SCOTT> CREATE SEQUENCE seq_jumun_no      2      INCREMENT BY 1      3      START WITH 1000      4      MAXVALUE 1010      5      MINVALUE 990      6      CYCLE      7      CACHE 2 ;

 

 

- 위에서 생성한 SEQUENCE 는 CURRVAL과 NEXTVAL을 사용하여 조회 및 사용할 수 있음

 

- CURRVAL은 현재까지 생성된 번호, 즉 가장 마지막 번호를 보여줌

- NEXTVAL은 다음에 나올번호를 보여줌

 

- SEQUENCE 에서 중요한 부분은 6번 라인에 있는 CACHE기능입니다.
 

ex) 평소 판매가격이 100만원인 제품을 선착순 1 천 명에게만 10만원에 판매하는 이벤트를 한다고

     미리 공지를 하고 주문을 받을 경우 1초에도 아주 많은 주문이 들어 올 것입니다.
     이때 주문 테이블의 주문 번호를 SEQUENCE 로 생성 할 경우 SEQUENCE에서 번호를 생성하는

    시간이 걸리므로  SEQUENCE 번호를 기다리는 대기(WAIT) 현상이 발생하게 될 것이고

    이로 인해 성능이 저하

   

     이런 경우를 조금이라도 개선하기 위해 SEQUENCE 번호를 미리 메모리상에 만들어두고(CACHE)

    SEQUENCE 번호 요청이 들어오면 즉시 번호를 할당하게 된 것

 

 

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

 

Step 1: 예제로 사용할 jumun 테이블을 아래와 같이 생성하고 데이터를 입력하겠습니다.  ​SCOTT> CREATE TABLE jumun      2      ( j_no number(4) ,      3        j_name varchar2(10)) ; ​ ​Step 2. 데이터를 입력합니다. ​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 에서 발생한 마지막 값  ​ ​Step 3. MAXVALUE / MINVALUE 항목과 CYCLE 값을 테스트 합니다. ​SCOTT> BEGIN      2      FOR i in 1..11 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  J_NO  J_NAME ----- ----------     990   DDDD               ←  SEQUENCE 생성 시 MINVALUE로 지정한 값 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 ;   ​  > 데이터를 1건 만 입력한 후 데이터베이스를 강제 종료 ​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_NAME ----- -------   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 조회 및 수정하기
    위에서 생성한 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 값은 변경이 안 됩니다.

 

 

 

4) SEQUENCE 삭제하기
    위에서 생성한 seq_jumun2_no 시퀀스를 최대값을 110 번으로 하고 CACHE 값을 5로 수정하세요.

 

SCOTT> DROP SEQUENCE seq_jumun2_no ;

 

 - SEQUENCE를 사용하는 DM이 ROLLBACK되어도 SEQUENCE는 다시 ROLLBACK 되지 않기 때문에

    만약 이럴 경우 입력되는 번호의 차이가 생길 수 있다는 점도 꼭 기억!!

 

 

 

2. SYNONYM(동의어)

 

- 동의어란 테이블에 붙이는 별명입니다.
- 이유 : 보안상 목적이나 사용자의 편의성 때문

 

1) 문 법:

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

 

 

- SYNONYM 도 스키마 오브젝트이므로 만든 사람만 사용할 수 있습니다.
   (SCOTT 이 어떤 SYNONYM 을 생성하게 되면 SCOTT만 사용할 수 있다는 뜻)

 

- 다른 사용자도 다 쓸 수 있게 생성하려면 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 ;

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.348-360