[SQL]시퀀스 만들기(생성,확인,실행,수정,삭제,동의어)

/*시퀀스 만들기*/
CREATE SEQUENCE SEQ_DEPT_SEQUENCE
INCREMENT BY 10
START WITH 10
MAXVALUE 90
MINVALUE 0
NOCYCLE
CACHE 2;

SELECT * FROM USER_SEQUENCES;

INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL,'DATABASE','SEOUL');

SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;

 

/*가장 마지막으로 생성된 시퀀스 확인*/
SELECT SEQ_DEPT_SEQUENCE.CURRVAL FROM DUAL;

 

/*순번을 반복사용하여 INSERT문 실행*/
INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL,'DATABASE','SEOUL');

/*시퀀스 수정*/
ALTER SEQUENCE SEQ_DEPT_SEQUENCE
INCREMENT BY 3
MAXVALUE 99

CYCLE;

 

/*CYCLE은 최댓값 도달 후 다시 수행*/
INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL,'DATABASE','SEOUL');

/*시퀀스 삭제*/
DROP SEQUENCE SEQ_DEPT_SEQUENCE;

/*13-5동의어(공식별칭지정)*/
CREATE SYNONYM E FOR EMP;
SELECT * FROM E;
DROP SYNONYM E;