PL/SQL 動態表名游標實戰：REF CURSOR 搭配動態 SQL 實現表名參數化

問題場景

存儲過程需接收**表名參數**，查詢該表資料並處理。靜態游標無法使用變數作為表名。

解決方案：REF CURSOR + OPEN FOR 動態 SQL

核心模式

CREATE OR REPLACE PROCEDURE process_table(p_table_name IN VARCHAR2) IS
  TYPE ref_cur IS REF CURSOR;  -- 弱類型 REF CURSOR
  c_cur  ref_cur;
  v_id   NUMBER;
  v_sum  NUMBER := 0;
BEGIN
  -- 動態開啟游標：表名串接進 SQL 字串
  OPEN c_cur FOR 'SELECT employee_id FROM ' || p_table_name;
  
  LOOP
    FETCH c_cur INTO v_id;
    EXIT WHEN c_cur%NOTFOUND;
    v_sum := v_sum + v_id;
  END LOOP;
  
  CLOSE c_cur;
  DBMS_OUTPUT.PUT_LINE('Total: ' || v_sum);
END;
/

呼叫範例

BEGIN
  process_table('employees');
  process_table('emp_archive');
END;
/

進階：強類型 REF CURSOR (9i+)

-- 定義強類型游標 (回傳結構固定)
TYPE emp_cur IS REF CURSOR RETURN employees%ROWTYPE;

CREATE OR REPLACE PROCEDURE get_emp_data(p_table_name IN VARCHAR2, p_cur OUT emp_cur) IS
BEGIN
  -- 僅限回傳結構符合 employees%ROWTYPE 的表
  OPEN p_cur FOR 'SELECT * FROM ' || p_table_name;
END;
/

完整實戰範本：動態查詢並寫入目標表

CREATE OR REPLACE PROCEDURE copy_table_data(
  p_source_table IN VARCHAR2,
  p_target_table IN VARCHAR2,
  p_where_clause IN VARCHAR2 DEFAULT NULL
) IS
  TYPE ref_cur IS REF CURSOR;
  c_src  ref_cur;
  v_sql  VARCHAR2(4000);
  v_cols VARCHAR2(4000);
BEGIN
  -- 1. 取得欄位清單 (排除系統欄位)
  SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id)
  INTO v_cols
  FROM user_tab_columns
  WHERE table_name = UPPER(p_source_table)
  AND column_name NOT IN ('CREATED_BY','CREATED_DATE','UPDATED_BY','UPDATED_DATE');
  
  -- 2. 建構動態查詢
  v_sql := 'SELECT ' || v_cols || ' FROM ' || p_source_table;
  IF p_where_clause IS NOT NULL THEN
    v_sql := v_sql || ' WHERE ' || p_where_clause;
  END IF;
  
  -- 3. 開啟來源游標
  OPEN c_src FOR v_sql;
  
  -- 4. 批次寫入目標表 (使用 FORALL 效能最佳)
  -- 這裡簡化為逐行，實際建議 BULK COLLECT + FORALL
  LOOP
    -- 動態 FETCH 需要 DBMS_SQL 或預知結構
    -- 實務上建議：目標表結構固定，來源表結構相同
    NULL; -- 實作略
  END LOOP;
  
  CLOSE c_src;
EXCEPTION
  WHEN OTHERS THEN
    IF c_src%ISOPEN THEN CLOSE c_src; END IF;
    RAISE;
END;
/

安全性警示：SQL 注入風險

動態串接表名極易遭 SQL 注入，必須嚴格驗證：

-- 驗證表名合法性
FUNCTION is_valid_table(p_name VARCHAR2) RETURN BOOLEAN IS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt
  FROM user_tables
  WHERE table_name = UPPER(p_name);
  RETURN v_cnt = 1;
END;

-- 在程序開頭檢查
IF NOT is_valid_table(p_table_name) THEN
  RAISE_APPLICATION_ERROR(-20001, 'Invalid table name: ' || p_table_name);
END IF;

替代方案對比

DBMS_SQL 完整動態範例 (未知欄位結構)

CREATE OR REPLACE PROCEDURE describe_and_fetch(p_table_name IN VARCHAR2) IS
  v_cursor  INTEGER := DBMS_SQL.OPEN_CURSOR;
  v_col_cnt INTEGER;
  v_desc    DBMS_SQL.DESC_TAB;
  v_sql     VARCHAR2(4000) := 'SELECT * FROM ' || p_table_name;
BEGIN
  -- 驗證表名 (略)
  
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_cnt, v_desc);
  
  -- 動態定義欄位
  FOR i IN 1..v_col_cnt LOOP
    DBMS_SQL.DEFINE_COLUMN(v_cursor, i, '', 4000);  -- 簡化為 VARCHAR2
  END LOOP;
  
  IF DBMS_SQL.EXECUTE(v_cursor) > 0 THEN
    LOOP
      EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;
      FOR i IN 1..v_col_cnt LOOP
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_val);
        DBMS_OUTPUT.PUT(v_desc(i).col_name || '=' || v_val || ' | ');
      END LOOP;
      DBMS_OUTPUT.NEW_LINE;
    END LOOP;
  END IF;
  
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor); END IF;
    RAISE;
END;
/

最佳實踐總結

1. 白名單驗證表名：查詢 USER_TABLES 確認存在
2. 優先考慮分區表：避免動態表名需求
3. 使用弱類型 REF CURSOR：回傳給 Java/.NET 客戶端
4. 強類型 REF CURSOR：內部模組間傳遞，編譯期檢查
5. 大量資料用 BULK COLLECT + FORALL：避免逐行 FETCH/INSERT
6. 例外處理務必關閉游標：避免游標洩漏 (ORA-01000)