Oracle 定时任务---详述

简述

       很多时候我们都要对数据进行定时操作，比如 某个时间点去备份某张表、又或者某个时间点去删除某个时间段的数据。总结以上就是 某个时间点去做某件事。在Oracle中，JOB就是在阐述这件事情。我们就可以将要做的事情写到存储过程中、在JOB中指定时间点，以及要做的事（存储过程）指定明白就好了，下面举例

存储过程示例------要做什么事

create or replace procedure add_test as    /*add_test   存储过程名称*/
begin         /*做的事情开始*/
  insert into table_name values (to_char(sysdate, 'yyyy-mm-dd hh:mi'));/*向table_name表插入数据*/
  commit;     /*提交事务    当你执行其他的语句比如delete多条数据的时候 最好每个delete后面加上commit;防止产生大量缓存，严重消耗资源*/
end;          /*结束*/

JOB示例-------什么时候执行、做什么事

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,           /*自动生成JOB_ID*/  
        WHAT => 'add_test;',  /*需要执行的存储过程名称或SQL语句*/  
        NEXT_DATE => to_date('2019-11-16 00:00:00','yyyy-mm-dd hh24:mi:ss'),  /*初次执行时间----2019年11月16号 零点零分零秒执行*/  
        INTERVAL => 'trunc(LAST_DAY(SYSDATE)+14' /*用于下一次运行的表达式----每月十四号零点执行*/
       /*
        next_date、interval  指定什么时候执行
       	what指定执行什么事情
      */
      );  
  commit;
end;

以上则是一个较完整存储过程和JOB的示例，下面根据操作详细讲解一下。

操作JOB

1.查看JOB的详细信息

select * from user_jobs；

JOB详细信息主要字段

列名	数据类型	说明
JOB	NUMBER	任务的唯一标示号
LOG_USER	VARCHAR2(30)	提交任务的用户
PRIV_USER	VARCHAR2(30)	赋予任务权限的用户
SCHEMA_USER	VARCHAR2(30)	对任务作语法分析的用户模式
LAST_DATE	DATE	最后一次成功运行任务的时间
LAST_SEC	VARCHAR2(8)	如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE	DATE	正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC	VARCHAR2(8)	如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE	DATE	下一次定时运行任务的时间
NEXT_SEC	VARCHAR2(8)	如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME	NUMBER	该任务运行所需要的总时间，单位为秒
BROKEN	VARCHAR2(1)	标志参数，Y标示任务中断，以后不会运行
INTERVAL	VARCHAR2(200)	用于计算下一运行时间的表达式
FAILURES	NUMBER	任务运行连续没有成功的次数
WHAT	VARCHAR2(2000)	执行任务的PL/SQL块

2.通过JOB（任务的唯一标示号、通过1进行查询得到，以下统一使用JOB代替）操作JOB

/*手动执行JOB*/
begin
   DBMS_JOB.RUN(JOB);  /*JOB 唯一标示号*/
 end;

/*删除JOB*/
begin 
  dbms_job.remove(JOB);  /*JOB 唯一标示号*/
end;

/*停止JOB*/ 
//exec dbms_job.broken(job，broken，nextdate);   
exec dbms_job.broken(JOB,true,sysdate);  /*JOB(唯一标识),trur|false,某一时刻停止sysdate（立刻停止）。*/

/*修改间隔时间*/
dbms_job.interval(JOB，interval);

/*修改下次执行时间*/
dbms_job.next_date(JOB，next_date);

/*修改要执行的操作  修改执行块内容*/
dbms_job.what（JOB，'new_add;'）; 

3.查看运行的JOB

select * from dba_jobs_running  /*where job = '166' 后面可跟where进行查看某个JOB是否运行*/;

---

INTERVAL参数常用值示例

表达式	说明
TRUNC(SYSDATE + 1)	每天午夜12点
TRUNC(SYSDATE + 1) + （8 * 60+30）/(24 * 60)	每天早上8点30分
NEXT_DAY(TRUNC(SYSDATE ), “TUESDAY”) + 12/24’	每星期二中午12点
TRUNC(LAST_DAY(SYSDATE ) + 1)	每个月第一天的午夜12点
TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘‘Q’’ ) -1/24	每个季度最后一天的晚上11点
TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, “SUNDAY”))) + （6 * 60+10）/（24 * 60）	每星期六和日早上6点10分
sysdate+3/(24 * 60 * 60)	每3秒钟执行一次
sysdate+2/(24 * 60)	每2分钟执行一次
每分钟执行
TRUNC(sysdate,‘mi’) + 1/ (24*60) 或者sysdate+1/（24 * 60）	每分钟执行
sysdate+1	每天
sysdate+1/24	每小时
sysdate+2/24 * 60	每2分钟
sysdate+30/24 * 60 * 60	每30秒
TRUNC(sysdate+1)	每天凌晨0点执行
TRUNC(sysdate+1)+1/24	每天凌晨1点执行
TRUNC(SYSDATE+1)+(8 * 60+30)/(24 * 60)	每天早上8点30分执行
TRUNC(next_day(sysdate,‘星期一’))+1/24	每周一凌晨1点执行
TRUNC(next_day(sysdate,1))+2/24	每周一凌晨2点执行
TRUNC(LAST_DAY(SYSDATE)+1)	每月1日凌晨0点执行
TRUNC(LAST_DAY(SYSDATE))+1+1/24	每月1日凌晨1点执行
TRUNC(ADD_MONTHS(SYSDATE,3),‘q’)	每季度的第一天凌晨0点执行
TRUNC(ADD_MONTHS(SYSDATE,3),‘q’) + 1/24	每季度的第一天凌晨1点执行
TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),‘q’)-1/24	每季度的最后一天的晚上11点执行
ADD_MONTHS(trunc(sysdate,‘yyyy’),6)+1/24	每年7月1日和1月1日凌晨1点
ADD_MONTHS(trunc(sysdate,‘yyyy’),12)+1/24	每年1月1日凌晨1点执行

注 意

1.验证 interval 时间点是否正确 可以在orcale里测试一下，测试语句如下 ：

select TRUNC(SYSDATE+1)+(8*60+30)/(24*60) from dual;   /* 会显示八点半*/

2.truncate 不能在存储过程中直接使用,需使用execute immediate 如下：

execute immediate  'truncate table tablename';

3.当使用delete删除大量的数据的时候 最好在每个delete语句后加入commit;提交事务。如下：

delete from test where number < 100000;
commit;
delete from test where number < 100000;
commit;