深度解讀 DB2 預存程序：核心優勢、分類與開發精要

什麼是 DB2 預存程序？

預存程序 是一組預先編譯、儲存在資料庫伺服器端的 SQL 與流程控制語句集合，透過特定名稱調用。它不只是「一堆 SQL」，而是**封裝業務邏輯、減少網路交互、提升效能、強化安全**的核心資料庫程式設計單元。

四大核心優勢

1. 效能飛躍：建立時編譯優化，執行時**無需重複解析/優化**，大幅降低 CPU 開銷；複雜多步驟操作在伺服器端一次完成，消除客戶端-伺服器多次網路往返延遲。
2. 事務原子性：將跨多表的 Insert/Update/Delete/Query 封裝為單一單元，配合 COMMIT/ROLLBACK 保證 ACID，簡化應用端錯誤處理。
3. 程式碼重用與維護：邏輯集中在資料庫層，多應用 (Java, .NET, PHP, Cobol, CLP) 共享，修改邏輯無需重新部署客戶端。
4. 精細權限控制：授予 EXECUTE ON PROCEDURE 權限而非底層表 INSERT/UPDATE 權限，實現「最小權限原則」，防 SQL 注入。

三大分類 (依命名慣例區分)

註：SP_/XP_ 命名慣例源自 Sybase/SQL Server，DB2 官方不強制前綴，但建議遵循命名規範區分用途。

開發關鍵要素

1. 語言選擇：SQL PL vs 外部語言

• SQL PL (SQL Procedural Language)：DB2 原生，LANGUAGE SQL，無需編譯器，部署簡單，效能最佳，支援完整流程控制 (IF/WHILE/FOR/CASE)、動態 SQL、條件處理。首選。
• 外部語言 (C/C++, Java, COBOL, .NET, REXX)：LANGUAGE C/JAVA/COBOL，需編譯成 DLL/Shared Library/JAR，部署複雜，適用於極其複雜運算、調用外部庫、既有資產重用場景。

2. 參數模式

• IN：輸入參數
• OUT：輸出參數
• INOUT：雙向參數
• 支援預設值、陣列類型 (DB2 9.7+)

3. 動態 SQL 與結果集返回

CREATE PROCEDURE GET_EMP_BY_DEPT (IN p_dept CHAR(3), OUT p_count INTEGER)
LANGUAGE SQL
DYNAMIC RESULT SETS 1  -- 宣告返回 1 個結果集
BEGIN
  DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
    SELECT * FROM EMPLOYEE WHERE WORKDEPT = p_dept;
  
  OPEN c1;  -- 打開游標即返回結果集給調用者
  SET p_count = (SELECT COUNT(*) FROM EMPLOYEE WHERE WORKDEPT = p_dept);
END;

4. 條件處理與錯誤管理

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS CONDITION 1
    v_sqlstate = RETURNED_SQLSTATE,
    v_msg = MESSAGE_TEXT;
  INSERT INTO ERROR_LOG VALUES (v_sqlstate, v_msg, CURRENT TIMESTAMP);
  RESIGNAL; -- 重新拋出或自定義錯誤
END;

調用範例

• CLP：CALL PROC_NAME(?, ?)
• JDBC：CallableStatement cs = conn.prepareCall("{CALL PROC_NAME(?, ?)}");
• ODBC/CLI：SQLExecDirect(hstmt, "{CALL PROC_NAME(?, ?)}", SQL_NTS);

最佳實務建議

1. 盡量用 SQL PL，減少外部依賴，享受原生優化。
2. 控制邏輯複雜度：單一程序行數建議 < 500 行，超過則拆分模組化。
3. 謹慎使用動態 SQL：優先用靜態 SQL；動態 SQL 需 PREPARE/EXECUTE，注意參數標記防注入。
4. 結果集優於大量 OUT 參數：返回游標比幾十個 OUT 參數高效且靈活。
5. 版本管理：DDL 腳本納入版控，部署用 DROP/CREATE 或 ALTER PROCEDURE (DB2 支援替換)。
6. 監控效能：MON_GET_ROUTINE 表函數監控執行次數、耗時、排序溢出等。