DB2 存儲過程開發最佳實踐：從參數校驗到異常處理的工程化指南

前言：為什麼需要存儲過程工程化標準

DB2 SQL PL (SQL Procedural Language) 提供強大的過程化能力，但新手易陷入「能跑通即上線」誤區。本文提煉六大最佳實踐，覆蓋參數聲明、輸入校驗、游標語義、異常體系、臨時表治理、非法對象修復，助你編寫生產級高可用存儲過程。

最佳實踐 1：CREATE PROCEDURE 完整聲明運行時特性

核心參數對照表：

實戰原則： 默認 MODIFIES SQL DATA 開銷最大，純查詢務必聲明 READS SQL DATA，無 SQL 操作聲明 NO SQL。

最佳實踐 2：輸入參數「零信任」校驗與標準化預處理

COALESCE 統一空值處理模式

-- 字符型：NULL → 空串，並去尾部空格
SET p_name = RTRIM(COALESCE(p_name, ''));

-- 數值型：NULL → 0
SET p_rank = COALESCE(p_rank, 0);

-- 必填檢查：業務主鍵非空非零
IF p_id IS NULL OR p_name = '' OR p_age IS NULL THEN
    SET p_status = 34100; -- 自定義錯誤碼
    RETURN p_status;
END IF;

參數校驗決策表

最佳實踐 3：游標返回類型精準聲明 — CALLER vs CLIENT

CREATE PROCEDURE get_people(IN p_age INT)
DYNAMIC RESULT SETS 2
READS SQL DATA
LANGUAGE SQL
BEGIN
    -- 返回給直接調用者 (可能是另一個 SP)
    DECLARE c_caller CURSOR WITH RETURN TO CALLER FOR
        SELECT name, age FROM person WHERE age < p_age;

    -- 返回給最頂層客戶端應用 (JDBC/ODBC/CLI)
    DECLARE c_client CURSOR WITH RETURN TO CLIENT FOR
        SELECT name, age FROM person WHERE age > p_age;

    OPEN c_caller; OPEN c_client;
END

語義差異圖解

• TO CALLER： 嵌套調用鏈中，結果集返回給直接父調用者 (SP Y → SP Z，結果給 Y)
• TO CLIENT： 穿透所有嵌套層，直接返回給發起 CALL 的應用程序 (最常用，默認行為)

避坑： 混用導致結果集「消失」或「錯位」，調試極難。統一規範：除非明確需要父 SP 消費，一律 TO CLIENT。

最佳實踐 4：構建分層異常處理體系

處理器類型選擇

預定義條件組

• NOT FOUND : SQLCODE +100 / SQLSTATE 02000 (查無數據)
• SQLEXCEPTION : SQLCODE < 0 (錯誤)
• SQLWARNING : SQLCODE > 0 且 ≠ +100 (警告)

企業級通用異常處理器模板

-- 輸出參數：p_status (0=成功, 負值=SQLCODE, 正值=業務錯誤碼)
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN NOT ATOMIC
    DECLARE v_sqlcode INT; DECLARE v_sqlstate CHAR(5);
    SELECT SQLCODE, SQLSTATE INTO v_sqlcode, v_sqlstate FROM SYSIBM.SYSDUMMY1;
    
    CASE v_sqlstate
        WHEN '02000' THEN SET p_status = 5000; -- 未找到
        WHEN '42724' THEN SET p_status = 3;    -- 對象已存在
        ELSE
            IF v_sqlcode < 0 THEN SET p_status = v_sqlcode; END IF;
    END CASE;
    
    -- 可選：寫入審計表
    -- INSERT INTO sp_audit_log (sp_name, sqlcode, sqlstate, ts) VALUES (...);
END;

自定義條件實現「內部函數」復用

-- 聲明自定義條件 (SQLSTATE 80xxx 保留給用戶)
DECLARE reset_ctx CONDITION FOR SQLSTATE '80100';

