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. 参数说明
参数 | 说明 |
| 请传入 |
| 调用存储过程名称，不可为空。本次示例中使用了 |
| 定时任务的开始时间，如果不传入则默认为当前时间。 |
| 定时任务的执行间隔。具体内容，请参见INTERVAL参考。 |
表 2. INTERVAL参考
定时任务执行间隔 | 示例 |
每分钟执行 | |
每天定时执行 | 每天凌晨1点执行： |
每周定时执行 | 每周一凌晨1点执行： |
每月定时执行 | 每月1日凌晨1点执行： |
每季度定时执行 | 每季度的第一天凌晨1点执行： |
每年定时执行 | 每年1月1日凌晨1点执行： |
固定时间点执行 | 每天早上的8点10分运行： |
固定时间间隔执行 | 每个小时的第15分钟运行，例如8点15分、9点15分、10点15分等。 |
调用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. 参数说明
参数 | 说明 |
| 任务ID，如何查看请参见查看定时任务。 |
| 调用存储过程名称。 说明 如果保持当前值不变，可将此参数设置为 |
| 定时任务的开始时间。 说明 如果保持当前值不变，可将此参数设置为 |
| 定时任务的执行间隔。具体内容，请参见INTERVAL参考。 说明 如果保持当前值不变，可将此参数设置为 |
修改任务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. 参数说明
参数 | 说明 |
| 任务ID，如何查看请参见查看定时任务。 |
| 定时任务的执行间隔。具体内容，请参见INTERVAL参考。 |
修改任务ID为1的定时任务，任务执行间隔修改为每天凌晨1点执行一次。示例如下：
BEGIN
DBMS_JOB.INTERVAL(1,'TRUNC(sysdate) + 1 + 1/(24)');
END;修改定时任务的执行时间
语法
NEXT_DATE(job BINARY_INTEGER, next_date DATE)表 5. 参数说明
参数 | 说明 |
| 任务ID，如何查看请参见查看定时任务。 |
| 定时任务的开始时间。 |
修改任务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. 参数说明
参数 | 说明 |
| 任务ID，如何查看请参见查看定时任务。 |
| 调用的存储过程名称。 |
修改任务ID为1的定时任务，将调用的存储过程改为job_proc2。示例如下：
BEGIN
DBMS_JOB.WHAT(1,'job_proc2');
END;停止和启动定时任务
语法
BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])表 7. 参数说明
参数 | 说明 |
| 任务ID，如何查看请参见查看定时任务。 |
| 定时任务的状态，取值如下：
|
| 定时任务的开始时间，如果不传入则默认为当前时间。 |
将任务ID为1的定时任务设置为已损坏状态。示例如下：
BEGIN
DBMS_JOB.BROKEN(1,true);
END;将任务ID为1的定时任务设置为正常运行状态。示例如下：
BEGIN
DBMS_JOB.BROKEN(1,false);
END;强制启动定时任务
语法
RUN(job BINARY_INTEGER)表 8. 参数说明
参数 | 说明 |
| 任务ID，如何查看请参见查看定时任务。 |
强制启动任务ID为1的定时任务。示例如下：
BEGIN
DBMS_JOB.RUN(1);
END;删除定时任务
语法
REMOVE(job BINARY_INTEGER)表 9. 参数说明
参数 | 说明 |
| 任务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库中的某张表每分钟插入一条数据。如何创建数据库，请参见创建数据库。
在
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;
切换到
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.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
切换到
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;