Oracle利用序列定时增长每天生成新序列号
1.定义序列
-- Create sequence create sequence BCM_GLOABAL_ID minvalue 0 maxvalue 999999999999999999 start with 1 increment by 1 cache 20;
2.编写存储过程
create or replace procedure SEQ_RESET_NEXT AS n NUMBER; Begin --BCM_GLOABAL_ID Begin Execute Immediate 'alter sequence BCM_GLOABAL_ID minvalue 0'; Execute Immediate 'select BCM_GLOABAL_ID.nextval from dual' Into n; Execute Immediate 'alter sequence BCM_GLOABAL_ID increment by -' || n; Execute Immediate 'select BCM_GLOABAL_ID.nextval from dual' Into n; --Execute Immediate 'alter sequence BCM_GLOABAL_ID minvalue 1'; Execute Immediate 'alter sequence BCM_GLOABAL_ID increment by 1 '; End; End;
3.编写定时任务调用存储过程
begin sys.dbms_job.submit(job => :job, what => 'SEQ_RESET_NEXT;', next_date => to_date('20-10-2020 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'TRUNC(sysdate) + 1 +1/(24)'); commit; end; /