利用oracle定时任务生成每月从1开始的流水号

--创建序列

--入库
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;

用时直接在程序中查询相应序列即可获取不重复的号码，序列的重置工作由定时任务自动执行

利用oracle定时任务生成每月从1开始的流水号

标签：res   开始   job   nextval   RoCE   定时任务   har   immediate   next