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

標籤：style   blog   color   使用   os   io   資料   for   

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

3、keywordPIPELINED表明這是一個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 觸發器。以下給出操作執行個體：


CREATE OR REPLACE VIEW V_F_TRANS ASSELECT x.var_num, x.var_char1, x.var_char2FROM TABLE(refcur_pkg.f_trans(CURSOR (SELECT *                                FROM emp))) x;

CREATE OR REPLACE TRIGGER tri_f_transINSTEAD OF INSERT ON v_f_transFOR EACH ROWBEGIN  dbms_output.put_line('Trigger of a pipelined funtion based view was on fire!');END;

[email protected]> insert into v_f_trans values(102, ‘abc‘,‘def‘);
Trigger of a pipelined funtion based view was on fire!


























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