Oracle关于每天重置序列号从1开始

1，创建序列

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，设置时间定时

declare job number;

begin

dbms_job.submit(job,'proc_seq_reset(''PCHSEQ'');',sysdate,'TRUNC(sysdate+1)');

commit;

end;