将DBMS_JOB迁移至DBMS_SCHEDULER：自定义间隔函数实现疑问

问题描述 #

现有DBMS_JOB使用自定义函数stk_HHD.SetNext作为INTERVAL参数，该函数查询stk_Schedule表中的运行时间列表，返回当前SYSDATE之后的下一个运行时间。由于每日运行时间非均匀分布，无法使用FREQ=HOURLY这类标准间隔配置，需在DBMS_SCHEDULER中实现相同逻辑。

原DBMS_JOB配置、函数及示例数据如下：

-- DBMS_JOB
INTERVAL = stk_HHD.SetNext

-- 间隔函数
FUNCTION SetNext
    RETURN  DATE
IS
    PfldNext    DATE;
BEGIN

    SELECT MIN(MOD(MOD(transfer-SysDate,1)+1,1))+SysDate INTO PfldNext
      FROM stk_Schedule
     WHERE source = 'HHD'
    ;

    RETURN NVL(PfldNext,TRUNC(SysDate,'MI')+1);

END SetNext;

-- 示例数据
SOURCE TRANSFER          
------ ------------------
HHD    01-APR-08 02:45:00
HHD    01-APR-08 06:45:00
HHD    01-APR-08 08:15:00
HHD    01-APR-08 09:45:00
HHD    01-APR-08 11:15:00
HHD    01-APR-08 12:45:00
HHD    01-APR-08 14:15:00
HHD    01-APR-08 15:45:00
HHD    01-APR-08 17:15:00

解决方案 #

DBMS_SCHEDULER支持直接使用自定义函数作为重复间隔，无需依赖标准日历表达式，具体实现如下：

方法1：在REPEAT_INTERVAL中调用自定义函数 #

DBMS_SCHEDULER的REPEAT_INTERVAL参数支持接受返回DATE类型的函数调用，逻辑和DBMS_JOB的INTERVAL完全一致。每次作业执行完成后，调度器会调用该函数获取下一次运行时间。

创建作业的示例代码：

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'HHD_SCHEDULED_TASK', -- 自定义作业名
    job_type        => 'PLSQL_BLOCK', -- 匹配原DBMS_JOB任务类型，原是存储过程则改为'PROCEDURE'
    job_action      => 'BEGIN -- 此处替换为原DBMS_JOB执行的任务代码; END;',
    repeat_interval => 'stk_HHD.SetNext()', -- 直接调用自定义函数
    enabled         => TRUE,
    comments        => '迁移自原DBMS_JOB，基于stk_Schedule表动态计算运行时间'
  );
END;
/

注意事项 #

• 确保作业所属用户拥有执行stk_HHD.SetNext函数的权限，无权限则执行授权：

  GRANT EXECUTE ON stk_HHD.SetNext TO 作业所属用户名;
  

• 原函数通过MOD提取时间部分，计算当日或次日的下一个匹配时间，该逻辑完全兼容DBMS_SCHEDULER调度机制，无需修改函数代码。

备选方案：事件驱动调度 #

若需实时响应stk_Schedule表的数据变更，可结合DBMS_SCHEDULER的事件调度：

1. 创建触发器，当stk_Schedule表的HHD相关数据变更时，调用DBMS_SCHEDULER.SET_ATTRIBUTE更新作业的下次运行时间。
2. 初始作业设置为单次运行，由触发器动态调整执行时间。此方式复杂度较高，仅在需要实时更新调度逻辑时使用。

内容的提问来源于stack exchange，提问作者Background-Agent-746