# DBMS_JOB #### 接口介绍 高级功能包DBMS_JOB支持的所有接口请参见[表1]。 表1DBMS_JOB | 接口名称 | 描述 | |:---|:---| | [DBMS_JOB.SUBMIT] | 提交一个定时任务。作业号由系统自动生成。 | | [DBMS_JOB.SUBMIT_NODE] | 提交一个定时任务。执行节点由用户指定，作业号由系统自动生成。 | | [DBMS_JOB.ISUBMIT] | 提交一个定时任务。作业号由用户指定。 | | [DBMS_JOB.REMOVE] | 通过作业号来删除定时任务。 | | [DBMS_JOB.BROKEN] | 禁用或者启用定时任务。 | | [DBMS_JOB.CHANGE] | 修改定时任务的属性，包括任务内容、下次执行时间、执行间隔。 | | [DBMS_JOB.WHAT] | 修改定时任务的任务内容属性。 | | [DBMS_JOB.NEXT_DATE] | 修改定时任务的下次执行时间属性。 | | [DBMS_JOB.INTERVAL] | 修改定时任务的执行间隔属性。 | | [DBMS_JOB.CHANGE_OWNER] | 修改定时任务的属主。 | | [DBMS_JOB.CHANGE_NODE] | 修改定时任务的执行节点。 | - DBMS_JOB.SUBMIT 存储过程SUBMIT提交一个系统提供的定时任务。 DBMS_JOB.SUBMIT函数原型为： ``` DBMS_JOB.SUBMIT( what IN TEXT, next_date IN TIMESTAMP DEFAULT sysdate, job_interval IN TEXT DEFAULT 'null', job OUT INTEGER); ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) 当创建一个定时任务（DBMS_JOB）时，系统默认将当前数据库和用户名与当前创建的定时任务（DBMS_JOB）绑定起来。该接口函数可以通过call或select调用，如果通过select调用，可以不填写出参。如果在存储过程中，则需要通过perform调用该接口函数。 表2DBMS_JOB.SUBMIT接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | what | text | IN | 否 | 要执行的SQL语句。支持一个或多个"DML"、"匿名块"、"调用存储过程的语句"或3种混合的场景。 | | next_date | timestamp | IN | 否 | 下次作业运行时间。默认值为当前系统时间（sysdate）。如果是过去时间，在提交作业时表示立即执行。 | | interval | text | IN | 是 | 用来计算下次作业运行时间的时间表达式，可以是interval表达式，也可以是sysdate加上一个numeric值（例如：sysdate+1.0/24）。如果为空值或字符串"null"表示只执行一次，执行后JOB状态STATUS变成'd' 不再执行。 | | job | integer | OUT | 否 | 作业号。范围为1～32767。当使用select调用dbms.submit时，该参数可以省略。 | 示例： ``` select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1'); select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24'); CALL DBMS_JOB.SUBMIT('INSERT INTO T_JOB VALUES(1); call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid); ``` - DBMS_JOB.SUBMIT_NODE 存储过程SUBMIT提交一个系统提供的定时任务。执行节点由用户指定。该接口仅8.3.0及以上集群版本支持。 DBMS_JOB.SUBMIT_NODE函数原型为： ``` DBMS_JOB.SUBMIT_NODE( what IN TEXT, next_date IN TIMESTAMP DEFAULT sysdate, job_interval IN TEXT DEFAULT 'null', job_node IN TEXT DEFAULT NULL, job OUT INTEGER); ``` 表3DBMS_JOB.SUBMIT_NODE接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | what | text | IN | 否 | 要执行的SQL语句。支持一个或多个DML、匿名块、调用存储过程的语句或3种混合的场景。 | | next_date | timestamp | IN | 否 | 下次作业运行时间。默认值为当前系统时间（sysdate）。如果是过去时间，在提交作业时表示立即执行。 | | interval | text | IN | 是 | 用来计算下次作业运行时间的时间表达式，可以是interval表达式，也可以是sysdate加上一个numeric值（例如：sysdate+1.0/24）。如果为空值或字符串"null"则表示只执行一次，执行后JOB状态STATUS变成'd'则不再执行。 | | node | text | IN | 是 | 作业执行节点名称。 | | job | integer | OUT | 否 | 作业号。范围为1～32767。当使用select调用dbms.submit时，该参数可以省略。 | 示例： ``` select DBMS_JOB.SUBMIT_NODE('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1','coordinator1'); select DBMS_JOB.SUBMIT_NODE('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24'); CALL DBMS_JOB.SUBMIT('INSERT INTO T_JOB VALUES(1); call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)', 'coordinator1', :jobid); ``` - DBMS_JOB.ISUBMIT ISUBMIT与SUBMIT语法功能相同，但其第一个参数是入参，即指定的作业号，SUBMIT最后一个参数是出参，表示系统自动生成的作业号。 示例： ``` CALL dbms_job.isubmit(101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24'); ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) DWS的pgstats持久化功能将内存中的统计信息写入pg_stat_object系统表，如果新装的9.1.0.100及以上集群版本，会占用1作为job_id。如果为低版本升级到9.1.0.100及以上集群版本，并且之前pg_job中有任务，那么会找到一个未被占用的job_id作为持久化任务的ID。所以在使用dbms_job.isubmit接口时需注意，不能和已经存在的pgstats持久化任务的ID重复，否则会导致任务注册失败。 - DBMS_JOB.REMOVE 存储过程REMOVE删除指定的定时任务。 DBMS_JOB.REMOVE函数原型为： ``` REMOVE(job IN INTEGER); ``` 表4DBMS_JOB.REMOVE接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | 示例： ``` CALL dbms_job.remove(101); ``` - DBMS_JOB.BROKEN 存储过程BROKEN禁用或者启用定时任务。 DBMS_JOB.BROKEN函数原型为： ``` DBMS_JOB.BROKEN( job IN INTEGER, broken IN BOOLEAN, next_date IN TIMESTAMP DEFAULT sysdate); ``` 表5DBMS_JOB.BROKEN接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | | broken | boolean | IN | 否 | 状态标志位，true代表禁用，false代表启用。具体true或false值更新当前job；如果为空值，则不改变原有job的状态。 | | next_date | timestamp | IN | 是 | 下次运行时间，默认为当前系统时间。如果参数broken状态为true，则更新该参数为'4000-1-1'；如果参数broken状态为false，且如果参数next_date不为空值，则更新指定job的next_date值，如果next_date为空值，则不更新next_date值。该参数可以省略，为默认值。 | 示例： ``` CALL dbms_job.broken(101,true); CALL dbms_job.broken(101,false,sysdate); ``` - DBMS_JOB.CHANGE 存储过程CHANGE修改定时任务的属性，包括任务内容、下次执行时间、执行间隔。 DBMS_JOB.CHANGE函数原型为： ``` DBMS_JOB.CHANGE( job IN INTEGER, what IN TEXT, next_date IN TIMESTAMP, interval IN TEXT); ``` 表6DBMS_JOB.CHANGE接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | | what | text | IN | 是 | 执行的存储过程名或者sql语句块。如果该参数为空值，则不更新指定job的what值，否则更新指定job的what值。 | | next_date | timestamp | IN | 是 | 下次运行时间。如果该参数为空值，则不更新指定job的next_date值，否则更新指定job的next_date值。 | | interval | text | IN | 是 | 用来计算下次作业运行时间的时间表达式。如果该参数为空值，则不更新指定job的interval值；如果该参数不为空值，会校验interval是否为有效的时间类型或interval类型，则更新指定job的interval值。如果为字符串"null"表示只执行一次，执行后JOB状态STATUS变成'd' 不再执行。 | 示例： ``` CALL dbms_job.change(101, 'call userproc();', sysdate, 'sysdate + 1.0/1440'); CALL dbms_job.change(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440'); ``` - DBMS_JOB.WHAT 存储过程WHAT修改定时任务的任务内容属性。 DBMS_JOB.WHAT函数原型为： ``` DBMS_JOB.WHAT( job IN INTEGER, what IN TEXT); ``` 表7DBMS_JOB.WHAT接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | | what | text | IN | 否 | 执行的存储过程调用或者sql语句块。 | ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) - 当what参数是一个或多个可以执行成功的sql语句/程序块/调用存储过程时，该接口函数才能被执行成功，否则会执行失败。 - 若what参数为一个简单的insert、update等语句，需要在表前加模式名。 示例： ``` CALL dbms_job.what(101, 'call userproc();'); CALL dbms_job.what(101, 'insert into tbl_a values(sysdate);'); ``` - DBMS_JOB.NEXT_DATE 存储过程NEXT_DATE修改定时任务的下次执行时间属性。 DBMS_JOB.NEXT_DATE函数原型为： ``` DBMS_JOB.NEXT_DATE( job IN INTEGER, next_date IN TIMESTAMP); ``` 表8DBMS_JOB.NEXT_DATE接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | | next_date | timestamp | IN | 否 | 下次运行时间。 | ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) 如果输入的next_date的值小于当前日期值，该job会立即执行一次。 示例： ``` CALL dbms_job.next_date(101,sysdate); ``` - DBMS_JOB.INTERVAL 存储过程INTERVAL修改定时任务的执行间隔属性。 DBMS_JOB.INTERVAL函数原型为： ``` DBMS_JOB.INTERVAL( job IN INTEGER, interval IN TEXT); ``` 表9DBMS_JOB.INTERVAL接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | | interval | text | IN | 是 | 用来计算下次作业运行时间的时间表达式。如果为空值或字符串"null"表示只执行一次，执行后JOB状态STATUS变成'd' 不再执行。interval是否为有效的时间类型或interval类型。 | 示例： ``` CALL dbms_job.interval(101, 'sysdate + 1.0/1440'); ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) 对于指定job正在运行状态（即job_status为'r'）时，不允许通过remove、change、next_date、what、interval等接口删除或修改job的参数信息。 - DBMS_JOB.CHANGE_OWNER 存储过程CHANGE_OWNER修改定时任务的属主。 DBMS_JOB.CHANGE_OWNER函数原型为： ``` DBMS_JOB.CHANGE_OWNER( job IN INTEGER, new_owner IN NAME); ``` 表10DBMS_JOB.CHANGE_OWNER接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | | new_owner | name | IN | 否 | 新的用户名。 | 示例： ``` CALL dbms_job.change_owner(101, 'alice'); ``` - DBMS_JOB.CHANGE_NODE 存储过程CHANGE_NODE修改定时任务的执行节点。该接口仅8.3.0及以上集群版本支持。 DBMS_JOB.CHANGE_NODE函数原型为： ``` DBMS_JOB.CHANGE_NODE( job IN INTEGER, new_node IN text); ``` 表11DBMS_JOB.CHANGE_OWNER接口参数说明 | 参数 | 类型 | 入参/出参 | 是否可以为空 | 描述 | |:---|:---|:---|:---|:---| | job | integer | IN | 否 | 指定的作业号。 | | new_node | text | IN | 否 | 新的执行节点。 | 示例： ``` CALL dbms_job.change_node(101, 'coordinator2'); ``` #### 约束说明 1. 创建一个新job后，该job从属于当前coordinator（即：该job仅在当前coordinator上调度和执行），其他coordinator不会调度和执行该job。所有coordinator都可以查看、修改、删除其他CN创建的job。 2. job只能通过dbms_job高级包提供的接口进行创建、更新、删除操作，因为高级包的接口中会考虑所有CN间job信息的同步和pg_jobs表主键的关联操作，如果通过DML语句对pg_jobs表进行增删改，会导致job信息在CN间不一致和系统表无法关联变更的混乱问题，会严重影响job内部的管理。 3. 由于用户创建的每个任务和CN绑定，若不开启CN故障自动迁移功能，当任务运行过程中，该CN故障，则该任务的状态无法实时刷新。如果在任务未执行时CN故障，则该CN上的任务都得不到正常的调度和执行。建议开启CN故障自动迁移功能，故障CN上的作业会迁移至其他CN继续调度。 4. job在定时执行过程中，需要在当前job所属的CN上实时更新该job的运行状态、最近执行开始时间、最近执行结束时间、下次开始时间、失败次数（如果job执行失败）等相关参数信息到pg_jobs系统表中，并同步到其他CN，保证job信息的一致性。如果其他CN存在节点故障，那么job所属CN会同步超时重发的处理，导致job执行时间变长，但CN间同步超时失败后，原CN上pg_jobs表中job的相关信息仍然能正常更新，且job能正常执行成功。当故障CN恢复正常后，可能出现该CN上pg_jobs表中当前job的执行时间、运行状态等参数与原CN上不一致的情况，需要原CN上再次执行该job后才能保证job信息的同步。 5. 对于并发同时有多个job到达执行时间的场景，由于会为每个job创建一个线程来执行job，由于系统内部启动每个线程的时间会有延迟，因此会导致同时并发执行的job的开始时间有延迟，每个job的延迟时间在0.1ms左右。 6. job中待执行SQL语句有长度限制，最长为8K。