Oracle 管道函數 實現即時數據流輸出

管道函數核心語法

• 返回類型必須為**集合**（Nested Table 或 VARRAY）。
• 關鍵字 PIPELINED 標識管道函數。
• 函數體內使用 PIPE ROW(expr) 逐行輸出元素。
• 以空的 RETURN; 結束，表示輸出完畢。

基礎範例：定義類型與函數

CREATE TYPE lookup_row AS OBJECT (idx NUMBER, text VARCHAR2(20));
CREATE TYPE lookups_tab AS TABLE OF lookup_row;

CREATE OR REPLACE FUNCTION Lookups_Fn RETURN lookups_tab PIPELINED IS
  v_row lookup_row;
BEGIN
  FOR j IN 1..10 LOOP
    v_row := CASE j
      WHEN 1 THEN lookup_row(1, 'one')
      WHEN 2 THEN lookup_row(2, 'TWO')
      WHEN 3 THEN lookup_row(3, 'three')
      WHEN 4 THEN lookup_row(4, 'FOUR')
      WHEN 5 THEN lookup_row(5, 'five')
      WHEN 6 THEN lookup_row(6, 'SIX')
      WHEN 7 THEN lookup_row(7, 'seven')
      ELSE lookup_row(j, 'other')
    END;
    PIPE ROW(v_row);
  END LOOP;
  RETURN;
END;
/
SELECT * FROM TABLE(Lookups_Fn);

即時輸出關鍵：設置 ARRAYSIZE

普通函數需執行完畢才一次性返回；管道函數配合 SET ARRAYSIZE 1 可逐行即時推送到客戶端：

CREATE OR REPLACE TYPE MsgType AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION f_pipeline_test RETURN MsgType 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;
END;
/
-- SQL*Plus 執行
SET ARRAYSIZE 1
SELECT * FROM TABLE(f_pipeline_test);

輸出將每秒顯示一行，而非等待 10 秒一次性返回。

管道函數中執行 DML：必須使用自治事務

直接在管道函數執行 DML 會報 ORA-14551。解決方案：添加 PRAGMA AUTONOMOUS_TRANSACTION; 並在每次 PIPE ROW 前 COMMIT（9.2.0.5+ 版本要求）：

CREATE OR REPLACE FUNCTION f_pipeline_testdml RETURN MsgType 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;
/
-- 若使用 SELECT FOR UPDATE 游標，需設置事件兼容舊版行為：
-- ALTER SYSTEM SET EVENT = "10946 trace name context forever, level 8" SCOPE=SPFILE;