基於 Oracle 的高效能動態 SQL 程式開發：EXECUTE IMMEDIATE 與動態游標實戰技巧

靜態 vs 動態 SQL

• 靜態 SQL：編譯期確定 (Early Binding)，效能最佳
• 動態 SQL：執行期建構 (Late Binding)，靈活但犧牲部分效能

EXECUTE IMMEDIATE 語法

EXECUTE IMMEDIATE dynamic_sql_string
  [USING bind_arg_list] [RETURNING INTO out_arg_list];

• 動態 SQL = DDL + 含參數 DML
• USING 綁定輸入參數 (IN 模式)
• RETURNING INTO 接收輸出參數

三大實戰範例

1. 動態建表與插入 (DDL)

CREATE OR REPLACE PROCEDURE create_table AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE emp(id NUMBER, name VARCHAR2(10), salary NUMBER)';
  INSERT INTO emp VALUES (100,'Jacky',5600);
  INSERT INTO emp VALUES (101,'Rose',3000);
  INSERT INTO emp VALUES (102,'John',4500);
END;

2. 參數化查詢 (DML + RETURNING)

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;

3. 動態游標 (REF CURSOR)

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('薪水大於' || p_salary || '的員工：ID=' || r_emp.id || ' 姓名=' || r_emp.name);
  END LOOP;
  CLOSE c1;
END;

關鍵技巧總結

• 佔位符 :1, :2... 對應 USING 位置參數
• 動態游標開啟語法 OPEN cursor FOR dynamic_sql USING params
• 儘可能改寫為靜態 SQL；必須動態時，使用繫結變數而非字串拼接（防注入、利用共享游標）
• 大量動態 DML 考慮 FORALL + 集合 批次處理
• 謹慎使用 DBMS_SQL 套件處理極複雜動態場景 (DESCRIBE、動態欄位數)