Oracle 動態 SQL 完整開發指南：原理、實戰與效能優化

動態 SQL 在執行階段組裝並執行 SQL 字串，解決靜態 SQL 無法應對的「查詢條件不確定、DDL 執行、物件名稱參數化」等場景。核心機制為晚期綁定，透過 EXECUTE IMMEDIATE 或動態游標實現。

核心語法

EXECUTE IMMEDIATE dynamic_sql_string
  [USING bind_arg_list]
  [RETURNING INTO out_arg_list];

• USING：綁定輸入參數（對應 SQL 中的 :1, :2...）。
• RETURNING INTO：接收 DML 的 RETURNING 子句或單行查詢結果。

三大經典場景實戰

1. 執行 DDL（建表、授權等）

CREATE OR REPLACE PROCEDURE create_table AS
BEGIN
  EXECUTE IMMEDIATE '
    CREATE TABLE emp(
      id NUMBER,
      name VARCHAR2(10),
      salary NUMBER
    )';
  -- DDL 隱含提交，無需 COMMIT
END;

2. 單行參數化查詢

CREATE OR REPLACE PROCEDURE find_info(p_id NUMBER) AS
  v_name VARCHAR2(10);
  v_sal  NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT name, salary FROM emp WHERE id = :1'
  USING p_id
  RETURNING INTO v_name, v_sal;
  DBMS_OUTPUT.PUT_LINE(v_name || ' 薪資：' || v_sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('查無資料');
END;

3. 多行動態游標

CREATE OR REPLACE PROCEDURE find_emp(p_sal NUMBER) AS
  TYPE rc IS REF CURSOR;
  c rc;
  r_emp emp%ROWTYPE;
BEGIN
  OPEN c FOR 'SELECT * FROM emp WHERE salary > :1' USING p_sal;
  LOOP
    FETCH c INTO r_emp;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID:' || r_emp.id || ' Name:' || r_emp.name);
  END LOOP;
  CLOSE c;
END;

效能優化三大鐵律

避坑清單

• 動態 SQL 無法直接引用 PL/SQL 區域變數，必須透過 USING 綁定。
• EXECUTE IMMEDIATE 內的 SQL 字串不可包含分號（PL/SQL 區塊除外）。
• 防止 SQL 注入：嚴禁直接拼接使用者輸入，全程使用綁定變數。

動態 SQL 雖以效能換取彈性，善用上述技巧即可將效能損耗壓縮至最低。