DB2 SQL 預存程序採用標準 SQL/PSM 風格語法，支援輸入/輸出/輸入輸出參數、動態結果集、決定性宣告、隔離等級控制等進階特性。
CREATE PROCEDURE procedure-name
( [ parameter-declaration [, ...] ] )
[ SPECIFIC specific-name ]
[ DYNAMIC RESULT SETS integer ]
[ MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA ]
[ NOT DETERMINISTIC | 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 ]
[ CONCURRENT ACCESS RESOLUTION USE CURRENTLY COMMITTED | WAIT FOR OUTCOME ]
[ COMMIT ON RETURN YES | NO ]
[ AUTONOMOUS | NO AUTONOMOUS ]
routine-body
parameter-declaration ::=
[ IN | OUT | INOUT ] parameter-name data-type
IN：輸入參數（預設），呼叫端傳入值，程序內可讀不可寫回。OUT：輸出參數，程序內賦值，呼叫端接收結果。INOUT：輸入輸出參數，呼叫端傳入初始值，程序可修改並回傳。| 選項 | 說明 | 預設值 |
|---|---|---|
| SPECIFIC specific-name | 提供程序的唯一特定名稱，解決多載衝突 | 系統自動產生 |
| DYNAMIC RESULT SETS integer | 宣告程序可傳回的游標結果集數量（0 表示不傳回） | 0 |
| MODIFIES SQL DATA | 程序執行 INSERT/UPDATE/DELETE/DDL | -- |
| CONTAINS SQL | 程序包含 SQL 但不讀寫資料（如 SET 變數、流程控制） | -- |
| READS SQL DATA | 程序僅執行 SELECT，不修改資料 | -- |
| DETERMINISTIC | 相同輸入必然產生相同輸出，優化器可快取結果 | NOT DETERMINISTIC |
| CALLED ON NULL INPUT | 任一參數為 NULL 仍呼叫程序（需自行處理 NULL） | CALLED ON NULL INPUT |
| INHERIT SPECIAL REGISTERS | 繼承呼叫端的特殊暫存器（如 CURRENT DATE、CURRENT PATH） | INHERIT SPECIAL REGISTERS |
| OLD/NEW SAVEPOINT LEVEL | 儲存點層級隔離行為 | OLD SAVEPOINT LEVEL |
CREATE PROCEDURE GetEmployeeDetails (
IN p_dept_id SMALLINT,
OUT p_emp_count INTEGER,
OUT p_avg_sal DECIMAL(10,2)
)
SPECIFIC GetEmpDetails_v1
DYNAMIC RESULT SETS 1
READS SQL DATA
DETERMINISTIC
LANGUAGE SQL
BEGIN
-- 宣告游標對應動態結果集
DECLARE c_emp CURSOR WITH RETURN TO CLIENT FOR
SELECT emp_id, emp_name, salary, hire_date
FROM employees
WHERE dept_id = p_dept_id
ORDER BY emp_name;
-- 計算彙總值
SELECT COUNT(*), AVG(salary)
INTO p_emp_count, p_avg_sal
FROM employees
WHERE dept_id = p_dept_id;
-- 開啟游標傳回結果集
OPEN c_emp;
END
-- SQL 呼叫
CALL GetEmployeeDetails(10, ?, ?);
-- 應用程式 (JDBC/ODBC/CLI) 使用 CallableStatement
CallableStatement cs = conn.prepareCall("{CALL GetEmployeeDetails(?, ?, ?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2, Types.INTEGER);
cs.registerOutParameter(3, Types.DECIMAL);
cs.execute();
int count = cs.getInt(2);
BigDecimal avgSal = cs.getBigDecimal(3);
ResultSet rs = cs.getResultSet(); // 取得動態結果集
| 特性 | DB2 | SQL Server | Oracle |
|---|---|---|---|
| 語法標準 | SQL/PSM (ISO 標準) | T-SQL (專有) | PL/SQL (專有) |
| 動態結果集 | DYNAMIC RESULT SETS + CURSOR WITH RETURN | 直接 SELECT 回傳 | SYS_REFCURSOR OUT 參數 |
| 參數模式 | IN/OUT/INOUT 明確宣告 | OUTPUT 關鍵字 | IN/OUT/IN OUT |
| 決定性提示 | DETERMINISTIC 關鍵字 | 無直接對應 | DETERMINISTIC |
| 自主交易 | AUTONOMOUS | 無 | PRAGMA AUTONOMOUS_TRANSACTION |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ... 捕捉 SQL 錯誤。CREATE OR REPLACE PROCEDURE（DB2 9.7+ 支援）。掌握 DB2 SQL 預存程序的標準語法與進階選項，能編寫可移植、高效能、易維護的資料庫端業務邏輯。
