開發人員常遇到需根據變數動態決定表名、欄位名的需求。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: ...
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 中的 資料值 應使用繫結變數,而非字串拼接:
v_sql := 'UPDATE employees SET salary = salary * :pct WHERE department_id = :did';
EXECUTE IMMEDIATE v_sql USING 1.1, 60;
優點:效能更好(共享游標)、防 SQL 注入、避免日期/數字格式轉換問題。
DECLARE
v_cnt NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name
INTO v_cnt;
END;
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR 'SELECT * FROM ' || v_table_name || ' WHERE id = :id' USING v_id;
-- 傳回呼叫端或迴圈處理
END;
EXECUTE IMMEDIATE 'CREATE TABLE ' || v_tbl || ' (id NUMBER, name VARCHAR2(50))';
EXECUTE IMMEDIATE 'DROP TABLE ' || v_tbl || ' PURGE';
注意:DDL 會隱含提交,不可回滾。
DBMS_ASSERT.ENQUOTE_NAME 或 DBMS_ASSERT.SIMPLE_SQL_NAME 驗證;USING 繫結,嚴禁字串拼接。DBMS_SQL 套件複用游標或改用靜態 SQL 重構;DBMS_SQL.RETURN_RESULT 向用戶端隱式返回結果集。-- 開啟 SQL 追蹤
ALTER SESSION SET SQL_TRACE = TRUE;
-- 或輸出動態 SQL 供人工檢查
DBMS_OUTPUT.PUT_LINE('執行 SQL: ' || v_sql);
EXECUTE IMMEDIATE 是 PL/SQL 動態 SQL 的核心指令,掌握「識別子拼接、資料值繫結、結果返回」三大模式,並嚴守安全防護原則,即可靈活應對動態表名、分區維護、結構變更等進階需求。