Oracle 11g 游標遍歷三大寫法詳解與 DBMS_SCHEDULER 定時作業實戰

Oracle 11g 游標遍歷三大寫法詳解與 DBMS_SCHEDULER 定時作業實戰

游標是 PL/SQL 處理多行查詢結果的核心機制。本文系統整理 **FOR LOOP**、**FETCH LOOP**、**WHILE LOOP** 三種遍歷模式的語法細節、優缺點對比，並實戰示範如何將游標邏輯封裝為預存程序、透過 DBMS_SCHEDULER 建立每日定時作業自動執行。

一、游標遍歷三大寫法深度對比

1. FOR LOOP —— 首選推薦（最簡潔、最安全、效能最佳）

CREATE OR REPLACE PROCEDURE prc_cursor_for_loop IS
    CURSOR c_emp IS SELECT empno, ename FROM emp;
BEGIN
    -- 無需 OPEN/CLOSE、無需宣告變數、自動 FETCH、自動結束
    FOR c_row IN c_emp LOOP
        DBMS_OUTPUT.PUT_LINE(c_row.empno || ' -- ' || c_row.ename);
    END LOOP;
END;
/

核心優勢：

• 隱式 OPEN/FETCH/CLOSE，杜絕忘記關閉游標導致記憶體洩漏
• 隱式宣告 c_row c_emp%ROWTYPE，型別自動對應游標投影欄位
• 變數作用域僅限迴圈內，不污染外部命名空間
• 隱式判斷 %NOTFOUND 結束迴圈，無需手寫 EXIT WHEN
• **內部優化**：Bulk Fetch 機制減少 Context Switch，大量資料時效能顯著優於手動 FETCH

2. FETCH LOOP —— 手動控制靈活，適合複雜邏輯

CREATE OR REPLACE PROCEDURE prc_cursor_fetch_loop IS
    CURSOR c_emp IS SELECT empno, ename FROM emp;
    c_row c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO c_row;
        EXIT WHEN c_emp%NOTFOUND;  -- 必須緊跟 FETCH 之後
        DBMS_OUTPUT.PUT_LINE(c_row.empno || ' ++ ' || c_row.ename);
        -- 此處可插入複雜業務邏輯、條件跳出、動態 SQL 等
    END LOOP;
    CLOSE c_emp;
END;
/

關鍵規則：

• EXIT WHEN c_emp%NOTFOUND **必須緊接在 FETCH 之後**，避免多處理一筆資料
• 業務邏輯放在 EXIT WHEN **之後**
• 迴圈結束**必須 CLOSE c_emp**，否則游標洩漏
• 適用場景：需在迴圈中動態開關其他游標、呼叫外部程序、異常處理需精細控制

3. WHILE LOOP —— 需預先 FETCH 一次，邏輯較繁瑣

CREATE OR REPLACE PROCEDURE prc_cursor_while_loop IS
    CURSOR c_emp IS SELECT empno, ename FROM emp;
    c_row c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    FETCH c_emp INTO c_row;  -- 必須先 FETCH 一次初始化 %FOUND
    WHILE c_emp%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(c_row.empno || ' ** ' || c_row.ename);
        FETCH c_emp INTO c_row;  -- 迴圈末尾 FETCH 供下輪判斷
    END LOOP;
    CLOSE c_emp;
END;
/

易錯點：

• 迴圈前**必須先 FETCH 一次**，否則 %FOUND 為 NULL 導致不進入迴圈
• 迴圈體內處理邏輯**必須在 FETCH 之前**
• 迴圈體末尾**必須 FETCH**，否則無限迴圈或漏處理最後一筆
• 不推薦常規使用，僅在極特殊流程控制需求下考慮

三種寫法效能與適用性總結

二、實戰：封裝 FOR LOOP 程序並建立每日定時作業

1. 業務預存程序：遍歷專案表逐筆呼叫報表產生程序

