Oracle 游標表達式與表函數深度解析：從 PL/SQL 整合到 ETL 流水線並行化

核心概念對照

游標表達式：邏輯封裝與重用

基本語法

CURSOR (SELECT ... FROM ... WHERE ...)

使用場景 1：單一查詢回傳主從關聯資料

SELECT d.dept_name,
       CURSOR (SELECT e.emp_name, e.salary
               FROM emp e
               WHERE e.dept_id = d.dept_id) AS emps
FROM dept d;

應用層（Java/.NET/Python）一次取得部門與員工明細，避免 N+1 查詢問題。

使用場景 2：作為 PL/SQL 程序參數

CREATE OR REPLACE PROCEDURE process_dept_emps(p_dept_cursor SYS_REFCURSOR) IS
  v_emp_rec emp%ROWTYPE;
BEGIN
  LOOP
    FETCH p_dept_cursor INTO v_emp_rec;
    EXIT WHEN p_dept_cursor%NOTFOUND;
    -- 處理邏輯
  END LOOP;
  CLOSE p_dept_cursor;
END;
/

-- 呼叫方式
BEGIN
  process_dept_emps(CURSOR(SELECT * FROM emp WHERE dept_id = 10));
END;
/

使用場景 3：參數化視圖

CREATE OR REPLACE VIEW v_dept_emps AS
SELECT d.dept_id, d.dept_name,
       CURSOR(SELECT e.* FROM emp e WHERE e.dept_id = d.dept_id) AS emps
FROM dept d;

表函數：從集合回傳到流水線並行

階段 1：基本集合回傳（9i 前）

CREATE TYPE emp_obj AS OBJECT (emp_id NUMBER, emp_name VARCHAR2(50));
CREATE TYPE emp_tab AS TABLE OF emp_obj;

CREATE OR REPLACE FUNCTION get_emps(p_dept_id NUMBER) RETURN emp_tab IS
  v_emps emp_tab := emp_tab();
BEGIN
  SELECT emp_obj(emp_id, emp_name) BULK COLLECT INTO v_emps
  FROM emp WHERE dept_id = p_dept_id;
  RETURN v_emps;
END;
/
-- 使用：TABLE() 轉換
SELECT * FROM TABLE(get_emps(10));

缺點：需在記憶體中建構完整集合再回傳，大量資料會爆 PGA。

階段 2：流水線表函數——即產即傳

CREATE OR REPLACE FUNCTION get_emps_pipe(p_dept_id NUMBER) RETURN emp_tab PIPELINED IS
BEGIN
  FOR r IN (SELECT emp_id, emp_name FROM emp WHERE dept_id = p_dept_id) LOOP
    PIPE ROW(emp_obj(r.emp_id, r.emp_name));  -- 即時產出一列
  END LOOP;
  RETURN;
END;
/
-- 使用方式相同
SELECT * FROM TABLE(get_emps_pipe(10));

優勢：

• First-row response 極快
• 記憶體常數級（不累積結果集）
• 可作為 VIEW 定義基礎

階段 3：游標輸入參數——菊花鏈轉換

CREATE OR REPLACE FUNCTION transform_emps(p_src SYS_REFCURSOR) RETURN emp_tab PIPELINED IS
  v_rec emp%ROWTYPE;
BEGIN
  LOOP
    FETCH p_src INTO v_rec;
    EXIT WHEN p_src%NOTFOUND;
    -- 轉換邏輯：薪資調整、欄位衍生、資料清洗
    PIPE ROW(emp_obj(v_rec.emp_id, UPPER(v_rec.emp_name), v_rec.salary * 1.1));
  END LOOP;
  CLOSE p_src;
  RETURN;
END;
/

-- 鏈式呼叫：來源 → 轉換1 → 轉換2 → 目標
SELECT * FROM TABLE(
  transform_emps(
    CURSOR(SELECT * FROM TABLE(get_emps_pipe(10)))
  )
);

這就是 ETL 的菊花鏈架構：Extract → Transform → Load，每階段都是流水線函數，無中間落地。

階段 4：並行表函數——利用多核心

CREATE OR REPLACE FUNCTION big_transform(p_src SYS_REFCURSOR) RETURN emp_tab PIPELINED
PARALLEL_ENABLE(PARTITION p_src BY ANY) IS
  v_rec emp%ROWTYPE;
BEGIN
  LOOP
    FETCH p_src INTO v_rec;
    EXIT WHEN p_src%NOTFOUND;
    -- 密集運算邏輯
    PIPE ROW(emp_obj(v_rec.emp_id, complex_calc(v_rec)));
  END LOOP;
  CLOSE p_src;
  RETURN;
END;
/

-- 並行查詢自動分配工作
SELECT /*+ PARALLEL(4) */ * FROM TABLE(big_transform(CURSOR(SELECT * FROM big_table)));

關鍵字：

• PARALLEL_ENABLE：宣告可並行
• PARTITION BY ANY：隨機分發；RANGE/HASH 依欄位分發
• 需搭配並行查詢 Hint 或表並行屬性

實戰：Fanout 多目標寫入

表函數內部可執行 自主交易 寫入多張表：

CREATE OR REPLACE FUNCTION etl_fanout(p_src SYS_REFCURSOR) RETURN NUMBER PIPELINED IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_rec source_tab%ROWTYPE;
  v_cnt NUMBER := 0;
BEGIN
  LOOP
    FETCH p_src INTO v_rec;
    EXIT WHEN p_src%NOTFOUND;
    -- 寫入核心表
    INSERT INTO core_table VALUES (v_rec.id, v_rec.data);
    -- 寫入稽核表
    INSERT INTO audit_table VALUES (v_rec.id, SYSDATE, 'ETL_LOAD');
    -- 寫入數據集市
    INSERT INTO mart_table SELECT * FROM transform(v_rec);
    v_cnt := v_cnt + 1;
    IF MOD(v_cnt, 1000) = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
  CLOSE p_src;
  PIPE ROW(v_cnt);
  RETURN;
END;
/

型別定義最佳實務

-- 1. 使用 Schema 層級型別（非 PL/SQL 區域型別）
CREATE TYPE emp_obj AS OBJECT (id NUMBER, name VARCHAR2(50), sal NUMBER);
CREATE TYPE emp_tab AS TABLE OF emp_obj;

-- 2. 依資料表定義 %ROWTYPE 型別（12c+ 支援）
CREATE TYPE emp_obj AS OBJECT (emp%ROWTYPE);  -- 需先建立物件型別對應

-- 3. 避免在 Package 內定義型別（跨 Session 重用困難）

效能調優關鍵指標

遷移建議：從 Procedure + Temp Table 到表函數

1. 識別「讀取 → 處理 → 寫入暫存表 → 下一階段讀取」模式
2. 將每階段封裝為流水線函數
3. 用游標表達式串聯
4. 移除暫存表與中間 COMMIT
5. 啟用並行處理大量資料階段

總結：為什麼選擇表函數？

• ✅ 邏輯模組化、可重用、可測試
• ✅ 記憶體效率（流水線）
• ✅ 並行擴展性
• ✅ ETL 菊花鏈無中間落地
• ✅ 參數化視圖能力
• ✅ Fanout 多目標寫入
• ✅ 關聯與集合資料統一存取介面