兩大動態SQL引擎
| 特性 | EXECUTE IMMEDIATE | DBMS_SQL |
|---|
| 適用版本 | 8i+ (主流) | 7.3+ (舊版兼容) |
|---|
| 語法複雜度 | 簡單、直觀 | 繁瑣(OPEN/PARSE/BIND/EXECUTE/FETCH/CLOSE) |
|---|
| 性能 | 優(內部優化) | 略低 |
|---|
| 功能覆蓋 | 95%場景 | 100%(含描述未知列數/類型) |
|---|
| 推薦度 | ⭐⭐⭐⭐⭐ | ⭐ (維護舊代碼) |
|---|
場景一：DDL與DML動態執行
-- 匿名塊演示
BEGIN
-- DDL: 動態建表
EXECUTE IMMEDIATE 'DROP TABLE temp_1 PURGE';
EXECUTE IMMEDIATE 'CREATE TABLE temp_1(name VARCHAR2(8))';
-- DML: 參數化綁定 (防注入、計劃複用)
DECLARE
v_name VARCHAR2(8) := '測試人員';
v_addr VARCHAR2(10) := '北京';
v_sql VARCHAR2(200) := 'INSERT INTO temp_1(name, address) VALUES (:1, :2)';
BEGIN
EXECUTE IMMEDIATE v_sql USING v_name, v_addr;
COMMIT;
END;
END;
/
場景二：返回單行結果 (INTO子句)
DECLARE
v_sql VARCHAR2(200) := 'SELECT * FROM temp_1 WHERE name = :b1 AND ROWNUM = 1';
v_name VARCHAR2(8) := '測試人員';
v_rec temp_1%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE v_sql INTO v_rec USING v_name;
DBMS_OUTPUT.PUT_LINE('Found: ' || v_rec.name || ', ' || v_rec.address);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No matching row');
END;
/
場景三：返回結果集 (REF CURSOR + 包裝)
-- 1. 定義包規範 (弱類型 REF CURSOR 才能動態綁定)
CREATE OR REPLACE PACKAGE pkg_dyn AS
TYPE ref_cur IS REF CURSOR; -- 弱類型，無RETURN子句
FUNCTION get_data(p_id NUMBER) RETURN ref_cur;
END pkg_dyn;
/
-- 2. 包體實現
CREATE OR REPLACE PACKAGE BODY pkg_dyn AS
FUNCTION get_data(p_id NUMBER) RETURN ref_cur IS
v_rc ref_cur;
v_sql VARCHAR2(500);
BEGIN
IF p_id = 0 THEN
-- 靜態游標
OPEN v_rc FOR SELECT id, name, address FROM temp_1;
ELSE
-- 動態SQL游標
v_sql := 'SELECT id, name, address FROM temp_1 WHERE id = :b1';
OPEN v_rc FOR v_sql USING p_id;
END IF;
RETURN v_rc;
END get_data;
END pkg_dyn;
/
-- 3. 調用示例 (PL/SQL)
DECLARE
v_rc pkg_dyn.ref_cur;
v_id temp_1.id%TYPE;
v_name temp_1.name%TYPE;
v_addr temp_1.address%TYPE;
BEGIN
v_rc := pkg_dyn.get_data(1); -- 動態查詢
LOOP
FETCH v_rc INTO v_id, v_name, v_addr;
EXIT WHEN v_rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ':' || v_name || ':' || v_addr);
END LOOP;
CLOSE v_rc;
END;
/
關鍵最佳實踐
| 規則 | 說明 | 反例後果 |
|---|
| 綁定變量用 :1 :2 或 :b1 :b2 | 位置/命名綁定，防注入、共享游標 | 拼接字符串導致注入風險、硬解析風暴 |
| REF CURSOR必須弱類型 | TYPE t IS REF CURSOR; 無RETURN | 強類型游標無法OPEN FOR dynamic_sql |
| 動態SQL字符串不含終止分號 | 'SELECT ...' 而非 'SELECT ...;' | ORA-00911: invalid character |
| 單行查詢必須保證唯一行 | 加ROWNUM=1或主鍵條件 | TOO_MANY_ROWS異常 |
| 異常處理覆蓋 NO_DATA_FOUND | 單行查詢無結果時 | 未捕獲導致調用者報錯 |
進階：DBMS_SQL 描述未知結構 (極少用)
-- 僅當列數/類型完全未知時使用(如通用導出工具)
DECLARE
c INTEGER := DBMS_SQL.OPEN_CURSOR;
v_sql VARCHAR2(500) := 'SELECT * FROM ' || p_table;
col_cnt INTEGER;
desc_t DBMS_SQL.DESC_TAB;
BEGIN
DBMS_SQL.PARSE(c, v_sql, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, desc_t);
-- 根據desc_t動態DEFINE_COLUMN並FETCH
DBMS_SQL.CLOSE_CURSOR(c);
END;
架構決策：
• 99%動態SQL場景直接用 EXECUTE IMMEDIATE，代碼清晰、性能優、維護易。
• 僅在構建通用元數據工具(如自動導出/遷移/比對框架)時才考慮DBMS_SQL。
• 所有動態SQL嚴禁拼接業務參數，全量使用USING綁定變量。
• 批量DML優先FORALL + 集合綁定，而非循環EXECUTE IMMEDIATE。
• 動態DDL操作前後建議ALTER SYSTEM FLUSH SHARED_POOL避免字典緩存不一致。
