Oracle Pipelined Table Functions |

由 Table function 返回的集合將消耗 PGA 記憶體，因此函數如果產生非常大的數據集可能會導致記憶體耗用錯誤。那能做些什麼？

那就得將 table function 轉為 pipelined table function。

Pipelined table function 是一種特別的 table function，它透過管道 (pipe) 在函數完成所有處理之前，將數據以函數所生成的形式返回 (RETURN) 給調用的查詢語句。

在深入 Pipelined table function 的實現和應用之前，了解這個述語句的不一樣非常重要。 PL/SQL 不是多線程語言 (Multithread)。通常，當調用 PL/SQL 區塊（anonymous、nested、procedure、function 等）時，該 session 的進一步處理將 “等候(on hold)”（或暫停 - suspended），直到該 PL/SQL 區塊將控制權返回給調用區塊的宿主(host)，該宿主有可能是另一個 PL/SQL 區塊，SQL 語句或 Java 等主機語言。

一般的（非 pipelined） table function 都以這種方式作業。每次在 FROM 子句中調用 table function 時，SQL 引擎必須等到函數執行 RETURN 語句才能將集合傳遞回 SELECT 語句以轉換為行和列。

此種阻塞行為可能會對 SELECT 語句的整體性能產生負面影響，尤其是在數據倉儲的 ETL 操作中。此外，隨著每個元素添加到 table function 中的集合中，消耗了越來越多的 PGA 記憶體。對於非常大的數據集，這可能導致進一步的性能下降，甚至錯誤。

Pipelined table function 解決了這兩個問題(性能與記憶體)。讓我們透過一個非常簡單的一般的 Table function 和一個非常簡單的 pipelined table function 來開始我們對它的了解。

首先，創建一個 Schema-level 包含單一字符串的 nested table 類型。

1 2	CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2 (100) /

接下來，編譯一個 table function，該函數返回一個 strings_t 類型的 nested table，其中只包含一筆資料內含一個字符串:

CREATE OR REPLACE FUNCTION strings
  RETURN strings_t
  AUTHID DEFINER
IS
  l_strings strings_t := strings_t ('壹貳叁');
BEGIN
  RETURN l_strings;
END;
/

1 2	SELECT COLUMN_VALUE my_string FROM TABLE (strings()) /

MY_STRING
----------------------------------------------------------------------------------
壹貳叁

現在創建同一個 table function 的 pipelined 版本。

CREATE OR REPLACE FUNCTION strings_pl
  RETURN strings_t
  PIPELINED
  AUTHID DEFINER
IS
BEGIN
  PIPE ROW('壹貳叁');
  RETURN;
END;
/

1 2	SELECT COLUMN_VALUE my_string FROM TABLE (strings_pl()) /

MY_STRING
----------------------------------------------------------------------------------
壹貳叁

我們看到了相同的結果。

現在讓我們探討這個一般的 table function 和 pipelined table function 中的代碼之間的差異：

一般的 Table function 和 Pipelined table function 之間還有另一個很大的區別:一般的 Table function 可以在 PL/SQL 中調用，但 Pipelined table function 只能在 SELECT 語句中調用。

DECLARE
  l_strings strings_t := strings_t ();
BEGIN
  l_strings := strings_pl ();  /* 這行不通的 */
END;
/

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

這應該是完全意料中的。因為 PL/SQL 不是多線程 (Multithread) 語言，所以它不能接受在函數終止執行之前 PIPE 回來的數據。也因此，在 Pipelined table function 功能中，它不會有阻塞或記憶體消耗的問題。它與一般的 Table function 的基本差異：

添加 PIPELINED 關鍵字到函數標頭。
使用 PIPE ROW 將數據發送回調用的 SELECT 語句，而不是將數據添加到 local 的集合。
函數只返回控制，沒有其它的數據。

現在看一個比較實際的應用，這裡要將 emp Table 的每一筆資料依薪資 (sal) 與獎金 (comm) 拆成兩筆:

CREATE OR REPLACE TYPE employee_ot
  AUTHID DEFINER
  IS OBJECT
(
  empno        NUMBER(4),
  ename        VARCHAR2(10),
  income_type  CHAR(1),
  income_value NUMBER(7,2),
  dname        VARCHAR2(14)
)
/

1 2	CREATE OR REPLACE TYPE employee_nt IS TABLE OF employee_ot /

CREATE OR REPLACE FUNCTION employee_pl(deptno_in NUMBER DEFAULT NULL)
  RETURN employee_nt
  PIPELINED
  AUTHID DEFINER
IS
  CURSOR c1 IS
    SELECT e.empno, e.ename, e.sal, e.comm, d.dname
      FROM emp e, dept d
     WHERE e.deptno = d.deptno
       AND e.deptno = coalesce(deptno_in, e.deptno)
     ORDER BY e.empno;
BEGIN
  FOR rec IN c1
  LOOP
    PIPE ROW(employee_ot(rec.empno, rec.ename, 'S', rec.sal, rec.dname));
    PIPE ROW(employee_ot(rec.empno, rec.ename, 'C', rec.comm, rec.dname));
  END LOOP;
  
  RETURN;
END;
/

1	select * from table(employee_pl(30));

     EMPNO ENAME      I INCOME_VALUE DNAME
---------- ---------- - ------------ --------------
      7499 ALLEN      S         1600 SALES
      7499 ALLEN      C          303 SALES
      7654 葉習堃      S         1250 SALES
      7654 葉習堃      C         1400 SALES
      7698 BLAKE      S         2850 SALES
      7698 BLAKE      C          101 SALES
      7844 하찮고      S         1500 SALES
      7844 하찮고      C              SALES
      7900 JAMES      S        94998 SALES
      7900 JAMES      C              SALES

