DB2 SQL 預存程序語法全景圖：從 CREATE PROCEDURE 到實戰範例的完整導航

DB2 SQL 預存程序（`CREATE PROCEDURE (SQL)`）功能強大，但語法選項繁多，官方文件鐵軌圖讓人望而卻步。本文將完整語法拆解為 13 個核心子句，逐一說明語意、限制與最佳實務，並附上可直接執行的「中位數薪資查詢」實戰範例，做你開發時的隨身語法導航儀。

完整語法鐵軌圖（簡化版）

CREATE PROCEDURE procedure-name
    ( [ IN | OUT | INOUT parameter-name data-type [, ...] ] )
    [ SPECIFIC specific-name ]
    [ DYNAMIC RESULT SETS integer ]
    [ CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA ]
    [ DETERMINISTIC | NOT DETERMINISTIC ]
    [ CALLED ON NULL INPUT ]
    [ INHERIT SPECIAL REGISTERS ]
    [ OLD SAVEPOINT LEVEL | NEW SAVEPOINT LEVEL ]
    LANGUAGE SQL
    [ EXTERNAL ACTION | NO EXTERNAL ACTION ]
    [ PARAMETER CCSID ASCII | UNICODE ]
    SQL-procedure-body

13 大核心子句深度解析

實戰範例：回傳「薪資高於中位數員工」結果集

場景：Sample 資料庫 `STAFF` 表，輸出中位數薪資（OUT 參數）與高於中位數員工清單（結果集）。

CREATE PROCEDURE MEDIAN_RESULT_SET
(
    OUT medianSalary DOUBLE
)
RESULT SETS 1
LANGUAGE SQL
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
    -- 變數宣告
    DECLARE v_numRecords INT DEFAULT 1;
    DECLARE v_counter    INT DEFAULT 0;
    
    -- 游標 1：按薪資排序，用於算中位數
    DECLARE c1 CURSOR FOR
        SELECT CAST(salary AS DOUBLE)
        FROM staff
        ORDER BY salary;
    
    -- 游標 2：回傳結果集，WITH RETURN TO CALLER 關鍵字
    DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
        SELECT name, job, CAST(salary AS INTEGER)
        FROM staff
        WHERE salary > medianSalary
        ORDER BY salary;
    
    -- 例外處理：找不到資料時給預設值
    DECLARE EXIT HANDLER FOR NOT FOUND
        SET medianSalary = 6666;
    
    SET medianSalary = 0;
    SELECT COUNT(*) INTO v_numRecords FROM STAFF;
    
    OPEN c1;
    WHILE v_counter < (v_numRecords / 2 + 1) DO
        FETCH c1 INTO medianSalary;
        SET v_counter = v_counter + 1;
    END WHILE;
    CLOSE c1;
    
    OPEN c2;  -- 不關閉，直接回傳給呼叫端
END

關鍵實務提醒

• 結果集游標必須 `WITH RETURN TO CALLER` (或 `TO CLIENT`)，且 `OPEN` 後不可 CLOSE，引擎會自動傳遞給呼叫端。
• DYNAMIC RESULT SETS 數值必須 ≥ 實際開啟的 `WITH RETURN` 游標數。
• 變數命名建議加前綴 `v_`，游標加 `c_`，提升可讀性。
• 錯誤處理用 `DECLARE ... HANDLER FOR SQLEXCEPTION / NOT FOUND / SQLSTATE 'xxxxx'`。
• 部署後用 `CALL MEDIAN_RESULT_SET(?)` 測試，驗證 OUT 參數與結果集雙重回傳。

收藏價值：這 13 條子句涵蓋 99% 開發場景。下次寫預存程序時，對著這張表逐項勾選、填寫，即可產出語法正確、語意清晰、效能友善的 DB2 SQL Procedure。