如何在不删除oracle中的序列的情况下更改序列顺序

问题描述：

我当前的序列号是203，我需要跳到1203而不删除该序列.

my current sequence no is 203 i need to jump to 1203 without dropping the sequence.

答

您可以在所需的DB模式上创建这样的过程，如下所示:

You may create such a procedure on your desired schema of DB as follows :

SQL>Create or Replace Procedure Pr_Set_Sequence( i_seq_name varchar2, i_val pls_integer ) is
   v_val     pls_integer;
begin
  for c in (
            Select u.sequence_name seq
              From User_Sequences u
             Where u.sequence_name = upper(i_seq_name)
           ) 
  loop  
    execute immediate 'select '||i_seq_name||'.nextval from dual' INTO v_val;
    execute immediate 'alter sequence '||i_seq_name||' increment by ' ||
                                               to_char(-v_val+i_val) || ' minvalue 0';
    execute immediate 'select '||i_seq_name||'.nextval from dual' INTO v_val;
    execute immediate 'alter sequence '||i_seq_name||' increment by 1 minvalue 0';
  end loop;  
end;

并调用所需的值(在我的情况下，创建一个名为my_seq的新值):

and call with the desired value ( in my case a create a new one called my_seq ) :

SQL> create sequence my_seq;

Sequence created

SQL> select my_seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> begin
  2     pr_set_sequence('my_seq',1203);
  3  end;
  4  /

PL/SQL procedure successfully completed

SQL> select my_seq.currval from dual;

   NEXTVAL
----------
      1203

如何在不删除oracle中的序列的情况下更改序列顺序

相关推荐