Oracle 管道函數深度解析：從語法到實戰的完整指南

什麼是管道函數？

管道函數是 Oracle 9i 引入的特殊函數類型，其核心特徵在於返回集合型別，並透過 PIPE ROW 陳述式逐行即時輸出結果給呼叫端。這打破了傳統函數「執行完畢再一次性返回」的限制，極大提升了大量資料處理的記憶體效率與回應速度。

核心語法結構

-- 1. 定義集合型別（必須為 Schema 層級）
CREATE OR REPLACE TYPE msg_type AS TABLE OF VARCHAR2(4000);
/

-- 2. 建立管道函數
CREATE OR REPLACE FUNCTION f_pipeline_test 
RETURN msg_type PIPELINED AS
BEGIN
  FOR i IN 1..10 LOOP
    PIPE ROW('Iteration ' || i || ' at ' || SYSTIMESTAMP);
    DBMS_LOCK.SLEEP(1);  -- 模擬耗時操作
  END LOOP;
  PIPE ROW('All done!');
  RETURN;  -- 空 RETURN 表示結束
END;
/

關鍵字解析

• PIPELINED：宣告此為管道函數，返回型別必須為集合
• PIPE ROW(expr)：將單一元素輸出至結果集，可多次呼叫
• RETURN：無需帶回傳值，僅表示函數結束

客戶端呼叫與即時效果驗證

在 SQL*Plus 中需設定 ARRAYSIZE 1 才能觀察到即時逐行返回效果：

SQL> SET ARRAYSIZE 1
SQL> SELECT * FROM TABLE(f_pipeline_test);

COLUMN_VALUE
--------------------------------------------------------------------------------
Iteration 1 at 14-FEB-08 02.13.18.273988000 PM +08:00
Iteration 2 at 14-FEB-08 02.13.19.275988000 PM +08:00
...
Iteration 10 at 14-FEB-08 02.13.27.288387000 PM +08:00
All done!

11 rows selected.

管道函數中的 DML 操作與自治事務

管道函數預設運行在查詢的事務上下文中，不可直接執行 DML，否則拋出 ORA-14551。解決方案：使用 PRAGMA AUTONOMOUS_TRANSACTION 建立獨立事務。

錯誤示範

CREATE OR REPLACE FUNCTION f_pipeline_testdml 
RETURN msg_type PIPELINED AS
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO test VALUES(1);  -- 直接 DML 會報錯
    PIPE ROW('insert ' || i || ' success');
    DBMS_LOCK.SLEEP(1);
  END LOOP;
  RETURN;
END;
/
-- 執行時報錯：ORA-14551: cannot perform a DML operation inside a query

正確寫法（Oracle 9.2.0.5 之前版本）

CREATE OR REPLACE FUNCTION f_pipeline_testdml 
RETURN msg_type PIPELINED AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO test VALUES(1);
    PIPE ROW('insert ' || i || ' success at ' || SYSTIMESTAMP);
    DBMS_LOCK.SLEEP(1);
  END LOOP;
  PIPE ROW('All done!');
  RETURN;
END;
/

Oracle 9.2.0.5 及更新版本的關鍵差異

因修復 Bug 2711518，自治事務行為變更：必須在 PIPE ROW 呼叫前提交或回滾，否則拋出 ORA-06519。

CREATE OR REPLACE FUNCTION f_pipeline_testdml 
RETURN msg_type PIPELINED AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO test VALUES(1);
    COMMIT;  -- 必須在 PIPE ROW 前提交
    PIPE ROW('insert values ' || i || ' success at ' || SYSTIMESTAMP);
    DBMS_LOCK.SLEEP(1);
  END LOOP;
  PIPE ROW('All done!');
  RETURN;
END;
/

相容性設定與特殊情況

若系統從 9.2.0.5 前版本升級，需保持舊行為，可設定事件 10946：

ALTER SYSTEM SET EVENT = "10946 trace name context forever, level 8" SCOPE=SPFILE;

特別注意：若管道函數中使用 SELECT ... FOR UPDATE 游標，即使在 PIPE ROW 前提交，仍會導致 ORA-1002，此時必須啟用上述事件回歸舊特性。

效能優勢與適用場景

總結

管道函數是 Oracle 處理大量資料轉換與串流輸出的利器。掌握 PIPELINED、PIPE ROW、自治事務時機與版本差異，能讓你在 ETL、報表、資料遷移等場景中編寫高效、可擴展的解決方案。