DB2 儲存程序動態 SQL 實作：EXECUTE IMMEDIATE 與動態查詢的進階技巧

動態 DML：DELETE 範例 (可行)

使用 EXECUTE IMMEDIATE 執行動態組裝的 DELETE 陳述式：

CREATE PROCEDURE testProced(OUT out_name1 INTEGER)
LANGUAGE SQL
P1: BEGIN
    DECLARE MySQL VARCHAR(200);
    DECLARE SCORE_tmp VARCHAR(100);
    SET SCORE_tmp = 'SCORE';
    SET MySQL = 'DELETE FROM T_USER WHERE ' || SCORE_tmp || ' = 2';
    EXECUTE IMMEDIATE MySQL;
    COMMIT;
END P1

關鍵點：雙管線 || 串接字串，字面值需單引號包裹，EXECUTE IMMEDIATE 直接執行動態 SQL 字串。

動態查詢挑戰：SELECT INTO 變數

需求：SELECT <動態欄位> INTO <動態變數> FROM <動態表>，欄位、表名、目標變數皆為執行期決定。

限制：SQL PL 的 EXECUTE IMMEDIATE 不支援直接 SELECT INTO 宿主變數，且游標宣告需靜態 SQL 文本。

解決方案：動態游標 + PREPARE

CREATE PROCEDURE dyn_select(
    IN  p_col     VARCHAR(128),   -- 欄位名
    IN  p_table   VARCHAR(128),   -- 表名
    OUT p_result  VARCHAR(200)    -- 結果 (假設單值字串)
)
LANGUAGE SQL
BEGIN
    DECLARE stmt STATEMENT;
    DECLARE c1 CURSOR FOR stmt;
    DECLARE v_sql VARCHAR(500);

    -- 1. 組裝查詢 (注意防 SQL 注入，生產環境需白名單驗證)
    SET v_sql = 'SELECT ' || p_col || ' FROM ' || p_table || ' FETCH FIRST 1 ROW ONLY';

    -- 2. 準備陳述式
    PREPARE stmt FROM v_sql;

    -- 3. 開啟游標並取值
    OPEN c1;
    FETCH c1 INTO p_result;
    CLOSE c1;

    -- 4. 處理 NOT FOUND
    IF p_result IS NULL THEN
        SET p_result = 'NO_DATA';
    END IF;
END

進階：多欄位/多列動態結果集回傳給呼叫端

若需回傳完整結果集給 Java/CLI 呼叫端，使用 DYNAMIC RESULT SETS + WITH RETURN TO CALLER：

CREATE PROCEDURE dyn_result_set(
    IN p_table VARCHAR(128),
    IN p_where VARCHAR(200)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
        SELECT * FROM TABLE(p_table) WHERE p_where;  -- 此處為示意，實際需 PREPARE

    -- 正確做法：PREPARE 後開啟游標
    DECLARE stmt STATEMENT;
    DECLARE v_sql VARCHAR(500);
    SET v_sql = 'SELECT * FROM ' || p_table || ' WHERE ' || p_where;
    PREPARE stmt FROM v_sql;
    DECLARE c1 CURSOR WITH RETURN TO CALLER FOR stmt;
    OPEN c1;
    -- 結果集自動回傳
END

安全性警示：防範 SQL 注入

• 動態拼接表名、欄位名時，嚴禁直接信任外部輸入。
• 實作白名單驗證：IF p_table NOT IN ('T1','T2','T3') THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table'; END IF;
• 欄位名同理，僅允許預定義清單內的識別碼。

總結

1. 動態 DML (INSERT/UPDATE/DELETE) 直接用 EXECUTE IMMEDIATE。
2. 動態查詢單值：PREPARE + DECLARE CURSOR + FETCH INTO。
3. 動態結果集回傳：DYNAMIC RESULT SETS + WITH RETURN TO CALLER + PREPARE 陳述式。
4. 務必實施識別碼白名單驗證，杜絕 SQL 注入風險。

掌握這套模式，即能在 DB2 儲存程序中靈活實現各類動態 SQL 需求，同時維護安全與效能。