Oracle動態SQL深度解析：EXECUTE IMMEDIATE與性能優化實戰

靜態SQL vs 動態SQL

靜態SQL：編譯時確定語句結構（早期綁定），性能優、安全性高。
動態SQL：運行時構建語句（晚期綁定），靈活性強但需額外解析開銷。

EXECUTE IMMEDIATE核心語法

EXECUTE IMMEDIATE dynamic_sql_string
    [USING bind_arguments]
    [RETURNING INTO out_arguments];

• USING：綁定輸入參數（對應佔位符:1, :2...）
• RETURNING INTO：接收DML返回值或單行查詢結果
• 動態SQL適用於DDL、帶變量的DML、未知表名/列名的查詢

三大典型應用場景實戰

場景一：動態建表與數據初始化

CREATE OR REPLACE PROCEDURE create_table AS
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE emp(
        id NUMBER, name VARCHAR2(10), salary NUMBER)';
    EXECUTE IMMEDIATE 'INSERT INTO emp VALUES (100, ''jacky'', 5600)';
    -- 更多插入語句...
END;

場景二：參數化單行查詢

CREATE OR REPLACE PROCEDURE find_info(p_id NUMBER) AS
    v_name VARCHAR2(10); v_salary NUMBER;
BEGIN
    EXECUTE IMMEDIATE 
        'SELECT name, salary FROM emp WHERE id = :1'
    USING p_id
    RETURNING INTO v_name, v_salary;
    DBMS_OUTPUT.PUT_LINE(v_name || '的收入為：' || v_salary);
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('找不到相應數據');
END;

場景三：動態游標多行查詢

CREATE OR REPLACE PROCEDURE find_emp(p_salary NUMBER) AS
    r_emp emp%ROWTYPE;
    TYPE c_type IS REF CURSOR;
    c1 c_type;
BEGIN
    OPEN c1 FOR 
        'SELECT * FROM emp WHERE salary > :1' USING p_salary;
    LOOP
        FETCH c1 INTO r_emp;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || r_emp.id || ' 姓名: ' || r_emp.name);
    END LOOP;
    CLOSE c1;
END;

性能優化三大技巧

技巧一：SQL語句模板化促進共享池復用

保持SQL結構一致，僅通過綁定變量傳遞不同值，讓Oracle復用解析樹和執行計劃。

技巧二：批量綁定替代循環執行

-- 低效：循環執行
FOR i IN v_id.FIRST..v_id.LAST LOOP
    EXECUTE IMMEDIATE 'UPDATE emp SET salary = salary*1.2 WHERE id = :1' 
    USING v_id(i);
END LOOP;

-- 高效：FORALL批量綁定
FORALL i IN v_id.FIRST..v_id.LAST
    EXECUTE IMMEDIATE 'UPDATE emp SET salary = salary*1.2 WHERE id = :1' 
    USING v_id(i);

適用場景：循環中執行INSERT/UPDATE/DELETE引用集合元素時。

技巧三：NOCOPY減少大對象傳遞開銷

CREATE OR REPLACE PROCEDURE test(p_object IN NOCOPY large_type) AS ...

將OUT/IN OUT參數從值傳遞改為引用傳遞，顯著降低大對象/集合複製成本。

動態SQL最佳實踐總結

• 優先考慮靜態SQL，僅在必要時使用動態SQL
• 嚴格使用綁定變量防止SQL注入
• 動態DDL隱式提交，注意事務邊界
• 大批量操作必須使用FORALL+BULK COLLECT
• 監控V$SQL檢查動態SQL解析開銷