오라클 시퀀스 초기화 방법

SQL> create sequence ots_seq;

Sequence created.

SQL> select  ots_seq.nextval from dual ;

   NEXTVAL

----------

         1

SQL> /

   NEXTVAL

----------

         2

SQL> /

   NEXTVAL

----------

         3

SQL> /

   NEXTVAL

----------

         4

SQL> /

   NEXTVAL

----------

         5

--PL/SQL 생성

 CREATE or REPLACE PROCEDURE reset_seq (seq_name IN VARCHAR2) as

  curr_val INTEGER;

 BEGIN
     EXECUTE IMMEDIATE 'alter sequence ' ||seq_name||' MINVALUE 0';
     EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.nextval FROM dual' INTO curr_val;
     EXECUTE IMMEDIATE 'alter sequence ' ||seq_name||' increment by -'||curr_val;
     EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.nextval FROM dual' INTO curr_val;
     EXECUTE IMMEDIATE 'alter sequence ' ||seq_name||' increment by 1';
 END reset_seq;
/

 

Procedure created.

SQL> exec reset_seq('ots_seq');

PL/SQL procedure successfully completed.

SQL>  select  ots_seq.nextval from dual ;

   NEXTVAL

----------

         1

SQL>