Oracle定时执行存储任务

用于安排和管理作业队列,通过使用作业,可以使ORACLE数据库定期执行特定的任务。

一、dbms_job涉及到的知识点
1. 创建job：
　　variable jobNo number;
　　dbms_job.submit(:jobNo,     ——job号  
                'your_procedure;',     ——执行的存储过程, ';'不能省略 
                next_date,                ——启动时间  
                'interval'                   ——执行频率
); 

• submit()的参数说明:

1. jobNo：系统会自动分配一个任务号jobNo，这个job号在定时器执行后，可在表user_jobs中找到。它前面有个冒号，表示动态获取job号，可不加冒号。
2. your_procedure：存储过程的名称。注意后面的分号';'不能省略。
3. next_date：启动的时间。若改为sysdate，则表示立即执行。
4. interval：执行频率，即隔多久执行一次存储过程。

• 创建job实例

• declare
       jobNo number;
  begin
      sys.dbms_job.submit(job => jobNo,
      what => 'prc_name;',
      next_date => to_date('2015-12-01 09:09:41', 'yyyy-mm-dd hh24:mi:ss'),   --定时器启动时间
      interval => 'sysdate+1/(24*60)');  --每分钟执行一次
      commit;
  end;

  程序中的job、what、next_date和interval关键字可以不要。

• submit()的参数设置

　　(1) 删除定时任务: dbms_job.remove(jobNo);  —— jobNo是定时任务的唯一标识。

• begin
  dbms_job.remove(316);  --假设jobNo = '316'
  commit;
  end;

　　(2) 修改要执行的任务：dbms_job.what(jobNo, what);

　　(3) 修改下次执行时间：dbms_job.next_date(jobNo, next_date); 

　　(4) 修改间隔时间：dbms_job.interval(jobNo, interval);

　　(5) 启动job：dbms_job.run(jobno); 

　　(6) 停止job： dbms.broken(jobno, broken, nextdate); –broken为boolean值 .

     注：(2)-(6)的sql语句的执行方法同(1)。

2. 定时器实例

    一. PLSQL中创建表

• create table tab_time(
       　Timedate date    
  );        

 　二. 创建存储过程

• create or replace procedure Tabtime_test as
  begin
    insert into tab_time values(sysdate);
  end;

 　三. 创建并启动job任务

• declare
       jobNo number;
  begin
  　　 dbms_job.submit(jobNo,'Tabtime_test;',sysdate,'sysdate+2/(24*60)');  --每两分钟执行一次
  　　 dbms_job.run(jobNo);  --启动任务
      commit;
   end;

    四. 查询定时任务

• select job,next_date,broken,what,interval,t.* from user_jobs t;   

　 五. 停止已启动的定时任务

• begin
     dbms_job.broken(315,true,sysdate);    --停止jobNo = '316'的任务,在user_jobs表中查看jobNo.
     commit; 
  end;

    六. 查看定时任务是否已停止成功

          执行查询定时任务，若broken值为Y，表示定时任务已停止，为N表示任务正在进行。

    七. 启动定时任务

• begin
     dbms_job.run(jobNo);   
     commit;
  end;

    八. 删除定时任务

• begin
     dbms_job.remove('316');     
     commit;
  end;

3. 定时时间参考设置

   描述                              INTERVAL参数值 
   每天午夜12点                 TRUNC(SYSDATE + 1) 
   每天早上8点30分             TRUNC(SYSDATE + 1) + （8*60+30）/(24*60) 
   每星期二中午12点            NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24 
   每个月第一天的午夜12点        TRUNC(LAST_DAY(SYSDATE ) + 1) 
   每个季度最后一天的晚上11点     TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24 
   每星期六和日早上6点10分        TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10) /(24×60)                                                       

  1)  每秒钟执行次   Interval => sysdate + 1/(24 * 60 * 60)

   如果改成sysdate + 10/(24 * 60 * 60)就是10秒钟执行次

　　sysdate+1                     表示每天执行一次
　　sysdate+2/24                表示两小时执行一次
　　sysdate+1/(24*60)        表示每分钟执行一次
　　sysdate+1/(24*60*60)   表示每秒执行一次 

  2) 每分钟执行 Interval => TRUNC(sysdate,'mi') + 1/ (24*60)

   如果改成TRUNC(sysdate,'mi') + 10/ (24*60) 就是每10分钟执行次

  3) 每天定时执行 
      例如：每天的凌晨2点执行 
              Interval => TRUNC(sysdate) + 1 +2/ (24)

  4) 每周定时执行 
      例如：每周一凌晨2点执行 
              Interval => TRUNC(next_day(sysdate,'星期一'))+2/24

  5) 每月定时执行 
      例如：每月1日凌晨2点执行 
              Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

   6) 每季度定时执行 
　　例如：每季度的第一天凌晨2点执行 
　　　　  Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

   7) 每半年定时执行 
　　例如：每年7月1日和1月1日凌晨1点 
　　　　  Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

   8) 每年定时执行 
　　例如：每年1月1日凌晨1点执行 
　　　　  Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24