Oracle Sequence 정리

SEQUENCE 선언하기

문법
CREATE SEQUENCE 시퀀스이름 START WITH 1 INCREMENT BY 증가숫자 MAXVALUE 최대값  nocycle cache 20;
--시퀀스이름 SEQUENCE명입니다. START WITH는 시작 번호 INCREMENT는 증가 번호입니다.
--MAXVALUE는 최대 시퀀스 값입니다. 최대값까지 가면 처리가 안되고 에러가 납니다.
--MAXVALUE는 생략시 10의 27승까지 가능하다.
--cycle는 최대값까지 간다음에 다시 start with부터 시작하지만 nocycle는 최대값을 간후에 멈춘다.
--cache는 메모리에 캐쉬하여 사용한다. nocache는 메모리에 캐쉬하여 사용안한다.

예제
create sequence dept_deptno
increment by 1
maxvalue 100
nocache
nocycle;

SEQUENCE 사용하기(선언후에 insert 에 값처럼 사용합니다.)

seq_bbs.nextval;
--매번 증가되는 연속값(유동)
seq_bbs.currval;
--마지막 연속값(고정)

※ seq_bbs.nextval 과 seq_bbs.currval의 차이점은 전자는 마지막 시퀀스값에 증가값을 더한값이며 후자는 마지막 시퀀스값입니다.
※ currval은 참조되기 전에 nextval이 먼저 이용되어야 합니다.
※ SEQUENCE는 정수,문자열형 컬럼에 모두 대입됩니다.

현재 DB에 생선된 SEQUENCE 보기

select * from user_sequences;

select sequence_name, min_value, max_value,increment_by, last_number from user_sequences;

select * from seq;

Sequence 수정하기

alter sequence dept_deptno
increment by 1
maxvalue 999999
nocache
nocycle;

SEQUENCE 삭제하기

drop SEQUENCE sequence_name;

drop SEQUENCE seq_bbs;

select절이나 update에 사용하게 되면
초기 값이 1인 스퀀스(1씩증가)를 4개의 출력 select 쿼리에 다음과 같이 사용하게되면
select bbs.nextval from bbs;
1
2
3
4
가 출력되면 insert에 해당 bbs.nextval 를 사용하게되면 5가 적용됩니다.

초기 값이 1인 스퀀스(1씩증가)를 4개의 처리 update 쿼리에 다음과 같이 사용하게되면
insert into bbs values (bbs.nextval);
1
2
3
4
가 출력되면 insert에 해당 bbs.nextval 를 사용하게되면 5가 적용됩니다.

※ Sequence는 SELECT 문에는 사용하지 않습니다.

Sequence 를 매일 drop하고 create를 자동화처리

sequence를 매일 drop하고 create해서 매일 1로 sequence값을 딸수 있도록 하고 있습니다.
그 작업을 crontab에 등록하여 매일 밤 12시에 수행하는데요. 이 작업이 성공했는지 실패했는지..로그를 남길수 있었수 있게 합니다.

cd /script/sequence
sqlplus -s /nolog<<!
connect id/password
drop sequence sq_name;
CREATE SEQUENCE SQ_NAME
      START WITH       1
      INCREMENT BY         1
       MAXVALUE        99999
          CACHE           10
          CYCLE
         ORDER;

이걸 clear_seq.sh로 만들어두어 이걸 매일 crontab에서 수행합니다.
log를 만들게 하려면 spool을 만들게 하고 sqlplus 접속을 nolog 옵션빼고 하면 됩니다.

시퀀스를 드롭하고 생성하는 스크립트를 만들어서 그 스크립트내에 spool 을 넣고 file_name.sql 파일을 만들면 로그를 남길수 있습니다.

spool log.txt
drop sequence sq_name;
CREATE SEQUENCE SQ_NAME
      START WITH       1
      INCREMENT BY         1
       MAXVALUE        99999
          CACHE           10
          CYCLE
         ORDER
/
spool off

이후 clear_seq.sh 내용
cd /script/sequence
sqlplus -s id/passwd @file_name.sql

이러면 해당 sql파일 실행이 가능합니다.

그리고 참고로 /var/spool/mail 밑에 유저명으로 crontab 에 대한 로그가 남습니다. 그것도
확인 가능합니다.

데이타를 삽입할때 데이타의 문자열의 끝에 예를들어 'emp001', 'emp002'
이런식으로 자동증가하게 sequence를 이용하고 싶을때

예제 : SELECT 'emp' || lpad(시퀀스명.nextval,3,0) FROM DUAL;

[출처] 제트스윙 블로그 - http://www.zetswing.com