oracle 定时任务

首先看一个 job 脚本：

 脚本1：

variable jobno number;
begin
dbms_job.submit(:jobno,'STU_IN_JOB;', sysdate, 'sysdate+1/24/60');
commit;
end;
/

脚本2：

begin
  sys.dbms_job.submit(job => :job,
                      what => 'STU_IN_JOB;',
                      next_date => to_date('16-02-2020 09:49:10', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+1/24/60');
  commit;
end;
/

脚本1 和 脚本2  是同一个 job ，脚本1是命令创建的，脚本2 是在 PLSQL Developer 工具中查看的 

点击 查看 SQL

job => :job,                 (job 任务号)                                                     

what => 'STU_IN_JOB;',      （job 要执行的存储过程，可以有多个用; 分割）
next_date => to_date('16-02-2020 09:49:10', 'dd-mm-yyyy hh24:mi:ss'),   （下次执行时间）
interval => 'sysdate+1/24/60'  （每次间隔时间，interval以天为单位，可以自定义）

脚本1 执行完成后可以得到 job 号 （系统会自动分配一个任务号jobno）

SQL> variable jobno number;
SQL> begin
  2  dbms_job.submit(:jobno,'STU_IN_JOB;', sysdate, 'sysdate+1/24/60');
  3  commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed
jobno
---------
24

操作：

1 .查看任务队列执行情况：

SQL> select job, next_date, next_sec, failures, broken from user_jobs;
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
        24 2020-02-16  09:38:10                  0 N

BROKEN=N 说明  job =24 的 任务正在执行。

其他操作相关语法：

2、删除job: dbms_job.remove(jobno); 

3、修改要执行的操作: job:dbms_job.what(jobno, what);  

4、修改下次执行时间：dbms_job.next_date(jobno, next_date);

5、修改间隔时间：dbms_job.interval(jobno, interval); 

6、启动job: dbms_job.run(jobno);

7、停止job: dbms.broken(jobno, broken, nextdate); –broken为boolean值

停止一个已启动的定时任务：

begin
dbms_job.broken(24, true, sysdate);
commit;
end;
/

启动一个定时任务：

begin
dbms_job.run(24);
commit;
end;
/

user_jobs：任务表

结构：

1. 字段（列） 类型 描述
2. job number 任务的唯一标示号
3. log_user varchar2(30) 提交任务的用户
4. priv_user varchar2(30) 赋予任务权限的用户
5. schema_user varchar2(30) 对任务作语法分析的用户模式
6. last_date date 最后一次成功运行任务的时间
7. last_sec varchar2(8) 如hh24:mm:ss格式的last_date日期的小时，分钟和秒
8. this_date date 正在运行任务的开始时间，如果没有运行任务则为null
9. this_sec varchar2(8) 如hh24:mm:ss格式的this_date日期的小时，分钟和秒
10. next_date date 下一次定时运行任务的时间

一个完整示例：

1：创建一个测试表：

create table TESTJOB
(
  name VARCHAR2(32),
  time DATE
)

2：创建一个存储过程：

create or replace procedure STU_IN_JOB is
begin
	 insert into TESTJOB (name,TIME) values ('TEST',sysdate);
	end;

3：创建任务并执行：

SQL> variable jobno number;
SQL> begin
  2  dbms_job.submit(:jobno,'STU_IN_JOB;', sysdate, 'sysdate+1/24/60');
  3  commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed
jobno
---------
24

4：查看任务是否启动：BROKEN=N 表示任务启动

SQL> select job, next_date, next_sec, failures, broken from user_jobs;
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
        24 2020-02-16  10:55:12                  0 N

5：停止任务：

SQL> begin
  2  dbms_job.broken(24, true, sysdate);
  3  commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed
 
SQL> select job, next_date, next_sec, failures, broken from user_jobs;
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
        24 4000-01-01  00:00:00                  0 Y

带参数的任务：

SQL> DECLARE
  2  X NUMBER;--随机一个job编号
  3  BEGIN
  4  SYS.DBMS_JOB.SUBMIT
  5  ( job => X,
  6  what => 'JOB_TEST(TO_CHAR(SYSDATE,''yyyy-mm-dd hh24:mi:ss''));',--参数是将被执行的PL/SQL代码块。即调用的存储过程
  7  next_date => to_date('2020-02-22 14:21:00','yyyy-mm-dd hh24:mi:ss'),--下一个运行时间大于这个时间就会运行
  8  interval => 'sysdate+5/24/60',--时间间隔 5分钟中执行一次。
  9  no_parse => FALSE--参数指示此工作在提交时或执行时是否应进行语法分析——TRUE
 10  );
 11  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 12  COMMIT;
 13  END;
 14  /
 
Job Number is: 25
 
PL/SQL procedure successfully completed

SQL> select job, next_date, next_sec, failures, broken from user_jobs;
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
        24 4000-01-01  00:00:00                  0 Y
        25 2020-02-22  14:26:22                  0 N

执行结果：

修改任务：

1：先停止任务：

SQL> begin
  2  dbms_job.broken(25, true, sysdate);
  3  commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed

2：修改执行时间间隔 该为 10秒执行一次：

SQL> begin
  2  	dbms_job.interval(25,interval => 'sysdate+10/(24*60*60)');
  3    commit;
  4  	end;
  5  /
 
PL/SQL procedure successfully completed

3：启动任务：

SQL> begin
  2  dbms_job.run(25);
  3  commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed
 
SQL> select job, next_date, next_sec, failures, broken from user_jobs;
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
        24 4000-01-01  00:00:00                  0 Y
        25 2020-02-22  14:33:07                  0 N

 

常用的任务时间间隔：

1:每分钟执行 
Interval => TRUNC(sysdate,'mi') + 1/ (24*60) --每分钟执行 
interval => 'sysdate+1/（24*60）' --每分钟执行 
interval => 'sysdate+1' --每天 
interval => 'sysdate+1/24' --每小时 
interval => 'sysdate+2/24*60' --每2分钟 
interval => 'sysdate+30/24*60*60' --每30秒 

2:每天定时执行 
Interval => TRUNC(sysdate+1) --每天凌晨0点执行 
Interval => TRUNC(sysdate+1)+1/24 --每天凌晨1点执行 
Interval => TRUNC(SYSDATE+1)+(8*60+30)/(24*60) --每天早上8点30分执行 

3:每周定时执行 
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24 --每周一凌晨1点执行 
Interval => TRUNC(next_day(sysdate,1))+2/24 --每周一凌晨2点执行 

4:每月定时执行 
Interval =>TTRUNC(LAST_DAY(SYSDATE)+1) --每月1日凌晨0点执行 
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24 --每月1日凌晨1点执行 

5:每季度定时执行 
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q') --每季度的第一天凌晨0点执行 
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q') + 1/24 --每季度的第一天凌晨1点执行 
Interval => TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24 --每季度的最后一天的晚上11点执行 

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

7:每年定时执行 
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24 --每年1月1日凌晨1点执行