--创建序列

–入库
create sequence rk_seq;
–出库
create sequence ck_seq;
–移库
create sequence yk_seq;

–创建存储过程

create or replace procedure reset_seq(rk_seq_name in varchar2,
ck_seq_name in varchar2,
yk_seq_name in varchar2) is
l_val number;
begin
–rk
execute immediate ‘select ‘ || rk_seq_name || ‘.nextval from dual’
INTO l_val;

execute immediate ‘alter sequence ‘ || rk_seq_name || ‘ increment by -‘ ||
l_val || ‘ minvalue 0’;

execute immediate ‘select ‘ || rk_seq_name || ‘.nextval from dual’
INTO l_val;

execute immediate ‘alter sequence ‘ || rk_seq_name ||
‘ increment by 1 minvalue 0’;
–ck
execute immediate ‘select ‘ || ck_seq_name || ‘.nextval from dual’
INTO l_val;

execute immediate ‘alter sequence ‘ || ck_seq_name || ‘ increment by -‘ ||
l_val || ‘ minvalue 0’;

execute immediate ‘select ‘ || ck_seq_name || ‘.nextval from dual’
INTO l_val;

execute immediate ‘alter sequence ‘ || ck_seq_name ||
‘ increment by 1 minvalue 0’;

–yk
execute immediate ‘select ‘ || yk_seq_name || ‘.nextval from dual’
INTO l_val;

execute immediate ‘alter sequence ‘ || yk_seq_name || ‘ increment by -‘ ||
l_val || ‘ minvalue 0’;

execute immediate ‘select ‘ || yk_seq_name || ‘.nextval from dual’
INTO l_val;

execute immediate ‘alter sequence ‘ || yk_seq_name ||
‘ increment by 1 minvalue 0’;
end;

–创建定时任务

declare
job number;
begin
dbms_job.submit(job,
‘ begin reset_seq(”rk_seq”, ”ck_seq”, ”yk_seq”);end;’,
sysdate,
‘TRUNC(LAST_DAY(SYSDATE))+1+1/24’);
end;