-- 處理器：重置上下文變量
DECLARE CONTINUE HANDLER FOR reset_ctx
BEGIN NOT ATOMIC
    SET v_sqlcode = 0; SET v_sqlstate = '00000'; SET p_status = 0;
END;

-- 業務流程中隨時重置
SIGNAL reset_ctx;
-- ... 繼續執行 ...

最佳實踐 5：臨時表治理 — 生命週期、並發、內存三維管控

適用場景判斷樹

1. 中間結果複用且計算昂貴 → ✅ 用臨時表
2. 多次返回相同結果集 → ✅ 用臨時表
3. 跨會話共享數據 → ❌ 用永久表 + 定時清理
4. 單次簡單查詢 → ❌ 直接 CTE/子查詢

聲明模板：會話隔離、提交刪除、無日誌

DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_emp (
    id INT, name VARCHAR(30), dept_id INT
) ON COMMIT DELETE ROWS NOT LOGGED IN user_tbsp WITH REPLACE;

• SESSION. 明確 Schema，避免歧義
• ON COMMIT DELETE ROWS 事務提交自動清空，保留結構
• NOT LOGGED 避免日誌爭用
• WITH REPLACE 同會話重複調用不報錯 (關鍵！)

「結果集綁定臨時表」的陷阱與對策

現象： SP 返回基於臨時表的游標 → 會話保持 → 臨時表不會隱式 DROP → 二次調用報「對象已存在」。

對策三選一：

1. 聲明加 WITH REPLACE (推薦，最簡單)
2. 客戶端讀完結果集後顯式 DROP TABLE SESSION.temp_xxx
3. 連接池場景：SP 開頭強制 DROP TABLE IF EXISTS SESSION.temp_xxx

內存壓力紅線

臨時表駐留內存 (或臨時表空間)。經驗閾值： 單表 > 10 萬行且高並發 (>50) 時，必須評估臨時表空間大小與內存壓力，必要時改用永久暫存表 + 分批處理。

最佳實踐 6：非法存儲過程自動發現與重綁定

失效根因

• 依賴表結構變更 (ALTER TABLE ADD/DROP COLUMN)
• 依賴視圖/函數/觸發器重建
• 父 SP 重編譯導致調用鏈失效

一鍵掃描腳本

SELECT RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS sp_name,
       '(' || RTRIM(r.routineschema) || '.P' || SUBSTR(CHAR(r.lib_id+10000000),2) || ')' AS pkg_name
FROM SYSCAT.ROUTINES r
WHERE r.routinetype = 'P'
  AND ((r.origin = 'Q' AND r.valid != 'Y')
       OR EXISTS (
           SELECT 1 FROM SYSCAT.PACKAGES p
           WHERE p.pkgschema = r.routineschema
             AND p.pkgname = 'P' || SUBSTR(CHAR(r.lib_id+10000000),2)
             AND p.valid != 'Y'
       ))
ORDER BY sp_name;

批量重綁定

-- 單個
REBIND PACKAGE TEST.P3550884 RESOLVE ANY;

-- 批量 (Shell 腳本循環)
for pkg in $(db2 -x "SELECT 'P'||SUBSTR(CHAR(lib_id+10000000),2) FROM SYSCAT.ROUTINES WHERE valid!='Y'"); do
    db2 "REBIND PACKAGE $pkg RESOLVE ANY"
done

前置條件： 所有依賴對象 (表/視圖/函數) 必須已 VALID，否則 REBIND 仍失敗。

同理可查非法視圖

SELECT RTRIM(viewschema) || '.' || RTRIM(viewname) AS view_name
FROM SYSCAT.VIEWS WHERE valid = 'X' ORDER BY view_name;

結語

存儲過程是業務邏輯的「最後一道防線」，工程化標準不僅關乎性能，更關乎可維護性與故障可恢復性。建議將六大實踐編碼為團隊 Code Snippet / SonarQube 規則 / CI 檢查門禁，讓最佳實踐成為肌肉記憶而非文檔陳列。