Oracle 動態 SQL 完整教學：EXECUTE IMMEDIATE、USING/INTO/RETURNING/BULK COLLECT 實戰

為什麼需要動態 SQL？

PL/SQL 靜態 SQL 編譯時即解析驗證，**不支援 DDL（CREATE/ALTER/DROP）**、不支援運行時才確定的表名/欄位/WHERE 條件。動態 SQL 在**執行期構建、解析、執行**字串，解決上述限制。

兩種動態 SQL 引擎

• Native Dynamic SQL (NDS)： EXECUTE IMMEDIATE，11g+ 內建優化，語法簡潔，主流選擇。
• DBMS_SQL 套件： 低階 API，支援未知欄位數/型別的極度動態場景，複雜度高，非必要不建議。

EXECUTE IMMEDIATE 核心語法

EXECUTE IMMEDIATE dynamic_sql_string
  [INTO define_variable [, ...]]
  [USING [IN | OUT | IN OUT] bind_argument [, ...]]
  [RETURNING INTO bind_argument [, ...]];

實戰場景與範例

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

EXECUTE IMMEDIATE 'CREATE TABLE tmp_log (id NUMBER, msg VARCHAR2(4000))';
EXECUTE IMMEDIATE 'TRUNCATE TABLE tmp_log';

2. 綁定變數 USING（防注入、提升解析重用）

PROCEDURE insert_student (p_id VARCHAR2, p_name VARCHAR2, p_sex VARCHAR2, p_age NUMBER) IS
  v_sql VARCHAR2(32767) := 'INSERT INTO jelly.tb_student VALUES (:1, :2, :3, :4)';
BEGIN
  EXECUTE IMMEDIATE v_sql USING p_id, p_name, p_sex, p_age;
  COMMIT;
END;

佔位符 :1、:2... 或命名 :id，USING 依序綁定。**綁定變數僅能用於值，不可用於表名/欄位名/型別**，DDL 動態物件名需用串接 ||。

3. 查詢單行：INTO 子句

PROCEDURE get_student (p_id VARCHAR2) IS
  v_sql VARCHAR2(32767) := 'SELECT id, name, sex, age FROM tb_student WHERE id = :id';
  v_id   VARCHAR2(50); v_name VARCHAR2(50); v_sex VARCHAR2(10); v_age NUMBER;
BEGIN
  EXECUTE IMMEDIATE v_sql INTO v_id, v_name, v_sex, v_age USING p_id;
  DBMS_OUTPUT.PUT_LINE(v_id||','||v_name);
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;

僅支援**單行**結果，多行拋 TOO_MANY_ROWS。

4. DML 返回值：RETURNING INTO

PROCEDURE update_age (p_id VARCHAR2, p_age NUMBER) IS
  v_sql VARCHAR2(32767) := 'UPDATE tb_student SET age = :age WHERE id = :id RETURNING id, name, sex INTO :rid, :rname, :rsex';
  v_id VARCHAR2(50); v_name VARCHAR2(50); v_sex VARCHAR2(10);
BEGIN
  EXECUTE IMMEDIATE v_sql USING p_age, p_id RETURNING INTO v_id, v_name, v_sex;
  COMMIT;
END;

• INSERT RETURNING：返回新增後的值（含序列生成的 PK）。
• UPDATE RETURNING：返回更新後的值。
• DELETE RETURNING：返回刪除前的值。

5. 多行查詢：BULK COLLECT INTO

PROCEDURE get_multi (p_id VARCHAR2) IS
  TYPE t_tab IS TABLE OF tb_student%ROWTYPE;
  v_arr t_tab;
  v_sql VARCHAR2(32767) := 'SELECT * FROM tb_student WHERE id = :id';
BEGIN
  EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_arr USING p_id;
  FOR i IN 1..v_arr.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_arr(i).id||','||v_arr(i).name);
  END LOOP;
END;

所有 INTO 變數必須為集合型別。適用於中小結果集（記憶體允許），大結果集改用游標 OPEN FOR ... USING 分批 FETCH ... BULK COLLECT LIMIT n。

6. 遊標變數動態開啟：OPEN FOR USING

TYPE rc_t IS REF CURSOR;
v_cur rc_t;
v_sql := 'SELECT * FROM tb_student WHERE id = :id';
OPEN v_cur FOR v_sql USING p_id;
LOOP FETCH v_cur INTO v_rec; EXIT WHEN v_cur%NOTFOUND; ... END LOOP;
CLOSE v_cur;

弱型別 REF CURSOR 可指向任意查詢，強型別 REF CURSOR RETURN type 限定結構。

效能與安全提醒

• 解析開銷： 動態 SQL 每次執行皆解析，高頻呼叫請善用綁定變數促進 Cursor Sharing。
• SQL 注入： **嚴禁** 直接串接使用者輸入字串，一律使用 USING 綁定。
• DDL 隱性提交： 執行 DDL 會隱性 COMMIT，謹慎用於事務中。

總結

動態 SQL 核心口訣：**DDL 必須動態、物件名動態必須動態、其餘優先靜態**。掌握 EXECUTE IMMEDIATE + USING/INTO/RETURNING/BULK COLLECT 四大子句，覆蓋 95% 動態需求，兼顧安全與效能。