디비랑[dɪ'bɪraŋ]

/********************************************************************************************
-- Title : [10g] 시퀀스 관리 - ver.dbrang
-- Reference : dbrang.com
-- Key word : sequence
********************************************************************************************/
/*
-- 테이블 생성
-- drop table scott.tbl12;
*/
-- 테이블 생성
CREATE TABLE scott.tbl12
( seq NUMBER NOT NULL PRIMARY KEY
, a VARCHAR2(5) NOT NULL
, c VARCHAR2(10) NULL
) TABLESPACE users;

-- 데이터 입력
INSERT INTO scott.tbl12 VALUES (1, 'aaa', 'aaaaa');
INSERT INTO scott.tbl12 VALUES (2, 'bbb', 'bbbbb');
COMMIT;

-- 데이터 확인
SELECT * FROM scott.tbl12;

/*
-- 시퀀스 생성
-- drop sequence scott.tbl12_seq;
*/
-- 시퀀스 생성
CREATE SEQUENCE scott.tbl12_seq
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 100
  NOCACHE
  NOCYCLE;

-- NEXTVAL, CURRVAL 확인
SELECT scott.tbl12_seq.nextval, scott.tbl12_seq.currval
FROM dual;

SELECT scott.tbl12_seq.nextval, scott.tbl12_seq.currval
FROM dual;

/*
-- 시퀀스 생성 확인 뷰
*/
SELECT sequence_owner, sequence_name, min_value, max_value, increment_by, last_number
     , cycle_flag, order_flag, cache_size
FROM DBA_SEQUENCES
WHERE sequence_owner = 'SCOTT';

/*
-- 시퀀스 사용
*/
INSERT INTO scott.tbl12(seq, a, c)
VALUES (scott.tbl12_seq.NEXTVAL, 'ccc', 'cccc');
INSERT INTO scott.tbl12(seq, a, c)
VALUES (scott.tbl12_seq.NEXTVAL, 'ddd', 'dddd');
COMMIT;

-- 입력 확인
SELECT * FROM scott.tbl12;

/*
-- 시퀀스 수정
*/
-- 수정
ALTER SEQUENCE scott.tbl12_seq
  INCREMENT BY 100
  MAXVALUE 9999
  NOCACHE
  NOCYCLE;

-- 수정 확인
SELECT sequence_owner, sequence_name, min_value, max_value, increment_by, last_number
     , cycle_flag, order_flag, cache_size
FROM DBA_SEQUENCES
WHERE sequence_owner = 'SCOTT';

-- NEXTVAL, CURRVAL 확인
SELECT scott.tbl12_seq.nextval, scott.tbl12_seq.currval
FROM dual;

-- 수정된 입력
INSERT INTO scott.tbl12(seq, a, c)
VALUES (scott.tbl12_seq.NEXTVAL, 'fff', 'fffff');
COMMIT;

-- 확인
SELECT * FROM scott.tbl12;

/*
-- NEXTVAL과 CURRVAL 차이
*/
-- 수정
ALTER SEQUENCE scott.tbl12_seq
  INCREMENT BY 1
  MAXVALUE 9999
  NOCACHE
  NOCYCLE;

-- 입력
INSERT INTO scott.tbl12 VALUES (scott.tbl12_seq.nextval, 'ggg','ggggg');
COMMIT;

SELECT * FROM scott.tbl12;   -- max(seq)=732 기억.

-- LAST_NUMBER 확인(733)
SELECT sequence_owner, sequence_name, min_value, max_value, increment_by, last_number
     , cycle_flag, order_flag, cache_size
FROM DBA_SEQUENCES WHERE sequence_owner = 'SCOTT';

-- 5회 확인(currval)
SELECT scott.tbl12_seq.currval FROM dual;

-- LAST_NUMBER 확인(733)
SELECT sequence_owner, sequence_name, min_value, max_value, increment_by, last_number
     , cycle_flag, order_flag, cache_size
FROM DBA_SEQUENCES WHERE sequence_owner = 'SCOTT';

-- 5회 실행(nextval)
SELECT scott.tbl12_seq.nextval FROM dual;

-- LAST_NUMBER 확인(738)
SELECT sequence_owner, sequence_name, min_value, max_value, increment_by, last_number
     , cycle_flag, order_flag, cache_size
FROM DBA_SEQUENCES WHERE sequence_owner = 'SCOTT';

/*
-- 시퀀스 제거
*/
DROP SEQUENCE scott.tbl12_seq;