Oracle DBMS_JOB 實作：定時自動清除失效資料

需求場景

業務表 CJ_JT_NSRCPDMDY 中含狀態欄位 BZ（'啟用'/'不啟用'），需每月 1 日午夜 12 點自動刪除所有「不啟用」記錄。

完整實作步驟

1. 建立測試表與資料

CREATE TABLE CJ_JT_NSRCPDMDY (
    ID    VARCHAR2(40) PRIMARY KEY,
    CPBH  VARCHAR2(80) NOT NULL,
    NSRBH VARCHAR2(80) NOT NULL,
    BZ    VARCHAR2(80)
);

INSERT INTO CJ_JT_NSRCPDMDY VALUES ('1', '001', '001', '啟用');
INSERT INTO CJ_JT_NSRCPDMDY VALUES ('2', '002', '002', '啟用');
INSERT INTO CJ_JT_NSRCPDMDY VALUES ('3', '003', '003', '不啟用');

2. 建立刪除預存程序

CREATE OR REPLACE PROCEDURE mypro AS
BEGIN
    DELETE FROM CJ_JT_NSRCPDMDY WHERE BZ = '不啟用';
    COMMIT;
END;
/
-- 程序已建立

3. 建立排程 Job（每月 1 日 00:00 執行）

VARIABLE job NUMBER;
BEGIN
    -- trunc(last_day(sysdate)+1) 取得下個月第一天 00:00
    DBMS_JOB.SUBMIT(
        :job,
        'mypro;',
        TRUNC(LAST_DAY(SYSDATE) + 1),
        'TRUNC(LAST_DAY(SYSDATE) + 1)'
    );
    COMMIT;
END;
/
-- 關鍵提醒：執行到此即完成排程建立，切勿繼續執行第 5 步驟，
-- 否則會立即觸發執行而非等待排程時間！

4. （測試用）手動執行 Job 驗證邏輯

BEGIN
    DBMS_JOB.RUN(:job);
END;
/
-- 程序已成功完成

SELECT * FROM CJ_JT_NSRCPDMDY;
-- 預期僅剩 2 筆「啟用」記錄，「不啟用」已被刪除

5. 移除 Job（測試結束或正式下線時）

BEGIN
    DBMS_JOB.REMOVE(:job);
END;
/
-- 程序已成功完成

關鍵技術點解析

• 排程時間運算式：TRUNC(LAST_DAY(SYSDATE)+1) 精確取得「下個月第一天 00:00:00」，避免手算月份邊界錯誤。
• DBMS_JOB vs DBMS_SCHEDULER：10g 後建議改用功能更強的 DBMS_SCHEDULER，但 DBMS_JOB 相容性佳，舊版遷移專案仍常用。
• 提交機制：COMMIT 後排程才真正生效；程序內亦需 COMMIT 確保刪除持久化。

常見誤區提醒

原文第 6 步驟註解提到「查出來 3 條記錄才對，可是已經把不啟用的記錄提前刪除啦」，這正是因為在第 4 步建立 Job 後，緊接著執行了第 5 步 DBMS_JOB.RUN 導致立即執行。生產環境建立排程後請直接跳過手動執行步驟，靜待排程時間到達。