萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 存儲過程動態 SQL 實戰:EXECUTE IMMEDIATE 解決動態表名問題

問題場景

開發人員常遇到需根據變數動態決定表名、欄位名的需求。PL/SQL 靜態 SQL 不支援識別子(表名、欄位名)繫結,必須使用 動態 SQL (EXECUTE IMMEDIATE) 方能達成。

錯誤示範與正確寫法對比

❌ 錯誤寫法:直接使用變數作為表名

CREATE OR REPLACE PROCEDURE sysringall_test AS
  v_tmp_name VARCHAR2(30);
  v_name     VARCHAR2(30);
BEGIN
  v_tmp_name := 'ring_all_old_' || TO_CHAR(SYSDATE, 'yyyymm') || '_tmp';
  v_name     := 'ring_all_old_' || TO_CHAR(SYSDATE, 'yyyymm');
  
  -- 編譯錯誤:PL/SQL 不允許變數作為表識別子
  INSERT INTO v_tmp_name SELECT * FROM v_name;
  COMMIT;
END;

錯誤訊息: PLS-00103: Encountered the symbol "V_TMP_NAME" when expecting one of the following: ...

✅ 正確寫法:EXECUTE IMMEDIATE 動態執行

CREATE OR REPLACE PROCEDURE sysringall_test AS
  v_tmp_name  VARCHAR2(30);
  v_name      VARCHAR2(30);
  v_sql       VARCHAR2(2000);
BEGIN
  v_tmp_name := 'ring_all_old_' || TO_CHAR(SYSDATE, 'yyyymm') || '_tmp';
  v_name     := 'ring_all_old_' || TO_CHAR(SYSDATE, 'yyyymm');
  
  -- 構建動態 SQL 字串
  v_sql := 'INSERT INTO ' || v_tmp_name || ' SELECT * FROM ' || v_name;
  
  -- 執行動態 SQL
  EXECUTE IMMEDIATE v_sql;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    -- 記錄錯誤日誌:SQLCODE, SQLERRM, v_sql
    RAISE;
END;

動態 SQL 進階用法

1. 使用繫結變數 (USING 子句)

動態 SQL 中的 資料值 應使用繫結變數,而非字串拼接:

v_sql := 'UPDATE employees SET salary = salary * :pct WHERE department_id = :did';
EXECUTE IMMEDIATE v_sql USING 1.1, 60;

優點:效能更好(共享游標)、防 SQL 注入、避免日期/數字格式轉換問題。

2. 返回單一值 (INTO 子句)

DECLARE
  v_cnt NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name
    INTO v_cnt;
END;

3. 返回多行 (OPEN FOR ... USING)

DECLARE
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR 'SELECT * FROM ' || v_table_name || ' WHERE id = :id' USING v_id;
  -- 傳回呼叫端或迴圈處理
END;

4. DDL 動態執行

EXECUTE IMMEDIATE 'CREATE TABLE ' || v_tbl || ' (id NUMBER, name VARCHAR2(50))';
EXECUTE IMMEDIATE 'DROP TABLE ' || v_tbl || ' PURGE';

注意:DDL 會隱含提交,不可回滾。

安全性防護:防止 SQL 注入

  • 識別子 (表名、欄位名) 必須經 DBMS_ASSERT.ENQUOTE_NAME 或 DBMS_ASSERT.SIMPLE_SQL_NAME 驗證;
  • 建立白名單機制,僅允許預定義物件名稱;
  • 資料值一律使用 USING 繫結,嚴禁字串拼接。

效能考量

  • 動態 SQL 無法享有靜態 SQL 的編譯期檢查與依賴追蹤;
  • 頻繁執行相同動態 SQL 會產生大量游標,建議使用 DBMS_SQL 套件複用游標或改用靜態 SQL 重構;
  • 12c+ 可使用 DBMS_SQL.RETURN_RESULT 向用戶端隱式返回結果集。

除錯技巧

-- 開啟 SQL 追蹤
ALTER SESSION SET SQL_TRACE = TRUE;

-- 或輸出動態 SQL 供人工檢查
DBMS_OUTPUT.PUT_LINE('執行 SQL: ' || v_sql);

總結

EXECUTE IMMEDIATE 是 PL/SQL 動態 SQL 的核心指令,掌握「識別子拼接、資料值繫結、結果返回」三大模式,並嚴守安全防護原則,即可靈活應對動態表名、分區維護、結構變更等進階需求。

copyright © 萬盛學電腦網 all rights reserved