DB2 SQL 存儲過程開發全攻略：從核心概念到 SQL PL 實戰範本

存儲過程是 DB2 伺服器端邏輯的核心載體，能將多條 SQL 封裝為單次網路調用、隔離資料邏輯與應用邏輯、返回多結果集。本文建立「概念→特性→SQL PL 語法→實戰範本」完整知識鏈，助力開發者快速掌握高效、安全的 SQL Procedure 開發。

核心概念澄析

• Routine（例程）：伺服器端可調用資料庫物件的統稱，含三類：Stored Procedure（SP）、`UDF (User-Defined Function)`、`Method (結構化類型行為)`。
• SP vs UDF：SP 由 `CALL` 調用、支援 `IN/OUT/INOUT` 參數、可返回多結果集、不可用於 SQL 表達式中；UDF 嵌入 SQL 表達式、返回單值/表、不可有副作用。
• 實現形式：SQL Routine（純 SQL PL，建議首選：安全、效能近 C++、部署簡單）vs External Routine（C/C++/Java/COBOL/OLE，需編譯部署、適用複雜運算/外部庫調用）。

SP 參數模式與結果集機制

結果集返回步驟（SQL SP 專用）：

1. `DECLARE c1 CURSOR WITH RETURN TO CALLER FOR SELECT ...;` （`TO CALLER` 供中間 Routine 獲取，`TO CLIENT` 直達端應用）
2. `OPEN c1;`
3. 不關閉游標直接 RETURN，引擎自動將結果集推給調用者。

需在 `CREATE PROCEDURE` 宣告 `DYNAMIC RESULT SETS n`（`n` ≥ 實際返回集數），否則超集觸發 `SQLCODE +464` 警告。

SQL PL 核心語法速查（支援於 Routine/Trigger/動態複合語句）

變量與條件聲明

DECLARE v_salary DECIMAL(9,2) DEFAULT 0;
DECLARE v_dept CHAR(3);
DECLARE my_condition CONDITION FOR SQLSTATE '23505'; -- 自定義異常名

賦值（SET）多樣形態

SET v_sal = (SELECT SALARY FROM EMP WHERE EMPNO = '000010'); -- 子查詢單值
SET v_sal = NULL;
SET v_sal = DEFAULT;
SET v_user = USER, v_today = CURRENT DATE; -- 多變量並行
SET (v_pay, v_bonus) = (SELECT SALARY, BONUS FROM EMP WHERE EMPNO = '000010');

流程控制

-- IF 三種形態
IF v_sal BETWEEN 10000 AND 90000 THEN ... END IF;
IF v_dept IN ('A00','B01') THEN ... ELSE ... END IF;
IF EXISTS (SELECT 1 FROM EMP) THEN ... ELSEIF ... END IF;

-- WHILE 迴圈（可選 LABEL）
lbl: WHILE v_cnt < 10 DO
  SET v_cnt = v_cnt + 1;
  IF v_cnt = 5 THEN ITERATE lbl; END IF; -- 回到 WHILE 頭
  IF v_cnt = 8 THEN LEAVE lbl; END IF;   -- 跳出迴圈
END WHILE lbl;

-- FOR 遊標迴圈（隱式 OPEN/FETCH/CLOSE）
FOR r_emp AS SELECT * FROM EMP WHERE BONUS > 1000 DO
  SET v_total_bonus = v_total_bonus + r_emp.BONUS;
END FOR;

異常處理

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS v_err_msg = MESSAGE_TEXT;
  SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'SP Error: ' || v_err_msg;
END;

診斷資訊

GET DIAGNOSTICS v_rows = ROW_COUNT; -- 取得前一條 INSERT/UPDATE/DELETE 影響行數

SQL PL 限制與最佳實踐

• 不可執行 DDL：`CREATE/DROP TABLE|INDEX|VIEW` 禁止在 SQL PL 中執行（可用動態 SQL `EXECUTE IMMEDIATE` 繞過，但慎用）。
• 原子性邊界：`BEGIN ATOMIC ... END` 區塊為不可分割事務單元；錯誤自動回滾。
• 效能原則：集合操作優於迴游標；參數化動態 SQL 配合 `REOPT ALWAYS` 解決參數窺探；大量寫入用 `MERGE` 或批次 `INSERT`。
• 版本管理：`CREATE OR REPLACE PROCEDURE`（9.7+）支援無縫熱替換，無需 `DROP` 再建、保留權限。

最小化可用範本：帶參數、異常處理、結果集、日誌記錄

CREATE OR REPLACE PROCEDURE SP_EMP_BONUS_STAT (
  IN  p_dept   CHAR(3),
  OUT p_total  DECIMAL(15,2),
  OUT p_cnt    INT
) DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC SP_EMP_BONUS_STAT
BEGIN
  DECLARE v_sqlstate CHAR(5) DEFAULT '00000';
  DECLARE v_msg      VARCHAR(32000);
  DECLARE c_emp CURSOR WITH RETURN TO CALLER FOR
    SELECT EMPNO, LASTNAME, BONUS FROM EMP WHERE WORKDEPT = p_dept AND BONUS > 0;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1 v_sqlstate = RETURNED_SQLSTATE, v_msg = MESSAGE_TEXT;
    SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'SP_EMP_BONUS_STAT failed: ' || v_msg;
  END;

  OPEN c_emp;

  SELECT SUM(BONUS), COUNT(*) INTO p_total, p_cnt
  FROM EMP WHERE WORKDEPT = p_dept AND BONUS > 0;

  -- 不 CLOSE c_emp，直接返回結果集
END @

結語：SQL Procedure 以「純 SQL 實現伺服器邏輯」實現了開發效率與執行效能的雙贏。掌握 `DECLARE/SET/IF/WHILE/FOR/ITERATE/LEAVE/SIGNAL/GET DIAGNOSTICS` 九大核心語句，配合「集合優先、參數化動態 SQL、原子性邊界清晰」三大原則，即可構建健壯、可維護、高效能的資料庫端服務層。