1、添加序列
CREATE SEQUENCE "CONVERGENCE"."SEQ_ERROR"
INCREMENT BY 1
MINVALUE 0
MAXVALUE 9999999999999999999999999999
START WITH 1
CACHE 10;
2、创建存储过程
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;
3、添加定时任务
declare job_n number;
begin
dbms_job.submit(job_n ,'seq_reset(SEQ_ERROR);',sysdate,'TRUNC(sysdate+1)');
commit;
end;
4、相关语句
查看所有jobs
select * from dba_jobs
查看下一个序列
SELECT SEQ_ERROR.nextval from dual;
调用存储过程
call seq_reset();