[ORACLE] #01. 오라클 시퀀스(Sequence) 사용 및 .NEXTVAL 초기화

1. 시퀀스 생성, 수정, 삭제

시퀀스 생성

테이블을 생성했을 때와 마찬가지로 CREATE 명령어를 사용합니다.

CREATE SEQUENCE로 시퀀스를 생성할 수 있으며, 여러가지 옵션을 지정할 수 있습니다.

 

CREATE SEQUENCE 시퀀스 이름

    [START WITH 시작값]

    [INCREMENT BY 증가값]

    [MAXVALUE n | 최대값]

    [MINVALUE n | 최소값]

    [CYCLE | 순환 여부]

    [CACHE | 캐시 여부]

 

시작값 :  말 그대로 시퀀스의 시작값입니다. 만약 1을 쓴다면 1부터 순차적으로 증가할 것이고 10을 쓴다면 10부터 순차적으로 증가합니다.

증가값 : 시퀀스가 얼마씩 증가할 건지를 지정하는 값입니다. 1을 쓰면 1씩 증가하며, 2를 쓰면 2씩 증가하게 됩니다.

최대값 : 시퀀스의 최대값을 의미하며, 최소값은 시퀀스의 최소값을 의미합니다.

순환 여부 : 시퀀스가 최대 값이 됐을때 다시 시작값으로 돌아갈 것인지에 대해 설정합니다.  순환한다면 CYCLE, 그렇지 않다면 NOCYCLE을 지정하시면 됩니다.

캐시 여부 : 시퀀스 값을 메모리에 할당할 것인지에 대해 설정합니다. CACHE에 할당한다면 원하는 값을 넣으시면 되고 기본 값은 20입니다. 할당하지 않겠다면 NOCACHE로 지정하시면 됩니다.

 

시퀀시 수정

시퀀스를 수정하는 방법도 역시 생성과 마찬가지로 테이블을 수정했던 방법과 비슷하게 ALTER 명령어를 사용합니다. 

ALTER SEQUENCE로 변경할 수 있습니다. 단, START WITH는 변경할 수 없습니다.

나머지 다른 옵션들은 변경이 가능합니다.

 

ALTER SEQUENCE 시퀀스 명

    [INCREMENT BY 증가값]

    [MAXVALUE n | 최대값]

    [MINVALUE n | 최소값]

    [CYCLE | 순환 여부]

    [CACHE | 캐시 여부]

 

시퀀스 삭제

 

DROP SEQUENCE 시퀀스 명;

 

테이블과 똑같이 DROP 명령어로 시퀀스를 삭제할 수 있습니다.

단, DROP 할 수 있는 권한이 있어야 삭제할 수 있습니다.

 

 

2. NEXTVAL 사용

 

이제 생성된 시퀀스를 증가시키는 방법을 알아보겠습니다. 먼저 시퀀스를 생성하고 사용할 수 있습니다.

 

CUSTOMER_SEQ 라는 이름의 시퀀스를 증가시키고 싶다면 이렇게 사용하시면 됩니다.

 

CUSTOMER_SEQ.NEXTVAL 

 

현재 시퀀스를 조회하고 싶다면 ? 

 

CUSTOMER_SEQ.CURRVAL 을 사용하시면 됩니다.

 

 

 

다만 주의해야할 규칙이 있습니다.

 

@ NEXTVAL, CURRVAL 사용 가능

 

-   SELECT 문 

 -  INSERT문의 SELECT절, VALUE절

 -  UPDATE 문 SET 절

@ NEXTVAL, CURRVAL 사용 불가능

 

 -  VIEW의 SELECT절

 -  SELECT문의 DISTINCT 키워드

 -  SELECT문의 GROUP BY, HAVING, ORDER BY 절

 -  SELECT, DELETE, UPDATE문의 서브 쿼리

 -  CREATE TABLE, ALTER TABLE의 DEFAULT 

 

 

3. NEXTVAL 초기화

 

사실 권한만 있다면 시퀀스를 DROP하고 다시 CREATE하면 됩니다. 그러나 권한이 없는 경우가 대부분입니다.

그럴때 NEXTVAL을 초기화하기 위해 사용하는 방법입니다.

 

1. 시퀀스의 현재 값을 확인합니다.

SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '시퀀스 명';

 

2. 시퀀스의 INCREMENT 를 현재 값만큼 빼도록 설정합니다. (현재값이 155인 경우)

ALTER SEQUENCE 시퀀스명 INCREMENT BY -155;

 

3. 시퀀스의 NEXTVAL값을 조회합니다. 

SELECT 시퀀스명.NEXTVAL FROM DUAL;

 

4. 시퀀스의 INCREMENT 를 1로 설정 합니다.

ALTER SEQUENCE 시퀀스명 INCREMENT BY 1;

 

4. 자바에서 NEXTVAL

그렇다면 자바에선 어떻게 사용할까요 ?

자바에서 디비로 쿼리를 날리는 것은 동일합니다.

 

어떻게 하는지 모르신다면 아래 글을 참조해주세요.

 

2017/11/08 - [IT/Language] - JAVA - 오라클 DB에 쿼리 날리기( INSERT, DELETE, UPDATE)

 

 

날리는 방법은 동일하니 sql문을 어떻게 작성하는지만 알면 되겠네요.

 

CUSTOMER_SEQ 이라는 시퀀스 명으로 생성되어있다고 가정하겠습니다.

 

sql문을 생성할 때 시퀀스에 해당하는 위치에 넣어주면 되겠습니다.

 

시퀀스 자리에 시퀀스명.NEXTVAL을 써주면 됩니다.

 

statement를 사용하는 경우엔 이렇게 합니다.

 

sql = "INSERT CUSTOMER (NAME, AGE, ADDRESS, RECEIPT) VALUES('" + name +"'," + age + ",'" + address +"' + CUSTOMER_SEQ.NEXTVAL)";

 

그렇다면 ?가 들어가는 PreparedStatement일때는 어떻게 할까요 ?

 

sql = "INSERT CUSTOMER (NAME, AGE, ADDRESS, RECEIPT) VALUES(?, ?, ?, CUSTOMER_SEQ.NEXTVAL)";

 

이렇게 sql문을 생성할 때 시퀀스 자리에 맞춰서 시퀀스명.NEXTVAL을 넣으면 되겠습니다.

 

저렇게 sql문을 만들어서 디비에 날리면 정상적으로 시퀀스가 증가하는 결과를 확인할 수 있습니다.