Oracle 函數回傳表資料：管道化表函數完整實作

問題背景

Oracle 函數原生支援回傳標量值，若需回傳「表格型態」結果集，早期常用 REF CURSOR 搭配 Package 實現，但無法直接在 SQL 中以 SELECT * FROM function() 形式呼叫。Oracle 9i 引入 管道化表函數 完美解決此問題。

核心概念

• 函數必須回傳一個 集合型別，並標註 PIPELINED 關鍵字。
• 函數本體不回傳具體變數，而是以 PIPE ROW(...) 逐行輸出，最後以空 RETURN; 結束。
• 呼叫時以 TABLE(function_name) 將管道流轉為虛擬表。

完整範例

1. 建立基礎表與測試資料

CREATE TABLE tb1 (k NUMBER, v VARCHAR2(10));
INSERT INTO tb1 VALUES (100, 'aaa');
INSERT INTO tb1 VALUES (200, 'bbb');
INSERT INTO tb1 VALUES (200, 'ccc');

2. 定義物件型別與集合型別

CREATE TYPE row_type1 AS OBJECT (k NUMBER, v VARCHAR2(10));
CREATE TYPE table_type1 AS TABLE OF row_type1;

3. 建立管道化函數

CREATE OR REPLACE FUNCTION fun1
RETURN table_type1 PIPELINED AS
  v_row row_type1;
BEGIN
  FOR myrow IN (SELECT k, v FROM tb1) LOOP
    v_row := row_type1(myrow.k, myrow.v);
    PIPE ROW(v_row);
  END LOOP;
  RETURN;
END;
/

4. 呼叫驗證

SELECT * FROM TABLE(fun1);
-- 結果：
-- K          V
-- 100        aaa
-- 200        bbb
-- 200        ccc

帶參數版本

CREATE OR REPLACE FUNCTION fun1(p_k IN NUMBER)
RETURN table_type1 PIPELINED AS
  v_row row_type1;
BEGIN
  FOR myrow IN (SELECT k, v FROM tb1 WHERE k = p_k) LOOP
    v_row := row_type1(myrow.k, myrow.v);
    PIPE ROW(v_row);
  END LOOP;
  RETURN;
END;
/

SELECT * FROM TABLE(fun1(100));

效能與管理提醒

• 管道化函數適合「資料轉換、ETL 中間層、複雜邏輯封裝」等場景。
• 大量資料時注意 PGA 記憶體使用，避免過多集合物件導致記憶體壓力。
• 12c 後可搭配 PARALLEL_ENABLE 提升並行效能。

對比：REF CURSOR 做法（不支援 TABLE() 呼叫）

CREATE OR REPLACE PACKAGE mypk AS
  TYPE t_cursor IS REF CURSOR;
  PROCEDURE proc(p_name VARCHAR2, p_cur OUT t_cursor, p_id NUMBER);
END;
/
-- 需在 PL/SQL 區塊或應用層透過游標處理，無法直接 SELECT * FROM ...

結語

管道化表函數讓 PL/SQL 函數能像資料表一樣被 SQL 直接查詢，極大擴展了函數的複用性與組合性，是 Oracle 進階開發必備技能。