三大主流數據庫存儲過程動態 SQL 寫法對比：Oracle、SQL Server、DB2 完整示例

三大主流數據庫存儲過程動態 SQL 寫法對比：Oracle、SQL Server、DB2 完整示例

動態 SQL 允許在運行時構建並執行 SQL 字符串，是實現通用查詢、動態表名、可選條件構建的核心技術。本文對比 Oracle (EXECUTE IMMEDIATE)、SQL Server (sp_executesql)、DB2 (PREPARE/EXECUTE) 三種語法差異，提供**帶參數綁定、帶返回游標、純執行**三種場景的標準模板代碼。

一、Oracle (PL/SQL) - EXECUTE IMMEDIATE

1. 基本執行 + 參數綁定 + INTO 變量

CREATE OR REPLACE PROCEDURE dyn_sql_demo AS
  v_sql   VARCHAR2(2000);
  v_a     VARCHAR2(20);
  v_b     VARCHAR2(20);
  v_c     VARCHAR2(20) := 'f';
  v_d     VARCHAR2(20) := 'g';
BEGIN
  v_sql := 'SELECT MAX(a), MAX(b) FROM t1 WHERE c = :b1 OR c = :b2';
  EXECUTE IMMEDIATE v_sql INTO v_a, v_b USING v_c, v_d;
  -- 無 INTO 則無需 USING
END;
/

2. 返回游標 (SYS_REFCURSOR)

CREATE OR REPLACE PROCEDURE dyn_cursor (p_cursor OUT SYS_REFCURSOR) AS
  v_sql VARCHAR2(2000);
  v_c   VARCHAR2(20) := 'f';
  v_d   VARCHAR2(20) := 'g';
BEGIN
  v_sql := 'SELECT * FROM t1 WHERE c = :b1 OR c = :b2';
  OPEN p_cursor FOR v_sql USING v_c, v_d;
END;
/

關鍵點：佔位符用 :b1, :b2 或 :name；USING 子句按位置綁定；游標由調用方 FETCH 關閉。

二、SQL Server (T-SQL) - sp_executesql

1. 基本執行 + 輸出參數

CREATE PROCEDURE dyn_sql_demo AS
  DECLARE @sql   NVARCHAR(2000); -- 必須 NVARCHAR
  DECLARE @a     VARCHAR(20), @b VARCHAR(20);
  DECLARE @c     VARCHAR(20) = 'f', @d VARCHAR(20) = 'g';
BEGIN
  SET @sql = N'SELECT @outA = MAX(a), @outB = MAX(b) FROM t1 WHERE c = @inC OR c = @inD';
  EXECUTE sp_executesql @sql
    , N'@outA VARCHAR(20) OUT, @outB VARCHAR(20) OUT, @inC VARCHAR(20), @inD VARCHAR(20)' -- 參數定義 (單行!)
    , @a OUT, @b OUT, @c, @d; -- 參數值按定義順序
END
GO

2. 返回結果集 (無需特殊語法)

SET @sql = N'SELECT * FROM t1 WHERE c = @inC OR c = @inD';
EXECUTE sp_executesql @sql, N'@inC VARCHAR(20), @inD VARCHAR(20)', @c, @d;
-- 直接返回結果集給調用者

關鍵點：@sql 必須 NVARCHAR；參數定義字符串 **必須單行寫完**；輸出參數在定義和傳值處均標 OUT；EXECUTE 後不能拼接字符串。

三、DB2 (SQL PL) - PREPARE / EXECUTE / CURSOR

1. 基本執行 + 參數綁定 + 游標讀取

CREATE PROCEDURE dyn_sql_demo (IN v_c VARCHAR(20))
BEGIN
  DECLARE v_sql  VARCHAR(2000);
  DECLARE v_a, v_b, v_d VARCHAR(20);
  DECLARE c1 CURSOR FOR s1; -- s1 為 prepared statement 名
  SET v_c = 'f'; SET v_d = 'g';
  SET v_sql = 'SELECT MAX(a), MAX(b) FROM t1 WHERE c = ? OR c = ?'; -- 佔位符用 ?
  PREPARE s1 FROM v_sql;
  OPEN c1 USING v_c, v_d; -- 綁定參數
  FETCH c1 INTO v_a, v_b;
  CLOSE c1;
END

2. 返回游標 (DYNAMIC RESULT SETS)

CREATE PROCEDURE dyn_cursor (IN v_c VARCHAR(20))
DYNAMIC RESULT SETS 1 LANGUAGE SQL
BEGIN
  DECLARE v_sql VARCHAR(2000);
  DECLARE v_d VARCHAR(20);
  DECLARE c1 CURSOR WITH RETURN FOR s1; -- WITH RETURN 關鍵字
  SET v_c = 'f'; SET v_d = 'g';
  SET v_sql = 'SELECT * FROM t1 WHERE c = ? OR c = ?';
  PREPARE s1 FROM v_sql;
  OPEN c1 USING v_c, v_d; -- 打開即返回結果集
END

關鍵點：佔位符統一用 ?；PREPARE stmt FROM sql_string；游標聲明引用 stmt；OPEN ... USING 綁定；返回游標需在 CREATE PROCEDURE 聲明 DYNAMIC RESULT SETS 1 且游標用 WITH RETURN。

四、核心差異總結表

五、最佳實踐建議

1. 優先靜態 SQL：動態 SQL 難調試、難優化、有注入風險 (拼接時)。僅在表名、列名、可選條件動態時使用。
2. 嚴禁字符串拼接參數：必須使用參數綁定 (Binding)，防注入、利用計劃緩存。
3. 類型匹配：綁定變量類型與 SQL 列類型嚴格一致，避免隱式轉換導致索引失效。
4. 游標關閉：Oracle/DB2 顯式 CLOSE；SQL Server 隨批次結束自動釋放。
5. 權限最小化：動態 SQL 執行權限遵循定義者權限 (Oracle/DB2) 或調用者權限 (SQL Server)，謹慎授予。