Oracle管道函數(Pipelined Table Function)介紹

一 概述：1、管道函數即是可以返回行集合（可以使巢狀表格nested table 或數組 varray）的函數，我們可以像查詢物理表一樣查詢它或者將其
 賦值給集合變數。
2、管道函數為並存執行，在普通的函數中使用dbms_output輸出的資訊，需要在伺服器執行完整個函數後一次性的返回給用戶端。如果需要在用戶端
即時的輸出函數執行過程中的一些資訊，在oracle9i以後可以使用管道函數(pipeline function)。

3、關鍵字PIPELINED表明這是一個oracle管道函數，oracle管道函數的返回值類型必須為集合，在函數中，PIPE ROW語句被用來返回該集合的單個元

素，函數以一個空的RETURN 語句結束，以表明它已經完成。

4、由於管道函數的並發多管道流式設計以及即時返回查詢結果而去除了中間環節因此可以帶來可觀的效能提升。

二、如何編寫管道函數：        例1：

CREATE OR REPLACE PACKAGE pkg1 AS    TYPE numset_t IS TABLE NUMBER;    FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 AS    FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS        BEGIN            FOR i IN 1..x LOOP                PIPE ROW(i);            END LOOP;            RETURN;        END;END pkg1;

SELECT * FROM TABLE(pkg1.f1(5));

COLUMN_VALUE
------------------------
           1
           2
           3
           4
           5
三 管道函數用於資料轉換：
例2：管道函數可以和常規函數一樣接收任何參數，下面的管道函數中參數為ref cursor。

CREATE OR REPLACE PACKAGE refcur_pkg IS  TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;  TYPE outrec_typ IS RECORD (     var_num    NUMBER(6),    var_char1  VARCHAR2(30),    var_char2  VARCHAR2(30));  TYPE outrecset IS TABLE OF outrec_typ; FUNCTION f_trans(p refcur_t)       RETURN outrecset PIPELINED;END refcur_pkg;

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS  FUNCTION f_trans(p refcur_t)    RETURN outrecset PIPELINED IS    out_rec outrec_typ;    in_rec  p%ROWTYPE;  BEGIN  LOOP    FETCH p INTO in_rec;    EXIT WHEN p%NOTFOUND;    -- first row    out_rec.var_num := in_rec.empno;    out_rec.var_char1 := in_rec.ename;    out_rec.var_char2 := in_rec.mgr;    PIPE ROW(out_rec);    -- second row    out_rec.var_num := in_rec.deptno;    out_rec.var_char1 := in_rec.deptno;    out_rec.var_char2 := in_rec.job;    PIPE ROW(out_rec);  END LOOP;  CLOSE p;  RETURN;  END;END refcur_pkg;

SELECT * FROM TABLE(   refcur_pkg.f_trans(CURSOR(SELECT * FROM emp WHERE empno=7782)));

   VAR_NUM       VAR_CHAR1           VAR_CHAR2
---------- ------------------------------ ------------------------------
      7782       CLARK                          7839
        10         10                             MANAGER


四 用法擴充：1、表函數間傳遞資料：SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));

2、使用遊標變數接收管線函數返回的結果：OPEN c FOR SELECT * FROM TABLE(f(...));

3、使用多個遊標變數入參：
例3：

-- Define the ref cursor typesCREATE PACKAGE refcur_pkg IS  TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;  TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;    TYPE outrec_typ IS RECORD (     var_num    NUMBER(6),    var_char1  VARCHAR2(30),    var_char2  VARCHAR2(30));  TYPE outrecset IS TABLE OF outrec_typ;  FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)     RETURN outrecset PIPELINED;END refcur_pkg;/CREATE PACKAGE BODY refcur_pkg ISFUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)     RETURN outrecset PIPELINED IS    out_rec outrec_typ;    in_rec1 p1%ROWTYPE;    in_rec2 p2%ROWTYPE;BEGIN  LOOP    FETCH p2 INTO in_rec2;    EXIT WHEN p2%NOTFOUND;  END LOOP;  CLOSE p2;  LOOP    FETCH p1 INTO in_rec1;    EXIT WHEN p1%NOTFOUND;    -- first row    out_rec.var_num := in_rec1.employee_id;    out_rec.var_char1 := in_rec1.first_name;    out_rec.var_char2 := in_rec1.last_name;    PIPE ROW(out_rec);    -- second row    out_rec.var_num := in_rec2.department_id;    out_rec.var_char1 := in_rec2.department_name;    out_rec.var_char2 := TO_CHAR(in_rec2.location_id);    PIPE ROW(out_rec);  END LOOP;  CLOSE p1;  RETURN;END;END refcur_pkg;/-- SELECT query using the g_trans table functionSELECT * FROM TABLE(refcur_pkg.g_trans(  CURSOR(SELECT * FROM employees WHERE department_id = 60),  CURSOR(SELECT * FROM departments WHERE department_id = 60)));

4、管道函數作為彙總函式使用：
例4：

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),                          weight NUMBER, grade NUMBER);INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);CREATE PACKAGE pkg_gpa IS  TYPE gpa IS TABLE OF NUMBER;  FUNCTION weighted_average(input_values SYS_REFCURSOR)    RETURN gpa PIPELINED;END pkg_gpa;/CREATE PACKAGE BODY pkg_gpa ISFUNCTION weighted_average(input_values SYS_REFCURSOR)  RETURN gpa PIPELINED IS  grade NUMBER;  total NUMBER := 0;  total_weight NUMBER := 0;  weight NUMBER := 0;BEGIN-- The function accepts a ref cursor and loops through all the input rows  LOOP     FETCH input_values INTO weight, grade;     EXIT WHEN input_values%NOTFOUND;-- Accumulate the weighted average     total_weight := total_weight + weight;     total := total + grade*weight;  END LOOP;  PIPE ROW (total / total_weight);  RETURN; -- the function returns a single resultEND;END pkg_gpa;/-- the query result comes back as a nested table with a single row-- COLUMN_VALUE is a keyword that returns the contents of a nested tableSELECT w.column_value "weighted result" FROM TABLE(       pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

5、在管道函數中進行DML操作,我們使用自治事務使管道函數作為獨立交易處理：
CREATE FUNCTION f(p SYS_REFCURSOR)
  RETURN CollType PIPELINED IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN NULL;  END;
/
6、對管道函數進行DML操作：
實際上我們無法直接對管道函數進行DML操作，例如以下語句都會失敗：
UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;
  INSERT INTO f(...) VALUES ('any', 'thing');

官方給出的替代方案是建立一個基於管道函數的VIEW，然後在這個VIEW上建立相應的instead of 觸發器。下面給出操作執行個體：



























---------------------------------------By    Dylan.