用存储过程重置序列

SQL> create sequence seq_1 increment by 1 start with 1 maxvalue 999999999; 
序列已创建。 
SQL> create or replace procedure seq_reset(v_seqname varchar2) as 
 n number(10); 
 tsql varchar2(100); 
 begin 
 execute immediate 'select '||v_seqname||'.nextval from dual' into n; 
 n:=-(n-1); 
 tsql:='alter sequence '||v_seqname||' increment by '|| n;--让序列一次递增-N，实现归0 
 execute immediate tsql; 
 execute immediate 'select '||v_seqname||'.nextval from dual' into n; 
10 tsql:='alter sequence '||v_seqname||' increment by 1'; 
11 execute immediate tsql; 
12 end seq_reset; 
13 / 
过程已创建。 
SQL> select seq_1.nextval from dual; 
NEXTVAL 
--------- 
2 
SQL> / 
NEXTVAL 
--------- 
3 
SQL> / 
NEXTVAL 
--------- 
4 
SQL> / 
NEXTVAL 
--------- 
5 
SQL> exec seq_reset('seq_1'); 
PL/SQL 过程已成功完成。 
SQL> select seq_1.currval from dual; 
CURRVAL 
--------- 
1 
SQL> 
这样可以通过随时调用此过程，来达到序列重置的目的。