工作中用到订单号需要每天从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');