Oracle EXECUTE IMMEDIATE 動態 SQL 實戰技巧全解

Oracle EXECUTE IMMEDIATE 動態 SQL 實戰技巧全解

EXECUTE IMMEDIATE 取代舊版 DBMS_SQL 套件，提供更簡潔、高效的動態 SQL/PLSQL 執行機制。本文系統整理 7 大核心用法與關鍵注意事項。

核心優勢

• 語法簡潔，程式碼可讀性大幅提升
• 效能優於 DBMS_SQL（少量解析開銷）
• 支援 USING/INTO/RETURNING 子句靈活綁定
• 原生支援 DDL、DML、PL/SQL 區塊混合執行

關鍵限制與規則

1. DML 不自動提交：需顯式 COMMIT；DDL 隱含提交前序交易
2. 不支援多行查詢：單行查詢用 INTO；多行需用暫存表或 REF CURSOR
3. 分號規則：SQL 語句末尾**不加**分號；PL/SQL 區塊**必須加**分號
4. Forms 6i (PL/SQL 8.0.6.3) 不支援：需升級環境
5. 異常處理：動態 SQL 錯誤堆疊較複雜，務必包裹 EXCEPTION WHEN OTHERS

7 大實戰用法範例

1. 執行 DDL 語句

BEGIN
   EXECUTE IMMEDIATE 'SET ROLE ALL';
END;
/

2. 綁定變數傳入 (USING 子句)

DECLARE
   l_depnam VARCHAR2(20) := 'TESTING';
   l_loc    VARCHAR2(10) := 'DUBAI';
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO dept VALUES (:1, :2, :3)'
   USING 50, l_depnam, l_loc;
   COMMIT;
END;
/

位置標記法：:1, :2, :3 對應 USING 順序；亦可用命名標記 :b1, :b2。

3. 單行查詢取值 (INTO 子句)

DECLARE
   l_cnt VARCHAR2(20);
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM emp'
   INTO l_cnt;
   DBMS_OUTPUT.PUT_LINE(l_cnt);
END;
/

4. 動態呼叫預存程序 (IN/OUT/IN OUT 模式)

DECLARE
   l_routin  VARCHAR2(100) := 'gen2161.get_rowcnt';
   l_tblnam  VARCHAR2(20)  := 'emp';
   l_cnt     NUMBER;
   l_status  VARCHAR2(200);
BEGIN
   EXECUTE IMMEDIATE 'BEGIN ' || l_routin || '(:1, :2, :3); END;'
   USING IN l_tblnam, OUT l_cnt, IN OUT l_status;

   IF l_status != 'OK' THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: ' || l_status);
   END IF;
END;
/

注意：參數模式必須顯式指定（IN 預設，OUT/IN OUT 必須寫出）。

5. 查詢結果映射至 RECORD 型別

DECLARE
   TYPE empdtlrec IS RECORD (
      empno  NUMBER(4),
      ename  VARCHAR2(20),
      deptno NUMBER(2)
   );
   empdtl empdtlrec;
BEGIN
   EXECUTE IMMEDIATE 'SELECT empno, ename, deptno FROM emp WHERE empno = 7934'
   INTO empdtl;
   -- empdtl.empno, empdtl.ename, empdtl.deptno 可直接使用
END;
/

亦可使用 %ROWTYPE 簡化宣告。

6. 同時綁定輸入與輸出 (INTO + USING)

DECLARE
   l_dept  PLS_INTEGER := 20;
   l_dname VARCHAR2(20);
   l_loc   VARCHAR2(20);
BEGIN
   EXECUTE IMMEDIATE 'SELECT dname, loc FROM dept WHERE deptno = :1'
   INTO l_dname, l_loc
   USING l_dept;
END;
/

順序固定：INTO 子句在前，USING 子句在後。

7. 多行查詢變通方案：暫存表 + INSERT SELECT

DECLARE
   l_sal PLS_INTEGER := 2000;
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO temp_table (empno, ename) ' ||
                     'SELECT empno, ename FROM emp WHERE sal > :1'
   USING l_sal;
   COMMIT;
   -- 後續處理 temp_table 資料
END;
/

替代方案：使用 OPEN ref_cursor FOR dynamic_sql USING ... 實現真正的動態游標。

動態 SQL 防注入最佳實務

1. 永遠使用繫結變數（USING），**嚴禁**字串拼接使用者輸入
2. 物件名稱（表/欄/Schema）需動態時，使用 DBMS_ASSERT.ENQUOTE_NAME 驗證
3. 建立白名單驗證允許的物件名稱
4. 最小權限原則：執行動態 SQL 的使用者僅擁有必要物件權限

效能優化建議

• 重複執行相同動態 SQL 時，考慮 DBMS_SQL 游標快取或 CURSOR_SHARING
• 大量 DML 改用 FORALL + 集合綁定（需靜態 SQL）
• 避免在迴圈中重複 EXECUTE IMMEDIATE，組裝批次語句