定期初始化 sequence

脚本如下
1.创建 一个procedure
    create or replace procedure seq_set_zero
   
     is
   
      v_dsep     varchar2(100);
      v_seqexist number;
      v_crsep   varchar2(250);
      v_rcsep   varchar2(250);
    begin
     
      select count(*)
        into v_seqexist
        from all_sequences
       where sequence_name = 'SEQ_GOODS_RETNUM';
      if v_seqexist > 0 then
        v_dsep := 'drop sequence ksc.seq_goods_retnum';
       execute immediate v_dsep;
        v_rcsep:='create sequence seq_goods_retnum start with 1 increment by 1 maxvalue 9999 nocycle cache 5';
        execute immediate v_rcsep;
       ELSE
         v_crsep:='create sequence seq_goods_retnum start with 1 increment by 1 maxvalue 9999 nocycle cache 5';
         execute immediate v_crsep;
      end if;
    end;

 

2.做一个定时任务 jobs
declare
      job1 number;
begin
       dbms_job.submit(job1,'seq_set_zero;',to_date('04-04-2013 00:00:00','dd-mm-yyyy hh24:mi:ss')
,'trunc(sysdate+1)');
      
end;