DBMS_JOB

PolarDB提供DBMS_JOB包调度与管理定时任务。本文为您介绍如何创建、管理和删除一个定时任务。

准备测试数据

说明

测试数据仅适用本文中的操作示例。

创建一张名为jobrun的表用于测试,示例如下:

CREATE TABLE jobrun (
    id              serial               NOT NULL PRIMARY KEY,
    runtime         VARCHAR2(40)
);

创建一个名为job_proc的存储过程,示例如下:

CREATE PROCEDURE job_proc
IS
BEGIN
    INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;

创建定时任务

语法

SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])

表 1. 参数说明

参数

说明

job

请传入jobid,用于返回本次提交任务的ID。该ID自动生成且对每个任务都唯一。

what

调用存储过程名称,不可为空。本次示例中使用了job_proc存储过程。

next_date

定时任务的开始时间,如果不传入则默认为当前时间。

interval

定时任务的执行间隔。具体内容,请参见INTERVAL参考

表 2. INTERVAL参考

定时任务执行间隔

示例

每分钟执行

TRUNC(sysdate,'mi') + 1/(24*60)

每天定时执行

每天凌晨1点执行:

TRUNC(sysdate) + 1 + 1/(24)

每周定时执行

每周一凌晨1点执行:

TRUNC(next_day(sysdate,'monday')) + 1/24

每月定时执行

每月1日凌晨1点执行:

TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24

每季度定时执行

每季度的第一天凌晨1点执行:

TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

每年定时执行

每年1月1日凌晨1点执行:

ADD_MONTHS(trunc(sysdate,'yyyy'), 12) + 1/24

固定时间点执行

每天早上的8点10分运行:

Trunc(Sysdate+1) + (8*60+10)/24*60

固定时间间隔执行

每个小时的第15分钟运行,例如8点15分、9点15分、10点15分等。

Trunc(sysdate,'hh') + (60+15)/(24*60)

调用job_proc存储过程创建一个定时任务,示例如下:

DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)');
END;
说明

如果定时任务中的时间使用了单引号,这种嵌套会导致语法错误,错误示例如下:

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'TRUNC(sysdate,'mi') + 1/(24*60)');

您需要将对应的参数改为$$的形式来避免这种错误,正确示例如下:

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, $$TRUNC(sysdate,'mi') + 1/(24*60)$$);

修改定时任务的内容、执行时间和执行间隔

语法

CHANGE(job BINARY_INTEGER what VARCHAR2, next_date DATE,interval VARCHAR2)

表 3. 参数说明

参数

说明

job

任务ID,如何查看请参见查看定时任务

what

调用存储过程名称。

说明

如果保持当前值不变,可将此参数设置为NULL

next_date

定时任务的开始时间。

说明

如果保持当前值不变,可将此参数设置为NULL

interval

定时任务的执行间隔。具体内容,请参见INTERVAL参考

说明

如果保持当前值不变,可将此参数设置为NULL

修改任务ID为1的定时任务,调用的存储过程不变,定时任务开始时间修改为2020年12月29日,任务执行间隔设置为每个小时的15分执行一次。示例如下:

BEGIN
    DBMS_JOB.CHANGE(1,NULL,TO_DATE('29-DEC-20','DD-MON-YY'),$$Trunc(sysdate,'hh') + (60+15)/(24*60)$$);
END;

修改定时任务的执行间隔

语法

INTERVAL(job BINARY_INTEGER, interval VARCHAR2)

表 4. 参数说明

参数

说明

job

任务ID,如何查看请参见查看定时任务

interval

定时任务的执行间隔。具体内容,请参见INTERVAL参考

修改任务ID为1的定时任务,任务执行间隔修改为每天凌晨1点执行一次。示例如下:

BEGIN
    DBMS_JOB.INTERVAL(1,'TRUNC(sysdate) + 1 + 1/(24)');
END;

修改定时任务的执行时间

语法

NEXT_DATE(job BINARY_INTEGER, next_date DATE)

表 5. 参数说明

参数

说明

job

任务ID,如何查看请参见查看定时任务

next_date

定时任务的开始时间。

修改任务ID为1的定时任务,定时任务开始时间设置为2020年12月30日。示例如下:

BEGIN
    DBMS_JOB.NEXT_DATE(1, TO_DATE('30-DEC-20','DD-MON-YY'));
END;

修改定时任务的内容

语法

WHAT(job BINARY_INTEGER, what VARCHAR2)

表 6. 参数说明

参数

说明

job

任务ID,如何查看请参见查看定时任务

what

调用的存储过程名称。

修改任务ID为1的定时任务,将调用的存储过程改为job_proc2。示例如下:

BEGIN
    DBMS_JOB.WHAT(1,'job_proc2');
END;

停止和启动定时任务

语法

BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])

表 7. 参数说明

参数

说明

job

