231023 Oracle SQL SEQUENCE

■ SEQUENCE
     A. 자동일련번호를 생성하는 객체이다. 
     B. SEQUENCE 객체를 생성하려면 CREATE SEQUENCE 시스템 권한이 있어야 한다. 
     C. sequence 이름.nextval : 사용가능한 번호를 리턴한 가상 컬럼
     D. sequence 이름.currval : 현재 사용한 번호를 리턴하는 가상컬럼
     E. 한번 사용하면 영구히 사용하게 되니, 주의해서 사용할 것. 
        (sequence 번호는 갭이 생길 수 있다.)
     F. 시퀀스.currval 를 INSERT 문에서도 가능하나 갭이 생길 수 있다.

    'CREATE SEQUENCE' 권한 X
    <SYS SESSION>;

    GRANT CREATE SEQUENCE TO hr;

# 시퀀스 확인

SELECT * FROM dba_sequences WHERE sequence_name = 'ID_SEQ';
SELECT * FROM seq$;

 

<hr SESSION>;
# 시스템 권한 확인(SYS 계정에서 권한 부여) 'CREATE SEQUENCE' 확인.

SELECT * FROM session_privs;

# SEQUENCE 생성

CREATE SEQUENCE id_seq;

# 'user_sequences' table 을 통해 시퀀스 확인

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

1. CACHE_SIZE : 메모리사이즈

2. LAST_NUMBER : 다음 메모리사이즈 

# test table 생성 

CREATE TABLE hr.seq_test (
    id    NUMBER,
    name  VARCHAR2(30),
    day   DATE
);

# data 로드
    - id_seq.nextval(sequence 이름.nextval) : 사용가능한 번호를 리턴한 가상 컬럼

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '홍길동', sysdate );

# data 확인

SELECT * FROM hr.seq_test;

# 시퀀스 확인 'LAST_NUMBER: 21' 변화

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

# data 로드

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '박찬호', sysdate );

# data 확인

SELECT * FROM hr.seq_test;

# 시퀀스 확인 'LAST_NUMBER: 21' 변동 X

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

# data 확인

SELECT * FROM hr.seq_test;

# sequence 이름.currval : 현재 사용한 번호를 리턴하는 가상컬럼

SELECT id_seq.currval 
FROM dual;

# sequence 번호는 갭이 생길 수 있다. 

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '홍길동', sysdate );
SELECT * FROM hr.seq_test;

# seq.currval 를 INSERT 문에서도 가능하나 갭이 생길 수 있다.

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.currval, '홍길동', sysdate );
SELECT * FROM hr.seq_test;

# nextval : select 문에서 생성하면 갭이 생길 수 있다.

SELECT id_seq.nextval FROM dual;

# data 로드 및 확인

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '손흥민', sysdate );
SELECT * FROM hr.seq_test;

# SEQUENCE 삭제

DROP SEQUENCE id_seq;

# SEQUENCE 생성 

CREATE SEQUENCE id_seq 
    START WITH 1 -- 시작 번호(기본값) 
    MAXVALUE 3 -- 기본값: 10의 27승, POWER(10, 27)
    INCREMENT BY 1 -- 증가분1 (기본값 )
    NOCYCLE -- 기본값, CYCLE
    NOCACHE -- CACHE 20 (기본값)
;

# 시퀀스 조회

SELECT * FROM hr.seq_test;

# DATA 로드

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '홍길동', sysdate );
INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '박찬호', sysdate );
INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '손흥민', sysdate );

# 오류발생 : sequence ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
-- MAXVALUE 3 까지 수행되어서 더이상 sequence 번호를 생성할 수 없다. 

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '이문세', sysdate );

# hr.seq_test data 확인 및 영구히 저장

SELECT * FROM hr.seq_test;
COMMIT;

# 시퀀스 수정하기 전에 'LAST_NUMBER' 값 확인

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

# SEQUENCE 수정
    - 단, START WITH 절은 수정불가.

ALTER SEQUENCE id_seq
MAXVALUE 10;

# 시퀀스 수정하기 후 'LAST_NUMBER' 값 확인

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

# 오류 DATA 로드 

INSERT INTO hr.seq_test(id, name, day) VALUES ( id_seq.nextval, '이문세', sysdate );

# DATA 확인

SELECT * FROM hr.seq_test;

# UPDATE data 변경

UPDATE hr.seq_test
SET id = id_seq.nextval
WHERE id = 4;