Oracle 自訂函數回傳表型態：管道化表函數實作範例

背景

Oracle 函數原生回傳標量值。若需回傳「表格型態」並在 SQL 中直接查詢（SELECT * FROM func()），需使用 管道化表函數（Oracle 9i+）。

實作步驟

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;  -- 空 RETURN 結束
END;
/

4. SQL 中直接查詢

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

關鍵規則總結

1. 回傳型別必須是集合型別（Nested Table 或 VARRAY），並標註 PIPELINED。
2. 函數體內不可用 RETURN value 回傳集合實例，必須用 PIPE ROW(expr) 逐行送出，最後以空 RETURN; 結束。
3. 呼叫時必須包裹在 TABLE(...) 中，將管道流轉為虛擬表。
4. 可帶參數實現篩選：
   CREATE FUNCTION fun1(p_k NUMBER) RETURN table_type1 PIPELINED AS ...

對比：傳統 REF CURSOR 做法

-- 僅能在 PL/SQL 或應用層透過游標處理
CREATE OR REPLACE PACKAGE mypk AS
  TYPE t_cur IS REF CURSOR;
  PROCEDURE get_data(p_cur OUT t_cur);
END;

管道化函數優勢：可直接在 SQL 中組合（JOIN、WHERE、GROUP BY）、支援並行查詢（PARALLEL_ENABLE）、與標準 SQL 無縫整合。

效能提示

• 大量資料時注意 PGA 記憶體，避免單次管道輸出過大導致 Swap。
• 12c+ 可加 PARALLEL_ENABLE 讓管道函數在並行查詢中分片執行。
• 若僅供 PL/SQL 內部呼叫，REF CURSOR 開銷較小；需 SQL 公開查詢時，管道化函數為首選。