오라클 시퀀스 초기화하기: DROP 없이 간단한 방법

오라클에서 시퀀스의 시작값을 변경하는 것은 생각보다 까다롭습니다. 특히 ALTER 구문으로는 시퀀스의 시작값을 조정할 수 없기 때문에, 보통은 DROP 한 후 CREATE하여 새로 생성하는 방법을 사용하게 됩니다. 하지만 이 방법은 DROP 및 CREATE 권한이 필요한 상황이므로 권한이 제한된 경우 문제가 발생할 수 있습니다. 이런 경우를 위해 시퀀스를 삭제하지 않고도 시작값을 초기화하는 방법을 소개합니다.

 

1. 기본 초기화 방법

아래 PL/SQL 프로시저는 지정된 시퀀스를 0부터 시작하도록 초기화합니다. 만약 다른 값으로 초기화하고 싶다면 추가적인 반복문을 사용할 수 있습니다.

CREATE OR REPLACE PROCEDURE P_RESET_SEQ( SEQ_NAME IN VARCHAR2 ) IS
    L_VAL NUMBER;
BEGIN
    -- 현재 시퀀스 값을 가져와서 L_VAL에 저장
    EXECUTE IMMEDIATE 'select ' || SEQ_NAME || '.nextval from dual' INTO L_VAL;
    
    -- 시퀀스를 현재 값만큼 감소시켜서 0으로 초기화
    EXECUTE IMMEDIATE 'alter sequence ' || SEQ_NAME || ' increment by -' || L_VAL || ' minvalue 0';
    EXECUTE IMMEDIATE 'select ' || SEQ_NAME || '.nextval from dual' INTO L_VAL;
    
    -- increment 값을 1로 다시 설정
    EXECUTE IMMEDIATE 'alter sequence ' || SEQ_NAME || ' increment by 1 minvalue 0';
END;

위 프로시저를 통해 시퀀스는 0으로 초기화됩니다. 이 방법으로 특정 시작값을 설정하려면 아래와 같은 추가 코드가 필요합니다.

 

2. 특정 값으로 초기화하기

시작값을 0이 아닌 300으로 설정하고 싶은 경우에는 추가 반복문을 사용합니다.

-- 특정 시작값 예시: 300
FOR i IN 1..300 LOOP
    EXECUTE IMMEDIATE 'select ' || SEQ_NAME || '.nextval from dual' INTO L_VAL;
END LOOP;

이 반복문을 통해 시퀀스의 값을 원하는 수치까지 증가시킬 수 있습니다.

 

3. 예제: 시퀀스 초기화 테스트

실제로 test_seq라는 시퀀스를 생성하고 reset_sequence 프로시저를 실행하여 값을 초기화해보겠습니다.

-- 시퀀스 생성
CREATE SEQUENCE test_seq;

-- 현재 시퀀스 값 확인
SELECT test_seq.nextval FROM dual;

-- 시퀀스 초기화 프로시저
CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) AS
    curr_val INTEGER;
BEGIN
    EXECUTE IMMEDIATE 'alter sequence ' || sequencename || ' MINVALUE 0';
    EXECUTE IMMEDIATE 'SELECT ' || sequencename || '.nextval FROM dual' INTO curr_val;
    EXECUTE IMMEDIATE 'alter sequence ' || sequencename || ' increment by -' || curr_val;
    EXECUTE IMMEDIATE 'SELECT ' || sequencename || '.nextval FROM dual' INTO curr_val;
    EXECUTE IMMEDIATE 'alter sequence ' || sequencename || ' increment by 1';
END reset_sequence;

-- 프로시저 실행하여 시퀀스 초기화
EXEC reset_sequence('test_seq');

-- 초기화된 시퀀스 값 확인
SELECT test_seq.nextval FROM dual;

이렇게 하면 test_seq 시퀀스는 1부터 다시 시작하게 됩니다.