Oracle EXECUTE IMMEDIATE 動態 SQL 綁定變數完整範例

Oracle EXECUTE IMMEDIATE 動態 SQL 綁定變數完整範例

EXECUTE IMMEDIATE 是 PL/SQL 執行動態 SQL 的核心指令，支援 USING 子句綁定輸入/輸出變數，有效防止 SQL 注入並提升解析效能。本文提供標準語法結構與實戰範例。

1. 語法結構

EXECUTE IMMEDIATE dynamic_sql_string
  [INTO {define_variable[, ...] | record}]
  [USING [{IN | OUT | IN OUT} bind_argument[, ...]]];

2. 實戰範例：動態呼叫函數並綁定多參數

DECLARE
  aa        NUMBER(15,2);
  l_cnt     VARCHAR2(200);
  i_yr      INT := 11;
  i_curCode VARCHAR2(100) := '001';
  i_vDate   DATE := SYSDATE;
  i_valExpr VARCHAR2(100) := 'haha';
  i_branchNo VARCHAR2(100) := '00';
BEGIN
  -- 動態組裝 SELECT 語句，呼叫自訂函數 Ns_Gls_AutoVal
  l_cnt := 'Ns_Gls_AutoVal(132,:1,:2,:3,:4)';

  EXECUTE IMMEDIATE 'SELECT ' || l_cnt || ' FROM dual'
    INTO aa
    USING i_curCode, i_vDate, i_valExpr, i_branchNo;

  DBMS_OUTPUT.PUT_LINE('函數返回值: ' || aa);
END;
/

3. 關鍵點解析

4. 進階用法提示

• IN/OUT/IN OUT 模式：
  USING IN v_in, OUT v_out, IN OUT v_inout 適用於動態呼叫有輸出參數的程序。
• 動態 DML 返回影響行數：
  EXECUTE IMMEDIATE 'DELETE FROM t WHERE id=:1' USING v_id RETURNING ROWCOUNT INTO v_cnt; (12c+ 支援)
• 批量綁定 (FORALL)：大量資料操作時搭配 BULK COLLECT、FORALL 使用 EXECUTE IMMEDIATE 可大幅提升效能。

5. 常見錯誤與避坑

1. 佔位符重複使用：同一 SQL 中相同佔位符需在 USING 中重複傳入對應變數。
2. 字串拼接注入風險：嚴禁直接拼接使用者輸入字串進 SQL 文本，務必使用佔位符綁定。
3. 型別不匹配：DATE 型別綁定時不可轉為字串，直接傳入 DATE 變數。

熟練掌握 EXECUTE IMMEDIATE ... USING 綁定模式，是編寫安全、高效動態 PL/SQL 的關鍵技能。