DATOR


SEQUENCE 초기화 SQL


마이그레이션 프로젝트를 진행하면서 UNIQUE 값을 만들때 SEQUENCE를 많이 사용하게 되는데
테스트때 마다 SEQUENCE를 초기값으로 돌려 놓아야 할 경우 여간 귀찮은 일이 아닐 수 없다.
이럴때 유용한 방법을 소개한다.

 

SEQUENCE를 초기화 하고싶을때  DROP 후 재생성 하는 방법을 사용하는데
권한을 다시 지정해 줘야 하기 때문에 번거롭고, 일괄적으로 다시 만들기도 까다롭다.


다음과 같은 PL/SQL문을 사용하면 MIN_VALUE가 각각 달라도
MIN_VALUE 값으로 초기화 할 수 있다.


DECLARE
       V_SEQ_VAL NUMBER;
       V_MINUS_VAL NUMBER;
BEGIN
        FOR SEQ_LIST IN (SELECT SEQUENCE_NAME, MIN_VALUE
                                                    FROM  ALL_SEQUENCES
                                                  WHERE  SEQUENCE_OWNER = 'SCOTT' )LOOP
                                   
                                    EXECUTE IMMEDIATE 'SELECT '||SEQ_LIST.SEQUENCE_NAME||'.NEXTVAL FROM DUAL'

                                    INTO V_SEQ_VAL;                               
                                    V_MINUS_VAL := V_SEQ_VAL - SEQ_LIST.MIN_VALUE;
                                    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||SEQ_LIST.SEQUENCE_NAME||'

                                    INCREMENT BY -'||V_MINUS_VAL||' MINVALUE 0';
                                    EXECUTE IMMEDIATE 'SELECT '||SEQ_LIST.SEQUENCE_NAME||'.NEXTVAL FROM DUAL'

                                    INTO V_SEQ_VAL;
                                    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||SEQ_LIST.SEQUENCE_NAME||' INCREMENT BY 1

                                    MINVALUE '||SEQ_LIST.MIN_VALUE;
        END LOOP;
END;

 

설명

 

ALTER로 현재 VALUE에서 MIN_VALUE만큼 뺀 값을 -로 INCREMENT를 지정한 다음
NEXTVAL을 수행하여  MIN_VALUE값과 같게 해주고  ALTER SEQUENCE로 다시 원래의
MIN_VALUE 값을 지정해 주는것이다.

 

한가지 주의 할점은 SEQUENCE를 DROP후 재 생성할때는  MIN_VALUE를 1로 주면
처음값이 1로 시작하지만 PL/SQL을 통해 초기화 할때는 2(MIN_VALUE+1)부터 시작한다.
따라서 시작값을 1부터 하고 싶다면 MIN_VALUE를 0으로 해줘야 한다.

 

SEQUENCE 초기화시 MIN_VALUE + 1 값이 나오는 이유는
위의 PL/SQL 과정중에 CURRVAL 값이 MIN_VALUE 값을 갖게 되고
그 값에 NEXTVAL을 취하기 때문에 MIN_VALUE+1 값이 처음값으로 나오는 것이다.

 

그러면 애초에
V_MINUS_VAL := V_SEQ_VAL - SEQ_LIST.MIN_VALUE;

이 문장을 V_MINUS_VAL := V_SEQ_VAL - SEQ_LIST.MIN_VALUE - 1;
로 해서 0부터 시작하면 될것 같지만
SEQENCE는 MIN_VALUE 보다 작은 값으로 INCREMENT를 지정하여 NEXTVAL을 취할수 없다.

 

반면, DROP후 재생성은 CURRVAL 값이 없는 상태이기 때문에
NEXTVAL을 취하면 MIN_VALUE가 처음값으로 나오게 된다.

Tag :

Leave Comments