- 데이터베이스의 데이터들 중 순서가 필요할 경우가 있음 (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 삭제하기
서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.348-360