Techdata VINA 지식 공유

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

 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>