EXECUTE IMMEDIATE 動態 SQL 完全指南：語法、綁定、游標與效能優化

為什麼用 EXECUTE IMMEDIATE？

• 取代 DBMS_SQL 繁複 API，代碼量減少 70%+。
• 原生支援綁定變量，軟解析效能接近靜態 SQL。
• 支援 DDL、DML、匿名區塊、呼叫預存程序。

核心語法

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

關鍵規則：

1. 動態 SQL 字串不以分號結尾；PL/SQL 區塊必須以分號結尾。
2. DML 不自動提交，需顯式 COMMIT；DDL 隱式提交前後交易。
3. 不支援多行查詢直接 INTO，需用 BULK COLLECT INTO 集合或 SYS_REFCURSOR。

七大實戰場景範例

1. 執行 DDL（如切換角色、建表）

BEGIN
    EXECUTE IMMEDIATE 'SET ROLE ALL';
END;

2. 綁定變數執行 DML（USING 子句）

DECLARE
    v_deptname VARCHAR2(20) := 'TESTING';
    v_loc      VARCHAR2(10) := 'DUBAI';
BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO dept VALUES (:1, :2, :3)'
        USING 50, v_deptname, v_loc;
    COMMIT;  -- 必須顯式提交
END;

3. 單行查詢綁定讀取（INTO 子句）

DECLARE
    v_cnt VARCHAR2(20);
BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM emp' INTO v_cnt;
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_cnt);
END;

4. 動態呼叫預存程序（IN/OUT/IN OUT 模式）

DECLARE
    v_proc   VARCHAR2(100) := 'pkg_util.get_rowcnt';
    v_tbl    VARCHAR2(30) := 'EMP';
    v_cnt    NUMBER;
    v_status VARCHAR2(200);
BEGIN
    EXECUTE IMMEDIATE 'BEGIN ' || v_proc || '(:1, :2, :3); END;'
        USING IN v_tbl, OUT v_cnt, IN OUT v_status;
    IF v_status != 'OK' THEN DBMS_OUTPUT.PUT_LINE('Error: ' || v_status); END IF;
END;

5. 查詢結果綁定至 RECORD 類型

DECLARE
    TYPE emp_rec IS RECORD (
        empno NUMBER(4), ename VARCHAR2(20), deptno NUMBER(2)
    );
    v_emp emp_rec;
BEGIN
    EXECUTE IMMEDIATE 'SELECT empno, ename, deptno FROM emp WHERE empno = 7934'
        INTO v_emp;
    DBMS_OUTPUT.PUT_LINE(v_emp.ename);
END;

6. 同時綁定輸入與輸出（INTO 在 USING 前）

DECLARE
    v_deptno NUMBER := 20;
    v_dname  VARCHAR2(20);
    v_loc    VARCHAR2(20);
BEGIN
    EXECUTE IMMEDIATE 'SELECT dname, loc FROM dept WHERE deptno = :1'
        INTO v_dname, v_loc
        USING v_deptno;
END;

7. 多行查詢：BULK COLLECT INTO 集合（高效能）

DECLARE
    TYPE emp_tab IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
    v_emps emp_tab;
    v_sal  NUMBER := 2000;
BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE sal > :1'
        BULK COLLECT INTO v_emps
        USING v_sal;
    FOR i IN 1..v_emps.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_emps(i).ename);
    END LOOP;
END;

或開啟 SYS_REFCURSOR 返回給呼叫端處理。

效能與安全最佳實踐

• 永遠使用綁定變量（:1, :2...），避免字串拼接導致硬解析與 SQL 注入。
• DBMS_ASSERT 套件驗證動態物件名稱（ENQUOTE_NAME, SCHEMA_NAME）。
• 大量 DML 用 FORALL 批次綁定，而非迴圈 EXECUTE IMMEDIATE。
• 異常處理務必覆蓋 WHEN OTHERS 並記錄 SQLERRM、DBMS_UTILITY.FORMAT_ERROR_BACKTRACE。
• 12c+ 可用 DBMS_SQL.RETURN_RESULT 直接向客戶端返回隱式游標結果集。

總結

EXECUTE IMMEDIATE 是 PL/SQL 動態 SQL 的首選工具。掌握 INTO/USING/BULK COLLECT 三大子句組合，配合綁定變量與集合，即可優雅解決 99% 動態需求。