创建procedure,如下:
/*
创建人:郑林 2017-8-30
方法说明:重置序列发生器
参数说明:v_seqname序列发生器名称;v_standard序列的起始号码
*/
create or replace procedure seq_reset(v_seqname varchar2,v_standard number) as
n number(10);
tsql varchar2(100);
begin
--获取序列的当前值
execute immediate 'select '|| v_seqname ||'.nextval from dual' into n;
n :=v_standard-(n-1);
--更改步长
tsql := 'alter sequence '|| v_seqname ||' increment by ' || n;
execute immediate tsql;
--获取当前值
begin
execute immediate 'select '|| v_seqname ||'.nextval from dual' into n;
exception
when others then
if sqlcode='-8004' then
tsql:='alter sequence '||v_seqname||' increment by 1';
execute immediate tsql;
end if;
end;
--恢复步长
tsql := 'alter sequence ' || v_seqname ||' increment by 1';
execute immediate tsql;
end seq_reset;
参数中v_standard为起始值。
这样,重写定时任务,便可解决当前问题
exec seq_reset('seq1',1000);