[Oracle] 시퀀스 초기화 프로시저

CREATE OR REPLACE PROCEDURE P_RESET_SEQ( SEQ_NAME IN VARCHAR2 )
IS
L_VAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SEQ_NAME || ' INCREMENT BY -' || L_VAL || ' MINVALUE 0';

EXECUTE IMMEDIATE 'SELECT ' || SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SEQ_NAME || ' INCREMENT BY 1 MINVALUE 0';
END;
/