1. SEQUENCE (시퀀스)
마치 은행의 번호표처럼 연속적인 일련번호를 만들어주는 기능
CREATE SEQUENCE sequence_name
INCREMENT BY n 시퀀스 번호의 증가 값으로 기본값은 1
START WITH n 시퀀스 시작 번호로 기본값은 1
MAXVALUE n | NOMAXVALUE 생성가능한 시퀀스 최대값
MINVALUE n | NOMINVALUE CYCLE일 경우 새로 시작되는 값
CYCLE | NOCYCLE 시퀀스 번호를 순환 사용할 것인지 지정 기본값은 NOCYCLE
CACHE n | NOCACHE 시퀀스 생성속도를 개선하기 위해 캐싱 여부 지정
사용 예1. 아래의 조건으로 제품 주문번호를 생성하기 위해 사용할 SEQUENCE 를 만드세요.
SEQUENCE 명 : seq_jumun_no
시작번호 : 1000
끝번호 : 1010
증가값 : 1
반복되고 캐싱은 2개씩 되도록 하세요.
CREATE SEQUENCE seq_jumun_no
INCREMENT BY 1
START WITH 1000
MAXVALUE 1010
MINVALUE 990
CYCLE
CACHE 2 ;
(1) SEQUENCE 생성 및 옵션 확인하기
STEP1 예제로 사용할 jumun1 테이블을 아래와 같이 생성하고 데이터를 입력
CREATE TABLE jumun1
(
j_no number(4),
j_name varchar2(10)
) ;
STEP2 데이터 입력
INSERT INTO jumun1
VALUES (seq_jumun1_no.NEXTVAL, 'AAAA') ;
SELECT * FROM jumun1 ;
INSERT INTO jumun1
VALUES (seq_jumun1_no.NEXTVAL, 'BBBB') ;
SELECT * FROM jumun1 ;
SELECT seq_jumun1_no.CURRVAL FROM dual ;
SEQUENCE 사용법은 "SEQUENCE이름. 함수" 이다.
NEXTVAL 이란 함수는 현재 값보다 다음 값을 가져오라는 함수이므로 계속 값이 증가함을 볼 수 있다.
STEP 3 MAXVALUE / MINVALUE 항목과 CYCLE 값을 테스트한다.
BEGIN
FOR i in 1..11 loop
INSERT INTO jumun1 VALUES (seq_jumun1_no.NEXTVAL, DBMS_RANDOM.STRING ('A', 4) ) ;
END LOOP ;
COMMIT ;
END ;
/
SELECT * FROM jumun1 ;
INSERT INTO jumun1
VALUES (seq_jumun_no.NEXTVAL, 'DDDD') ;
SELECT * FROM jumun1 ;
위 테스트에서 알 수 있듯이 MAXVALUE 값을 다 사용한 상태에서 CYCLE로 설정되면 다시 돌아가는데
그 시작 번호는 MINVALUE 로 설정된 값임을 알 수 있다.
STEP4 NOCYCLE 설정과 CACHE 설정을 테스트한다.
CREATE TABLE jumun2
(
j_no number(4),
j_name varchar2(4)
) ;
CREATE SEQUENCE seq_jumun2_no
INCREMENT BY 1
START WITH 100
MAXVALUE 105
CACHE 2 ;
데이터를 1건만 입력한 후 데이터베이스 강제 종료
INSERT INTO jumun2
VALUES (seq_jumun2_no.NEXTVAL, 'AAAA') ;
COMMIT ;
CONN / AS SYSDBA ;
shutdown abort ;
STARTUP ;
CONN scott/tiger
SELECT * FROM jumun2 ;
INSERT INTO jumun2
VALUES (seq_jumun2_no.NEXTVAL, 'BBBB') ;
SELECT * FROM jumun2 ;
J_NO J_NA
---------- ----
100 AAAA
102 BBBB
원래 101번이 입력되어야 하지만 시퀀스 생성할 때 CACHE 값을 2로 주어서 100, 101번이 캐싱되어 있다가
서버가 비정상 종료되어 캐싱되었던 101번이 사라지고 102번이 입력된 것이다.
seq_jumun2_no 시퀀스는 생성될 때 CYCLE 이라는 옵션을 주지 않았다.
이 옵션을 주지 않고 생성하면 기본값은 NOCYCLE 이다.
아래와 같이 MAXVALUE 를 초과하여 값을 요청하면 에러가 발생한다.
INSERT INTO jumun2
VALUES (seq_jumun2_no.NEXTVAL, 'FFFF') ;
INSERT INTO jumun2
VALUES (seq_jumun2_no.NEXTVAL, 'GGGG') ;
INSERT INTO jumun2
VALUES (seq_jumun2_no.NEXTVAL, 'HHHH') ;
INSERT INTO jumun2
VALUES (seq_jumun2_no.NEXTVAL, 'ZZZZ') ;
ERROR at line 2:
ORA-08004: sequence SEQ_JUMUN2_NO.NEXTVAL exceeds MAXVALUE and cannot be
instantiated
(2) 값이 감소하는 SEQUENCE 생성 및 사용하기
CREATE SEQUENCE s_rev
INCREMENT BY -1
MINVALUE 0
MAXVALUE 10
START WITH 2 ;
INSERT INTO s_test1 VALUES (s_rev.NEXTVAL) ;
INSERT INTO s_test1 VALUES (s_rev.NEXTVAL) ;
INSERT INTO s_test1 VALUES (s_rev.NEXTVAL) ;
2 부터 시작해서 1씩 감소하기 때문에 세번째 INSERT 에서 에러 발생
(3) SEQUENCE 조회 및 수정하기
앞에서 생성한 seq_jumun2_no 시퀀스를 최대값 110, CACHE 5로 수정하세요.
SELECT sequence_name,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
FROM user_sequences
WHERE sequence_name = 'SEQ_JUMUN2_NO' ;
ALTER SEQUENCE seq_jumun2_no
MAXVALUE 110
CACHE 5 ;
※ SEQUENCE 를 변경할 때 START WITH 값은 변경 안된다.
(4) SEQUENCE 삭제하기
DROP SEQUENCE seq_jumun2_no ;
2. SYNONYM (동의어)
동의어란 테이블에 붙이는 별명이다.
(1) 문법
CREATE [PUBLIC] SYNONYM synonym이름
FOR [schema.]대상객체 ;
SYNONYM 도 스키마 오브젝트 이므로 만든 사람만 사용할 수 있다.
그래서 다른 사용자도 다 쓸수 있게 생성하려면 PUBLIC이란 키워드를 사용해서 생성해야만 한다.
동의어를 생성하려면 CREATE SYNONYM 권한이 필요하므로 권한을 먼저 할당해야 한다.
CONN / AS SYSDBA
GRANT CREATE SYNONYM TO SCOTT ;
GRANT CREATE PUBLIC SYNONYM TO SCOTT ;
CONN SCOTT/TIGER
(2) 생성 예
CREATE SYNONYM e2 FOR emp2 ;
CREATE PUBLIC SYNONYM d2 FOR department ;
(3) SYNONYM 조회하기
SELECT synonym_name, table_owner, table_name
FROM user_synonyms
WHERE table_name = 'EMP2' ;
(4) SYNONYM 삭제하기
DROP SYNONYM e2 ;
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저