Oracle 動態 SQL 完整實戰：EXECUTE IMMEDIATE 與 REF CURSOR 靈活查詢

當查詢條件、資料表名稱、欄位清單在執行期才能確定時，靜態 SQL 無法滿足需求。Oracle 提供 EXECUTE IMMEDIATE (單筆/多筆 DML/DDL) 與 REF CURSOR (動態游標) 兩大機制支援動態 SQL。本文詳細說明單行查詢 INTO 用法、多行查詢 OPEN-FOR/FETCH/CLOSE 完整流程，並提供可直接部署的 PL/SQL 範例程式碼。

動態 SQL 兩大適用場景

場景一：EXECUTE IMMEDIATE 單行查詢

DECLARE
  v_sql       VARCHAR2(200) := 'SELECT * FROM emp WHERE empno = :b1';
  v_empno     NUMBER := 7369;
  v_emp_rec   emp%ROWTYPE;
BEGIN
  EXECUTE IMMEDIATE v_sql
    INTO v_emp_rec
    USING v_empno;

  DBMS_OUTPUT.PUT_LINE('姓名：' || v_emp_rec.ename || '，職稱：' || v_emp_rec.job);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('查無資料');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('查詢回傳超過一筆，請改用 REF CURSOR');
END;
/

關鍵點：

• USING 綁定變數 (位置或名稱綁定)，防止 SQL 注入並利用共享游標。
• INTO 目標變數結構必須與 SELECT 清單完全對應 (建議用 %ROWTYPE 或 RECORD)。
• 若回傳 0 筆拋 NO_DATA_FOUND，超過 1 筆拋 TOO_MANY_ROWS。

場景二：REF CURSOR 多行動態查詢

四步驟標準流程

1. 定義游標型別與變數
2. OPEN FOR 動態 SQL 字串
3. LOOP FETCH 逐筆取值
4. CLOSE 釋放資源

DECLARE
  -- 1. 定義弱型別 REF CURSOR (可對應任意 SELECT 結構)
  TYPE emp_cur_t IS REF CURSOR;
  v_cur       emp_cur_t;

  -- 強型別範例 (需預先定義 RECORD 或對應 TABLE%ROWTYPE)
  -- TYPE emp_cur_t IS REF CURSOR RETURN emp%ROWTYPE;

  v_sql       VARCHAR2(200);
  v_deptno    NUMBER := 20;
  v_emp_rec   emp%ROWTYPE;
BEGIN
  -- 2. 組裝動態 SQL (使用綁定變數 :b1)
  v_sql := 'SELECT * FROM emp WHERE deptno = :b1';

  -- 3. 開啟游標並綁定參數
  OPEN v_cur FOR v_sql USING v_deptno;

  -- 4. 迴圈提取
  LOOP
    FETCH v_cur INTO v_emp_rec;
    EXIT WHEN v_cur%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('員工：' || v_emp_rec.ename || '，薪資：' || v_emp_rec.sal);
  END LOOP;

  -- 5. 關閉游標 (必要！否則會洩漏 PGA 記憶體)
  CLOSE v_cur;
EXCEPTION
  WHEN OTHERS THEN
    IF v_cur%ISOPEN THEN CLOSE v_cur; END IF;
    RAISE;
END;
/

進階實務技巧

1. 弱型別 vs 強型別 REF CURSOR

• 弱型別 (SYS_REFCURSOR)：TYPE t IS REF CURSOR; 或直接用 SYS_REFCURSOR，彈性最高，適用於回傳結構不固定的動態查詢。
• 強型別：TYPE t IS REF CURSOR RETURN emp%ROWTYPE; 編譯期檢查結構，適合固定介面的 API 設計。

2. 動態 WHERE 條件組裝最佳實務

v_sql := 'SELECT * FROM emp WHERE 1=1';
IF p_ename IS NOT NULL THEN
  v_sql := v_sql || ' AND ename LIKE :b' || v_bind_cnt;
  v_bind_cnt := v_bind_cnt + 1;
END IF;
-- 最後統一 USING 所有綁定值 (需用集合或動態 USING 技巧)

建議採用 DBMS_SQL 套件處理極度動態的欄位/綁定數量場景，或改用 XMLTABLE / JSON_TABLE (12c+) 將動態查詢轉為結構化資料。

3. 安全性：防禦 SQL 注入

• 嚴禁直接字串拼接使用者輸入：v_sql := '... WHERE name = ''' || p_name || '''' ❌
• 務必使用綁定變數：v_sql := '... WHERE name = :b1' USING p_name ✅
• 識別子 (表名、欄位名) 無法綁定，需經 DBMS_ASSERT.ENQUOTE_NAME 驗證後拼接。

4. 效能：游標共享與解析

• 動態 SQL 同樣受益於 CURSOR_SHARING 參數。
• 相同 SQL 文本 (含綁定變數佔位符) 會共用 Library Cache 中的父游標。
• 避免在迴圈中重複 EXECUTE IMMEDIATE 相同 SQL，應移至迴圈外準備一次。

常見錯誤排查

動態 SQL 賦予 PL/SQL 極大彈性，但也帶來除錯難度與注入風險。遵循「綁定變數優先、弱型別游標、統一異常處理關閉游標」三原則，即能安全高效地掌握動態查詢威力。