利用存储过程重置序列

工作中用到订单号需要每天从1累加。通过序列提供订单号，每天通过存储过程来重置改序列从1开始

创建序列：

create sequence Order_SEQID
minvalue 0
maxvalue 9999
start with 1
increment by 1
cache 20;

重置的存储过程：

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;
tsql:='alter sequence '||v_seqname||' increment by '|| n;
execute immediate tsql;
execute immediate 'select '||v_seqname||'.nextval from dual' into n;
tsql:='alter sequence '||v_seqname||' increment by 1';
execute immediate tsql;
end seq_reset;

调用方式：call seq_reset('Order_SEQID');