CREATE OR REPLACE PROCEDURE bis_quick_report IS
    -- 宣告游標：查詢所有專案 ID
    CURSOR cur_proids IS
        SELECT t.bis_project_id FROM bis_project t;

    -- 變數：年份、月份（去除前導零）
    v_year  VARCHAR2(4);
    v_month VARCHAR2(2);
BEGIN
    -- 取得當前年份
    SELECT TO_CHAR(SYSDATE, 'yyyy') INTO v_year FROM dual;

    -- 取得月份並去除前導零（'03' → '3'）
    SELECT CASE 
               WHEN SUBSTR(TO_CHAR(SYSDATE, 'mm'), 1, 1) = '0' 
               THEN SUBSTR(TO_CHAR(SYSDATE, 'mm'), 2, 1)
               ELSE TO_CHAR(SYSDATE, 'mm')
           END INTO v_month FROM dual;

    -- FOR LOOP 遍歷游標
    FOR cur_pids IN cur_proids LOOP
        -- 記錄執行日誌
        INSERT INTO zzz_test (id, name, create_time)
        VALUES (v_month, 'PKP_BIS_REPORT.buildQuickReport:' || cur_pids.bis_project_id, SYSDATE);
        COMMIT;

        -- 呼叫核心報表產生程序
        PKP_BIS_REPORT.buildQuickReport(
            cur_pids.bis_project_id, v_year, v_month, 
            NULL, NULL, NULL, NULL
        );
    END LOOP;
END bis_quick_report;
/

2. 建立 DBMS_SCHEDULER 定時作業（每日凌晨 3 點執行）

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JACK.BUILDQUICKREPORT',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'BIS_QUICK_REPORT',
        start_date      => TO_DATE('01-04-2015 03:00:00', 'dd-mm-yyyy hh24:mi:ss'),
        repeat_interval => 'FREQ=DAILY',          -- 每日執行
        end_date        => NULL,                  -- 無結束日期
        job_class       => 'DEFAULT_JOB_CLASS',   -- 標準作業類別
        enabled         => TRUE,                  -- 立即啟用
        auto_drop       => FALSE,                 -- 不自動刪除
        comments        => '每日自動產生專案快速報表'
    );
END;
/

關鍵參數說明：

• repeat_interval：支援 Calendaring Syntax，如 FREQ=HOURLY; INTERVAL=2（每 2 小時）、FREQ=WEEKLY; BYDAY=MON,WED,FRI（週一三五）
• job_class：建議使用 DEFAULT_JOB_CLASS，DBMS_JOB$ 為舊版相容類別
• auto_drop = FALSE：作業完成後保留定義，方便查詢歷史與重跑

3. PL/SQL Developer 中作業管理介面

建立成功後，在 PL/SQL Developer「作業」視窗可視化檢視：

• 作業名稱、狀態（啟用/停用）、下次執行時間、上次執行狀態
• 右鍵可「立即執行」、「停用」、「編輯」、「查看日誌」
• 日誌記錄每次執行的啟動時間、結束時間、錯誤訊息（如有）

三、進階技巧與最佳實踐

1. 大量資料游標優化：FOR c_row IN c_emp LOOP 內若執行 DML，考慮改用 BULK COLLECT ... FORALL 批次綁定，減少 Context Switch
2. 作業監控：查詢 DBA_SCHEDULER_JOB_RUN_DETAILS 追蹤執行歷史；設定 MAX_RUNS、MAX_FAILURES 限制異常重試
3. 時區處理：start_date 建議使用 TIMESTAMP WITH TIME ZONE 明確指定時區，避免夏令時/跨時區問題
4. 權限授予：執行 CREATE JOB 需 CREATE JOB 系統權限；跨 Schema 呼叫程序需 EXECUTE 權限
5. 舊版 DBMS_JOB 遷移：10g 以前用 DBMS_JOB.SUBMIT，11g+ 強烈建議遷移至 DBMS_SCHEDULER，功能更強、可視化管理更完善