10 rows selected.

NO_DATA_NEEDED

有時候需要在所有行被傳回之前終止 Pipelined table function，就像下面例子:

1	select * from table(employee_pl()) where rownum < 5;

     EMPNO ENAME      I INCOME_VALUE DNAME
---------- ---------- - ------------ --------------
      7369 SMITH      S         8001 RESEARCH
      7369 SMITH      C              RESEARCH
      7499 ALLEN      S         1600 SALES
      7499 ALLEN      C          303 SALES

where rownum < 5 將會在接收到 4 筆資料後引發資料庫 NO_DATA_NEEDED 異常。這將終止函數，但不會終止調用它的 SELECT 語句。因此，在大多數情況下，你不必擔心此 NO_DATA_NEEDED 異常。

但如果滿足以下任一條件，則需要特別去處理此異常：

在包含 PIPE ROW 語句的區塊中包含 OTHERS 異常處理程序。
提供 PIPE ROW 語句的代碼必須有一些清理的程序。通常，清理過程會釋放代碼不再需要的資源。

讓我們更詳細地探討這種行為。在上面示例中，我只 SELECT 了 4 行，因此 Oracle 資料庫引發了 NO_DATA_NEEDED 而終止了函數的執行，但沒有引發異常。

現在在原函數只添加一個 OTHERS 異常處理程序。

CREATE OR REPLACE FUNCTION employee_pl(deptno_in NUMBER DEFAULT NULL)
  RETURN employee_nt
  PIPELINED
  AUTHID DEFINER
IS
  CURSOR c1 IS
    SELECT e.empno, e.ename, e.sal, e.comm, d.dname
      FROM emp e, dept d
     WHERE e.deptno = d.deptno
       AND e.deptno = coalesce(deptno_in, e.deptno)
     ORDER BY e.empno;
BEGIN
  FOR rec IN c1
  LOOP
    PIPE ROW(employee_ot(rec.empno, rec.ename, 'S', rec.sal, rec.dname));
    PIPE ROW(employee_ot(rec.empno, rec.ename, 'C', rec.comm, rec.dname));
  END LOOP;
  
  RETURN;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
    RAISE;  
END;
/

在包含 PIPE ROW 語句的區塊中包含 OTHERS 異常處理程序。

1
2
3

set serveroutput on

select * from table(employee_pl()) where rownum < 5;

      EMPNO ENAME      I INCOME_VALUE DNAME
---------- ---------- - ------------ --------------
      7369 SMITH      S         8001 RESEARCH
      7369 SMITH      C              RESEARCH
      7499 ALLEN      S         1600 SALES
      7499 ALLEN      C          303 SALES

Error: ORA-06548: no more rows needed

如這裡顯示的，NO_DATA_NEEDED 錯誤被 OTHERS 處理程序捕獲，並且重新 RAISE，但這不會在 SELECT 語句中顯示為錯誤。但是，採用這種方法的問題在於，您的 OTHERS 處理程序可能需要包含特定的清理代碼，這些代碼對於意外故障有意義，但不適用於數據管道的提前終止。因此，建議您單獨為 NO_DATA_NEEDED 包含一個特定的處理程序。

在下面的代碼中，展示 NO_DATA_FOUND 和 NO_DATA_NEEDED 在 Pipelined table function 中預設是從從調用的查詢中被 “隱藏”，但其他異常 (如 PROGRAM_ERROR) 則會導致 SQL 語句的終止。

CREATE OR REPLACE FUNCTION strings (err_in IN VARCHAR2)
  RETURN strings_t
  PIPELINED
  AUTHID DEFINER
IS
BEGIN
  PIPE ROW (1);
  
  CASE err_in
    WHEN 'no_data_found'
    THEN
      RAISE NO_DATA_FOUND;
    WHEN 'no_data_needed'
    THEN
      RAISE NO_DATA_NEEDED;
    WHEN 'program_error'
    THEN
      RAISE PROGRAM_ERROR;
  END CASE;
  
  RETURN;
END;
/

這段程式碼中我們故意引發錯誤，觀察 SQL 的回應。

SQL> select COLUMN_VALUE my_string from table(strings('no_data_found'));

MY_STRING
--------------------------------------- /* NO_DATA_FOUND 錯誤被 SQL 隱藏了 */
1

SQL> select COLUMN_VALUE my_string from table(strings('no_data_needed'));

MY_STRING
--------------------------------------- /* NO_DATA_NEEDED 錯誤被 SQL 隱藏了 */
1

SQL> select COLUMN_VALUE my_string from table(strings('program_error'));
ERROR:
ORA-06501: PL/SQL: program error        /* 其它的異常會導致 SQL 語句的終止 */
ORA-06512: at "DEMO.STRINGS", line 18

no rows selected

關於 NO_DATA_NEEDED 的基本要點是不要擔心，除非你在 Pipelined table function 中提供 WHEN OTHERS 處理程序。在這種情況下，請確保單獨為 NO_DATA_NEEDED 提供處理程序，只需使用 RAISE 語句重新引用該異常即可 (即 RAISE NO_DATA_NEEDED)。

Pipelined table function 在 PL/SQL 中是一個比較特殊的東西。它們在函數完成之前就將數據傳遞回調用的查詢。 RETURN 除了返回控制之外，它們不會傳回任何數據。而你不能在 PL/SQL 本身內調用 Pipelined table function ，只能從 SELECT 語句的 FROM 子句中調用。

但是那些奇怪的功能反映了這種特殊功能的強大：與普通 (非 Pipelined) Table function 相比，性能提高不少，更減少了記憶體的消耗。