承接上篇語法導航，本文聚焦 SQL PL (SQL Procedural Language) 實戰開發核心：如何回傳結果集、控制流程語句、變數宣告與賦值、條件/迴圈/游標/異常處理完整語法與範例。適合作為寫 Procedure/Function/Trigger 時的桌邊速查卡。
DECLARE clientCur CURSOR WITH RETURN TO CALLER FOR SELECT * FROM staff;
OPEN clientCur;
-- 此處不寫 CLOSE clientCur
-- 一般變數
DECLARE sql-var-name data-type [DEFAULT default-value];
-- 條件/異常名稱（對應 SQLSTATE）
DECLARE condition-name CONDITION FOR SQLSTATE 'value';
-- 純量子查詢（必須只回傳 1 行 1 欄）
SET var = (SELECT salary FROM employee WHERE empno = 5);
-- 直接給值
SET var = 10000;
SET var = NULL;
SET var = DEFAULT; -- 使用宣告時的 DEFAULT 值
-- 特殊暫存器
SET userid = USER;
SET today = CURRENT DATE;
-- 多變數同時賦值
SET pay = 10000, bonus = 1500;
SET (pay, bonus) = (10000, 1500);
SET (pay, bonus) = (SELECT pay, bonus FROM employee WHERE empno = 5);
-- 1. 簡單 IF
IF (salary BETWEEN 10000 AND 90000) THEN
-- statements
END IF;
-- 2. IF-ELSE
IF (deptno IN ('A00','B01')) THEN
-- statements
ELSE
-- statements
END IF;
-- 3. IF-ELSEIF-ELSE
IF (EXISTS (SELECT * FROM employee)) THEN
-- statements
ELSEIF (SELECT COUNT(*) FROM employee) > 0 THEN
-- statements
ELSE
-- statements
END IF;
亮點：條件式中可直接使用 SQL 運算子（`BETWEEN`、`IN`、`EXISTS`、子查詢比較）。
-- WHILE 迴圈（可選標籤）
label_loop: WHILE condition DO
-- SQL PL statements
END WHILE label_loop;
-- FOR 迴圈：專為遍歷查詢結果集設計（隱含游標）
label_for: FOR row_name AS SELECT statement DO
-- 直接用 row_name.col_name 存取欄位
SET total_bonus = total_bonus + row_name.bonus;
END FOR label_for;
FOR 迴圈範例：
FOR emp AS SELECT * FROM employee WHERE bonus > 1000 DO
SET total_bonus = total_bonus + emp.bonus;
END FOR;
ITERATE label：跳回標籤對應迴圈開頭（類似 `continue`）。LEAVE label：跳離標籤對應迴圈/區塊（類似 `break`）。
check_bonus: FOR emp AS SELECT * FROM employee DO
IF emp.bonus > 10000 THEN
SET total_bonus = total_bonus + emp.bonus;
ELSE
ITERATE check_bonus; -- 跳過本輪剩餘邏輯，直接下一筆
END IF;
END FOR check_bonus;
-- 自訂 SQLSTATE（建議 7,8,9 或 I-Z 開頭）
SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = '薪資超出合理範圍';
-- 使用預先宣告的 CONDITION
DECLARE invalid_salary CONDITION FOR SQLSTATE '75001';
SIGNAL invalid_salary SET MESSAGE_TEXT = '薪資超出合理範圍';
-- 區塊級異常處理器
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 記錄錯誤、設定輸出參數預設值
SET out_err_code = SQLCODE;
SET out_err_msg = '程序內部錯誤';
END;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET medianSalary = 6666; -- 找不到資料給預設值
僅限 SQL PL 觸發器或複合語句（非 Function）使用，取得前一條 `INSERT/UPDATE/DELETE` 影響列數。
GET DIAGNOSTICS v_rowcount = ROW_COUNT;
CREATE PROCEDURE CALC_DEPT_BONUS
(
IN p_deptno CHAR(3),
OUT p_total DECIMAL(15,2),
OUT p_cnt INTEGER,
OUT p_err_code INTEGER,
OUT p_err_msg VARCHAR(200)
)
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
BEGIN
DECLARE v_bonus DECIMAL(15,2);
DECLARE v_sqlcode INTEGER DEFAULT 0;
-- 異常處理器
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
v_sqlcode = DB2_RETURNED_SQLCODE,
p_err_msg = MESSAGE_TEXT;
SET p_err_code = v_sqlcode;
SET p_total = 0;
SET p_cnt = 0;
END;
SET p_total = 0;
SET p_cnt = 0;
FOR emp AS
SELECT bonus FROM employee WHERE workdept = p_deptno AND bonus IS NOT NULL
DO
SET p_total = p_total + emp.bonus;
SET p_cnt = p_cnt + 1;
END FOR;
SET p_err_code = 0;
SET p_err_msg = 'SUCCESS';
END
| 任務 | 關鍵語法 | 注意事項 |
|---|---|---|
| 回傳結果集 | DECLARE ... CURSOR WITH RETURN TO CALLER / OPEN / 不 CLOSE | DYNAMIC RESULT SETS >= 1 |
| 宣告變數 | DECLARE v_name TYPE DEFAULT val; | 支援所有 SQL 型別，含 ROW/ARRAY (新版) |
| 賦值 | SET v = (SELECT ...); / SET (a,b) = ... | 子查詢必須單列單行 |
| 條件判斷 | IF ... THEN ... [ELSEIF ...] [ELSE ...] END IF | 條件可含 SQL 運算子/子查詢 |
| 遍歷查詢結果 | FOR row AS SELECT ... DO ... END FOR | 最簡潔，隱含游標管理 |
| 條件迴圈 | WHILE cond DO ... END WHILE | 需自行管理游標或計數器 |
| 跳過本輪 | ITERATE label | 需在 FOR/WHILE 區塊內 |
| 跳出迴圈 | LEAVE label | 需標籤 |
| 拋出自訂錯誤 | SIGNAL SQLSTATE '...' SET MESSAGE_TEXT = '...' | SQLSTATE 7,8,9,I-Z 開頭 |
| 捕捉異常 | DECLARE EXIT/CONTINUE HANDLER FOR SQLEXCEPTION/SQLSTATE/CONDITION | EXIT=離開區塊, CONTINUE=繼續下一句 |
| 取得影響列數 | GET DIAGNOSTICS v = ROW_COUNT | 僅限 Trigger/複合語句 |
核心心法：SQL PL = SQL 的語意表達力 + 程式語言的控制流。掌握
FOR迴圈遍歷結果集、HANDLER統一異常處理、WITH RETURN回傳結果集三大核心模式，即可應付 95% 預存程序/觸發器開發場景。把這張速查卡貼在螢幕邊，寫 Code 時查閱，效率倍增。