任务ID,如何查看请参见查看定时任务

broken

定时任务的状态,取值如下:

  • true:定时任务的状态设置为已损坏,设置为损坏的定时任务将停止运行,您可以通过dbms_job.run强行运行已损坏状态的定时任务。

  • false:定时任务的状态设置为正常运行。

next_date

定时任务的开始时间,如果不传入则默认为当前时间。

将任务ID为1的定时任务设置为已损坏状态。示例如下:

BEGIN
    DBMS_JOB.BROKEN(1,true);
END;

将任务ID为1的定时任务设置为正常运行状态。示例如下:

BEGIN
    DBMS_JOB.BROKEN(1,false);
END;

强制启动定时任务

语法

RUN(job BINARY_INTEGER)

表 8. 参数说明

参数

说明

job

任务ID,如何查看请参见查看定时任务

强制启动任务ID为1的定时任务。示例如下:

BEGIN
    DBMS_JOB.RUN(1);
END;

删除定时任务

语法

REMOVE(job BINARY_INTEGER)

表 9. 参数说明

参数

说明

job

任务ID,如何查看请参见查看定时任务

删除任务ID为1的定时任务。示例如下:

BEGIN
    DBMS_JOB.REMOVE(1);
END;

查看定时任务

您可以通过如下语句查看当前用户创建的定时任务列表:

select * from sys.user_jobs;

查看任务执行记录

您可以通过如下语句查看定时任务的执行记录:

select * from dbmsjob.pga_joblog;

跨库执行定时任务

由于DBMS_JOB只能配置在postgres库,其他库如果需要设置定时任务则需要进行跨库任务。

以下示例中,DBMS_JOB的配置库为postgres,运行库为test,需要在test库中的某张表每分钟插入一条数据。如何创建数据库,请参见创建数据库

  1. test库中创建一个名为jobrun的表和一个名为job_proc的存储过程。

    • 创建名为jobrun的表,命令如下:

      CREATE TABLE public.jobrun (
          id              serial               NOT NULL PRIMARY KEY,
          runtime         VARCHAR2(40)
      );
    • 创建名为job_proc的存储过程,命令如下:

      CREATE PROCEDURE public.job_proc
      IS
      BEGIN
          INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
      END;
  2. 切换到postgres库,创建定时任务。

    您需要在DBMS_JOB.SUBMIT()函数中添加目标库,本例中目标库名为test。其他参数请参见创建定时任务

    示例如下:

    DECLARE
       jobid           INTEGER;
    BEGIN
       DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
    END;
  3. postgres中查看定时任务的状态和执行记录。

    • 查看定时任务:

      select * from sys.user_jobs;

      返回结果如下:

       job | jobloguser | job_user | database |           job_created            |           job_changed            |            last_date             | last_sec |         next_date         | next_sec |   total_time    | broken |                 interval                 | failures |                                                      what                                                       | instance
      -----+------------+----------+----------+----------------------------------+----------------------------------+----------------------------------+----------+---------------------------+----------+-----------------+--------+------------------------------------------+----------+-----------------------------------------------------------------------------------------------------------------+----------
         1 | DBUSER     | dbuser   | postgres | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:51:12.025001 +00:00 | 02:51:12 | 29-OCT-20 02:53:12 +00:00 | 02:53:12 | 00:00:00.243224 | N      | BEGIN return SYSDATE + 1/(24 * 30); END; |        0 |  BEGIN EXECUTE IMMEDIATE 'SELECT dbmsjob.dbms_job_internal_job_link(''BEGIN job_proc; END;'', ''test'');' ; END |        0
    • 查看执行记录:

      select * from dbmsjob.pga_joblog;

      返回结果如下:

       jlgid | jlgjobid | jlgstatus |             jlgstart             |   jlgduration   
      -------+----------+-----------+----------------------------------+-----------------
           1 |        1 | s         | 29-OCT-20 02:38:49.762995 +00:00 | 00:00:00.017495
           2 |        1 | s         | 29-OCT-20 02:39:50.061113 +00:00 | 00:00:00.016463
           3 |        1 | s         | 29-OCT-20 02:40:50.062331 +00:00 | 00:00:00.016244
  4. 切换到test库,查看表中数据。

    查询命令如下:

    select * from jobrun;

    查询结果如下:

     id |               runtime               
    ----+-------------------------------------
      1 | job_proc run at 2020-10-29 02:38:50
      2 | job_proc run at 2020-10-29 02:39:50
      3 | job_proc run at 2020-10-29 02:40:50
说明

如果您需要修改跨库任务的信息,需要在修改函数时附加对应的数据库名称。例如将上述示例中每分钟运行一次的计划改为每两分钟运行一次,示例如下:

BEGIN
    DBMS_JOB.CHANGE(1,NULL,SYSDATE,'SYSDATE + 1/(24 * 30)','test');
END;