oracle产生每日的流水号,利用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;