博客

oracle创建自增序列并每日刷新重新从1开始

--创建增长序列

CREATE SEQUENCE ID_SEQ
    INCREMENT BY 1 
    START WITH 1
    MINVALUE 0  
    NOCYCLE 
    CACHE 20;

--测试
select id_seq.nextval from dual

--创建存储过程重置自增序列从1开始

create or replace procedure idseq_r as n number(10);
  tsql varchar2(100);
     begin
     execute immediate 'select id_seq.nextval from dual' into n; 
      n:=-(n);
      tsql:='alter sequence id_seq increment by '|| n;
      execute immediate tsql;
      execute immediate 'select id_seq.nextval from dual' into n;
      tsql:='alter sequence id_seq increment by 1';
      execute immediate tsql;
end idseq_r;

--测试
call idseq_r();

--创建定时任务调用存储过程

declare
  job number;/*自动生成job编号*/
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job, 
        WHAT => 'gansubk.grc_lawyer_flowss_applyidseq_r;',  /*需要执行的存储过程名称或SQL语句*/  
        NEXT_DATE => sysdate+1/(24*60),  /*初次执行时间-下一个1分钟*/  
        INTERVAL => 'trunc(sysdate+1)' /*每天0点执行一次*/
      );  
  commit;
end;

--测试
select * from user_jobs;

参考文章

https://blog.csdn.net/Xiadeqiu_0516/article/details/117249239