PolarDB-O 云原生数据库 DBMS_JOB

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

前提条件

DBMS_JOB暂不支持控制台或命令行开启，如有需要您可以提交工单联系技术支持进行开启。
PolarDB集群的内核版本为V1.1.7及以上版本。如何查看内核版本，请参见新版本更新说明。

注意事项

DBMS_JOB仅支持高权限用户创建及使用。如何创建高权限账号，请参见创建数据库账号。
如果之前安装过DBMS_JOB相关插件，需要先删除相关插件，然后参考本文重新安装。删除插件语法如下：
```
drop extension dbms_job;
```
警告删除插件会同时删除相关定时任务，请务必提前备份。
目前仅支持在postgres库中创建DBMS_JOB插件，如果您需要在其他库中使用定时任务，可以在postgres库中配置跨库任务。具体操作，请参见跨库执行定时任务。

准备测试数据

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

创建一张名为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;

创建或删除插件

DBMS_JOB包需要创建dbms_job插件后才可以使用，创建或删除插件语法如下：

创建插件

CREATE EXTENSION IF NOT EXISTS dbms_job;

删除插件
```
DROP EXTENSION dbms_job CASCADE;
```
警告删除插件时会删除所有已有的用户任务。

创建定时任务

语法：

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


参数	说明
`job`	请传入`jobid`，用于返回本次提交任务的ID。该ID自动生成且对每个任务都唯一。
`what`	调用存储过程名称，不可为空。本次示例中使用了`job_proc`存储过程。
`next_date`	定时任务的开始时间，如果不传入则默认为当前时间。
`interval`	定时任务的执行间隔。具体内容，请参见表 1。

表 1.
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) + (860+10)/2460`
固定时间间隔执行	每个小时的第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)


参数	说明
`job`	任务ID，如何查看请参见查看定时任务。
`what`	调用存储过程名称。说明如果保持当前值不变，可将此参数设置为 `NULL`。
`next_date`	定时任务的开始时间。说明如果保持当前值不变，可将此参数设置为 `NULL`。
`interval`	定时任务的执行间隔。具体内容，请参见表 1。说明如果保持当前值不变，可将此参数设置为 `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)


参数	说明
`job`	任务ID，如何查看请参见查看定时任务。
`interval`	定时任务的执行间隔。具体内容，请参见表 1。

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

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

修改定时任务的执行时间

语法：

NEXT_DATE(job BINARY_INTEGER, next_date DATE)


参数	说明
`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)


参数	说明
`job`	任务ID，如何查看请参见查看定时任务。
`what`	调用的存储过程名称。

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

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

停止和启动定时任务

语法：

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


参数	说明
`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)


参数	说明
`job`	任务ID，如何查看请参见查看定时任务。

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

BEGIN
    DBMS_JOB.RUN(1);
END;

删除定时任务

语法：

REMOVE(job BINARY_INTEGER)


参数	说明
`job`	任务ID，如何查看请参见查看定时任务。

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

BEGIN
    DBMS_JOB.REMOVE(1);
END;

查看定时任务

您可以通过如下语句查看定时任务列表：

select * from sys.dba_jobs;

查看任务执行记录

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

select * from dbmsjob.pga_joblog;

跨库执行定时任务

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

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

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

创建名为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;

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

示例如下：
```
DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
END;
```

在postgres中查看定时任务的状态和执行记录。

查看定时任务：

select * from sys.dba_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;