Oracle 動態 SQL 高效能開發指南：EXECUTE IMMEDIATE、REF CURSOR 與三大優化技巧

靜態 SQL vs 動態 SQL：編譯時機差異

• 靜態 SQL：前期繫結，編譯期即確定 SQL 文本，效能最佳。
• 動態 SQL：後期繫結，執行期才建立 SQL（如 WHERE 條件由使用者輸入決定），犧牲效能換取彈性。

動態 SQL 核心語法

EXECUTE IMMEDIATE dynamic_sql_string
  [USING bind_argument_list]
  [RETURNING INTO out_argument_list];

• USING：輸入參數綁定（對應 SQL 中 :1, :2... 佔位符）。
• RETURNING INTO：DML 執行後回傳值（如 RETURNING salary INTO v_sal）。
• 查詢多筆資料需搭配 REF CURSOR：OPEN cursor FOR dynamic_sql USING params;

完整範例：EMP 表 CRUD 操作

1. 建立資料表與測試資料 (DDL 動態 SQL)

CREATE OR REPLACE PROCEDURE create_emp_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);
  COMMIT;
END;
/
EXEC create_emp_table;

2. 依 ID 查詢單筆 (動態查詢 + RETURNING INTO)

CREATE OR REPLACE PROCEDURE find_emp_by_id (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 NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('查無此員工');
END;
/
EXEC find_emp_by_id(101);

3. 依薪資區間查詢多筆 (REF CURSOR)

CREATE OR REPLACE PROCEDURE find_emp_by_sal (p_sal NUMBER) AS
  TYPE emp_refcur IS REF CURSOR;
  c_emp   emp_refcur;
  r_emp   emp%ROWTYPE;
BEGIN
  OPEN c_emp FOR
    'SELECT * FROM emp WHERE salary > :1' USING p_sal;
  LOOP
    FETCH c_emp INTO r_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID:' || r_emp.id || ' 姓名:' || r_emp.name || ' 薪資:' || r_emp.salary);
  END LOOP;
  CLOSE c_emp;
END;
/
EXEC find_emp_by_sal(4000);

三大高效能開發技巧

技巧一：SQL 文本標準化，善用共享游標

動態 SQL 文本若完全相同（含大小寫、空白），Oracle 可在 Shared Pool 重用解析樹。建議：

• 固定欄位順序、表別名、縮排風格。
• 使用佔位符而非字串串接常數值。

技巧二：集合操作改用 FORALL 批次繫結

逐筆 EXECUTE IMMEDIATE 在迴圈中會造成大量 Context Switch。改用 FORALL 將整個集合一次傳給 SQL 引擎：

-- 逐筆 (慢)
FOR i IN v_ids.FIRST .. v_ids.LAST LOOP
  EXECUTE IMMEDIATE 'UPDATE emp SET salary = salary * 1.1 WHERE id = :1' USING v_ids(i);
END LOOP;

-- 批次 (快)
FORALL i IN v_ids.FIRST .. v_ids.LAST
  EXECUTE IMMEDIATE 'UPDATE emp SET salary = salary * 1.1 WHERE id = :1' USING v_ids(i);

同理，查詢回傳集合使用 BULK COLLECT INTO。

技巧三：大型 OUT/IN OUT 參數加上 NOCOPY

預設 OUT 參數採值傳遞，大型物件/集合複製成本高。加上 NOCOPY 改為參考傳遞：

CREATE OR REPLACE PROCEDURE process_big_data (p_data IN OUT NOCOPY big_object_type) AS
BEGIN
  -- 直接操作記憶體原物件，無複製開銷
  p_data.attribute := 'modified';
END;

注意：若主程式例外中止，NOCOPY 參數可能處於部分修改狀態，需自行處理一致性。

測試環境

• Server: Unix + Oracle 9.2
• Client: Windows 2000 Pro + TOAD

動態 SQL 是強大工具，善用上述技巧可在保有彈性的前提下，將效能損耗降至最低。