DBMS_JOB.SUBMIT 的 interval 參數為 VARCHAR2(200),複雜排程邏輯 (如「每月最後一個工作日」、「避開節假日」) 無法在 200 字元內表達。本文介紹利用 **NEXT_DATE 輸出參數** 動態計算下次執行時間的進階技巧。
Oracle 內部執行 JOB 時,實際調用如下匿名區塊 (不可直接查看,由 Tom Kyte 揭示):
DECLARE
job BINARY_INTEGER := :job;
next_date DATE := :mydate;
broken BOOLEAN := FALSE;
BEGIN
-- 執行用戶 WHAT 程序
-- 用戶程序可修改 :mydate (OUT 參數) 指定下次執行時間
-- 用戶程序可修改 :broken (OUT 參數) 標記 JOB 失效
:mydate := next_date; -- 預設保持原排程
IF broken THEN :b := 1; ELSE :b := 0; END IF;
END;
PROCEDURE my_job(p_next_date OUT DATE, p_broken OUT BOOLEAN);interval 字串限制;CREATE OR REPLACE PROCEDURE job_last_workday (
p_next_date OUT DATE,
p_broken OUT BOOLEAN
) AS
v_next DATE;
BEGIN
-- 計算下個月最後一天
v_next := TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') - 1;
-- 向前找工作日 (排除週六日)
WHILE TO_CHAR(v_next, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT','SUN') LOOP
v_next := v_next - 1;
END LOOP;
-- 排除節假日表 (假設 holiday_tab 存放日期)
WHILE EXISTS (SELECT 1 FROM holiday_tab WHERE h_date = v_next) LOOP
v_next := v_next - 1;
WHILE TO_CHAR(v_next, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT','SUN') LOOP
v_next := v_next - 1;
END LOOP;
END LOOP;
p_next_date := v_next;
p_broken := FALSE;
-- 實際業務邏輯
process_month_end_closing(v_next);
EXCEPTION
WHEN OTHERS THEN
p_broken := TRUE; -- 失敗標記 BROKEN,避免反覆重試
RAISE;
END;
DECLARE
v_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => v_job,
what => 'job_last_workday(NEXT_DATE, BROKEN);', -- 必須大寫參數名
next_date => TRUNC(SYSDATE) + 1/24, -- 明日凌晨 1 點首次執行
interval => 'NULL' -- 關鍵:interval 設為 NULL,完全由 NEXT_DATE 控制
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Job ' || v_job || ' submitted.');
END;
普通一次性 JOB (`interval => NULL`) 執行後即從 `USER_JOBS` 消失。若需保留記錄供稽核:
CREATE OR REPLACE PROCEDURE one_time_job (
p_broken OUT BOOLEAN
) AS
BEGIN
-- 業務邏輯
process_year_end_report;
-- 標記 BROKEN,JOB 保留但不再執行
p_broken := TRUE;
EXCEPTION
WHEN OTHERS THEN
p_broken := TRUE;
RAISE;
END;
/
-- 提交
DBMS_JOB.SUBMIT(v_job, 'one_time_job(BROKEN);', SYSDATE, 'NULL');
驗證:
SELECT job, what, broken, failures, last_date, next_date
FROM user_jobs WHERE job = v_job;
-- 執行後:BROKEN='Y', FAILURES=0, NEXT_DATE=NULL, 記錄永久保留
CREATE OR REPLACE PROCEDURE adaptive_job (
p_next_date OUT DATE,
p_broken OUT BOOLEAN
) AS
v_load NUMBER;
BEGIN
-- 檢測系統負載
SELECT value INTO v_load FROM v$sysmetric
WHERE metric_name = 'Host CPU Utilization (%)' AND rownum=1;
IF v_load > 80 THEN
-- 高負載:推遲 1 小時,降低平行度
p_next_date := SYSDATE + 1/24;
execute_immediate 'ALTER SESSION SET PARALLEL_DEGREE_POLICY=LIMITED';
ELSE
p_next_date := SYSDATE + 1/1440; -- 1 分鐘後
execute_immediate 'ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO';
END IF;
-- 執行資源密集型任務
heavy_etl_process;
END;
雖然 NEXT_DATE 技巧強大,但 **DBMS_SCHEDULER** 提供更優雅的解決方案:
CREATE_SCHEDULE 定義複雜排程 (日曆語法、節假日排除);CREATE_JOB 關聯排程,支援事件驅動、鏈式作業、資源管理器整合;JOB_ACTION 可為存儲過程、PL/SQL 區塊、外部腳本;利用 NEXT_DATE / BROKEN OUT 參數動態控制 JOB 排程與生命週期,是突破 INTERVAL 字串長度限制、實現複雜業務排程的經典技巧。新系統建議直接採用 DBMS_SCHEDULER,既有系統可平滑遷移。