ORACLE通过JOB每天重置序列

场景：某个业务表单里有个合同编号，需求要求是合同类别+年月日+流水号，流水号需要每天重置，于是就用上了oracle里的序列通过job每天进行重置。

1.创建序列

-- Create sequence 
create sequence FOTC_RELOCATE_PROJECTCODE_SEQ 
minvalue 0 
maxvalue 999999999 
start with 1 
increment by 1 
cache 10;

2.创建重置序列存储过程

create or replace procedure proc_seq_reset(v_seqname varchar2) as n number(20);
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 proc_seq_reset;

3.创建JOB（每天凌晨0点定时执行）

declare job number; 
begin 
 dbms_job.submit(job,'proc_seq_reset(''FOTC_RELOCATE_PROJECTCODE_SEQ'');',sysdate,'TRUNC(sysdate+1)');
commit; 
end;