场景：某个业务表单里有个合同编号，需求要求是合同类别+年月日+流水号，流水号需要每天重置，于是就用上了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;