Oracle 游標在預存程序中的完整應用範例：隱式/顯式游標、FOR LOOP、參數化與異常處理

游標分類與特性

完整實戰範例：編輯 Inlay 出庫單（含日誌、晶片狀態同步、異常回滾）

CREATE OR REPLACE PROCEDURE sp_edit_inlay_out(
    p_fid            IN NUMBER,        -- 主鍵 ID
    p_inlay_box_ids  IN VARCHAR2,      -- 箱ID清單，逗號分隔
    p_box_count      IN NUMBER,        -- 裝箱數
    p_apply_user_id  IN VARCHAR2,      -- 申請人
    p_store_user_id  IN VARCHAR2,      -- 庫管
    p_confirm_state  IN CHAR,          -- 確認狀態
    p_exist_state    IN CHAR,          -- 存在狀態
    p_err_msg        OUT VARCHAR2      -- 回傳錯誤訊息
) AS
    v_log_id     NUMBER;
    v_chip_id    NUMBER;
    v_now        DATE;
    v_sql        VARCHAR2(2000);

    -- 參數化游標：依父單 ID 找出所有晶片 ID
    CURSOR c_chips(p_inlay_out_id NUMBER) IS
        SELECT ch.f_id
        FROM t_chip ch
        JOIN t_inlay_box ib ON ch.f_inlay_box_id = ib.f_id
        WHERE ib.f_inlay_out_id = p_inlay_out_id;
BEGIN
    -- 1. 記錄異動日誌（快照舊值）
    INSERT INTO t_inlay_out_log(
        f_inlay_box_ids, f_boxcount, f_applyuserid, f_storeuserid,
        f_addtime, f_confirmstate, f_existstate,
        f_modifyid, f_modifytime, f_modifyuserid
    )
    SELECT f_inlay_box_ids, f_boxcount, f_applyuserid, f_storeuserid,
           f_addtime, f_confirmstate, f_existstate,
           p_fid, SYSDATE, p_store_user_id
    FROM t_inlay_out WHERE f_id = p_fid;

    -- 取得日誌序號
    SELECT seq_t_inlay_out_log.CURRVAL INTO v_log_id FROM dual;

    -- 2. 遍歷晶片游標：更新狀態、寫歷史
    OPEN c_chips(p_fid);
    LOOP
        FETCH c_chips INTO v_chip_id;
        EXIT WHEN c_chips%NOTFOUND;

        -- 取最早確認時間
        SELECT MIN(f_currenttime) INTO v_now
        FROM t_chipstatehistory
        WHERE f_historystate = 'Confirm_InlayIn' AND f_chipid = v_chip_id;

        -- 更新晶片狀態
        UPDATE t_chip
        SET f_state = 'Confirm_InlayIn', f_comparetime = v_now
        WHERE f_id = v_chip_id;

        -- 寫狀態歷史
        INSERT INTO t_chipstatehistory(f_chipid, f_historystate, f_tableid, f_currenttime, f_tablename)
        VALUES (v_chip_id, 'Confirm_InlayIn', v_log_id, SYSDATE, 'T_InlayOut_Log');
    END LOOP;
    CLOSE c_chips;

    -- 3. 更新主單與箱狀態（動態 SQL 處理 IN 清單）
    v_sql := 'UPDATE t_inlay_box SET f_state=3, f_inlay_out_id=' || p_fid ||
             ' WHERE f_id IN (' || p_inlay_box_ids || ')';
    EXECUTE IMMEDIATE v_sql;

    -- 4. 更新主單基本資料
    UPDATE t_inlay_out
    SET f_inlay_box_ids = p_inlay_box_ids,
        f_boxcount      = p_box_count,
        f_applyuserid   = p_apply_user_id,
        f_storeuserid   = p_store_user_id,
        f_confirmstate  = p_confirm_state,
        f_existstate    = p_exist_state,
        f_confirmtime   = NULL
    WHERE f_id = p_fid;

    -- 5. 更新晶片為新出庫狀態、寫歷史
    SELECT SYSDATE INTO v_now FROM dual;
    UPDATE t_chip
    SET f_state = 'No_Confirm_InlayOut', f_comparetime = v_now
    WHERE f_inlay_box_id IN (SELECT f_id FROM t_inlay_box WHERE f_inlay_out_id = p_fid);

    INSERT INTO t_chipstatehistory(f_chipid, f_historystate, f_tableid, f_currenttime, f_tablename)
    SELECT f_id, 'No_Confirm_InlayOut', v_log_id, v_now, 'T_InlayOut_Log'
    FROM t_chip
    WHERE f_inlay_box_id IN (SELECT f_id FROM t_inlay_box WHERE f_inlay_out_id = p_fid);

    COMMIT;
    p_err_msg := NULL;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        p_err_msg := SUBSTR(SQLERRM, 1, 200);
        -- 可選：寫入錯誤日誌表
        -- INSERT INTO err_log(proc_name, err_msg, err_time) VALUES ('SP_EDIT_INLAY_OUT', p_err_msg, SYSDATE);
END sp_edit_inlay_out;
/

關鍵最佳實踐

1. 游標參數化：避免硬編碼，提升重用性與安全性（防注入）。
2. FOR LOOP 簡化：若無需手動控制 FETCH，優先用 FOR r IN c_chips(p_fid) LOOP ... END LOOP; 自動開關。
3. BULK COLLECT + FORALL：大量資料時，改用集合批次提取與 DML，減少 Context Switch，效能提升 10x+。
4. 動態 SQL 謹慎用：EXECUTE IMMEDIATE 處理 IN 清單，注意注入風險，建議改用臨時表或集合傳遞。
5. 異常區塊務必 ROLLBACK：確保交易原子性，錯誤訊息截斷避免超長。
6. 游標用畢即 CLOSE：釋放 PGA 記憶體，避免 ORA-01000: maximum open cursors exceeded。

隱式與顯式游標屬性速查