您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 存儲過程定時執行兩大方案實戰：Shell+Crontab 與 DBMS_JOB/DBMS_SCHEDULER

編輯：Oracle 資料庫基礎

方案一：OS 層調度 — Shell 腳本 + Crontab (跨平台、零依賴)

腳本模板 `exec_proc.sh`

#!/bin/bash
# 環境變量 (務必與 oracle 用戶 .bash_profile 一致)
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORACLE_SID=ORCL
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

# 日志文件
LOG_DIR=/home/oracle/logs
mkdir -p $LOG_DIR
LOG_FILE=$LOG_DIR/exec_proc_$(date +%Y%m%d).log

# 執行邏輯 (支援動態參數)
sqlplus -s /nolog <> $LOG_FILE 2>&1
CONNECT / AS SYSDBA
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINES 200 PAGES 1000
DECLARE
  v_code VARCHAR2(100);
  v_msg  VARCHAR2(4000);
  v_err  VARCHAR2(4000);
  v_ym   VARCHAR2(6) := TO_CHAR(SYSDATE, 'YYYYMM');
  v_ymd  VARCHAR2(8) := TO_CHAR(SYSDATE, 'YYYYMMDD');
BEGIN
  -- 業務邏輯示例: 月初 1-4 處理上月數據，其餘處理本月
  IF TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) BETWEEN 1 AND 4 THEN
    v_ym := TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM');
  END IF;
  
  MY_SCHEMA.MY_PACKAGE.MY_PROC(v_ym, v_ymd, v_code, v_msg, v_err);
  
  DBMS_OUTPUT.PUT_LINE('CODE=' || v_code || ', MSG=' || v_msg);
  IF v_code != '0' THEN
    RAISE_APPLICATION_ERROR(-20001, 'PROC FAILED: ' || v_err);
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('EXCEPTION: ' || SQLERRM);
    RAISE;
END;
/
EXIT SQL.SQLCODE
EOF

# 返回碼傳遞給 cron
exit $?

Crontab 部署

# 編輯 oracle 用戶 crontab
crontab -e

# 每天 22:30 執行，日志追加
30 22 * * * /home/oracle/scripts/exec_proc.sh >> /home/oracle/logs/cron_exec_proc.log 2>&1

# 時間格式: 分 時 日 月 周
# 示例: 每月 1 號 01:00 執行
# 0 1 1 * * /home/oracle/scripts/exec_proc.sh ...

優缺點

優點	缺點
完全獨立於數據庫狀態，DB 掛掉不影響調度器	需管理 OS 用戶、環境變量、密碼文件安全
可調用任意外部程序 (預處理、後處理、通知)	無法感知事務邊界，跨庫協調困難
成熟穩定，所有 Unix/Linux 原生支持	集群環境需配合分佈式鎖防止重複執行

方案二：數據庫內部調度 — DBMS_SCHEDULER (10g+ 推薦) / DBMS_JOB (舊版兼容)

DBMS_SCHEDULER 現代寫法 (企業級首選)

BEGIN
  -- 1. 創建調度鏈 (可選，用於複雜依賴)
  DBMS_SCHEDULER.CREATE_CHAIN(chain_name => 'MY_DAILY_CHAIN', rule_set_name => NULL, evaluation_interval => NULL, comments => 'Daily batch chain');

  -- 2. 定義程序對象
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name        => 'PROG_MY_PROC',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'MY_SCHEMA.MY_PACKAGE.MY_PROC',
    number_of_arguments => 5,
    enabled             => FALSE,
    comments            => 'Core business procedure'
  );
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT('PROG_MY_PROC', 1, 'V_YYMM', 'VARCHAR2', 'YYYYMM');
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT('PROG_MY_PROC', 2, 'V_YYMMDD', 'VARCHAR2', 'YYYYMMDD');
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT('PROG_MY_PROC', 3, 'V_CODE', 'VARCHAR2', 'OUT');
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT('PROG_MY_PROC', 4, 'V_MSG', 'VARCHAR2', 'OUT');
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT('PROG_MY_PROC', 5, 'V_ERR', 'VARCHAR2', 'OUT');
  DBMS_SCHEDULER.ENABLE('PROG_MY_PROC');

  -- 3. 創建作業
  DBMS_SCHEDULER.CREATE_JOB(
    job_name            => 'JOB_MY_DAILY',
    program_name        => 'PROG_MY_PROC',
    schedule_name       => NULL,  -- 使用 inline schedule
    start_date          => SYSTIMESTAMP,
    repeat_interval     => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=30; BYSECOND=0',  -- 每天 22:30
    end_date            => NULL,
    job_class           => 'DEFAULT_JOB_CLASS',
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Daily execution of MY_PROC'
  );

  -- 4. 設置作業參數 (動態計算)
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('JOB_MY_DAILY', 1, TO_CHAR(SYSDATE, 'YYYYMM'));
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('JOB_MY_DAILY', 2, TO_CHAR(SYSDATE, 'YYYYMMDD'));
  -- OUT 參數無需預設

  -- 5. 啟用
  DBMS_SCHEDULER.ENABLE('JOB_MY_DAILY');
  
  COMMIT;
END;
/

關鍵優勢

事務一體性：作業運行在數據庫 Session 內，自動參與事務管理。
高可用感知：RAC 環境下自動故障轉移 (service affinity)、負載均衡 (instance_stickiness)。
豐富日志與監控：DBA_SCHEDULER_JOB_RUN_DETAILS、DBA_SCHEDULER_JOB_LOG 完整記錄執行歷史、耗時、錯誤堆棧。
事件驅動：支持文件到達、隊列消息、自定義事件觸發，突破時間調度限制。
資源管控：關聯 Resource Manager Consumer Group，限制 CPU/IO 佔用。

DBMS_JOB 兼容寫法 (僅供 9i/10g 老系統維護)


VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job       => :jobno,
    what      => 'MY_SCHEMA.MY_PACKAGE.MY_PROC(TO_CHAR(SYSDATE,''YYYYMM''), TO_CHAR(SYSDATE,''YYYYMMDD''), :code, :msg, :err);',
    next_date => SYSDATE,
    interval  => 'TRUNC(SYSDATE+1) + 22/24 + 30/1440'  -- 每天 22:30
  );
  COMMIT;
END;
/
PRINT jobno;
-- 查看: SELECT * FROM dba_jobs WHERE job = :jobno;
-- 禁用: EXEC DBMS_JOB.BROKEN(:jobno, TRUE);
-- 刪除: EXEC DBMS_JOB.REMOVE(:jobno);

警告：DBMS_JOB 不支持 RAC 親和性、無日志表、間隔表達式易出錯，**新系統嚴禁使用**。

選型決策矩陣

場景	推薦方案	核心理由
純數據庫內部邏輯、需事務保證、RAC 環境	DBMS_SCHEDULER	原生 HA、事務一體、可視化監控
需調用 OS 命令、跨庫異構、舊版 9i/無 SCHEDULER 權限	Shell + Crontab	靈活、零依賴、運維熟悉
混合場景 (DB 邏輯 + 文件傳輸 + 郵件通知)	混合編排	SCHEDULER 執行 DB 部分 → 外部程序 (Shell/Python) 做周邊 → SCHEDULER 事件鏈串聯