Oracle管道函数(Pipelined Table Function)介绍

Oracle管道函数(Pipelined Table Function)介绍

一 概述：

1、管道函数即是可以返回行集合（可以使嵌套表nested table 或数组 varray）的函数，我们可以像查询物理表一样查询它或者将其

 赋值给集合变量。

2、管道函数为并行执行，在普通的函数中使用dbms_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端

实时的输出函数执行过程中的一些信息，在oracle9i以后可以使用管道函数(pipeline function)。

3、关键字PIPELINED表明这是一个Oracle管道函数，oracle管道函数的返回值类型必须为集合，在函数中，PIPE ROW语句被用来返回该集合的单个元

素，函数以一个空的RETURN 语句结束，以表明它已经完成。

4、由于管道函数的并发多管道流式设计以及实时返回查询结果而去除了中间环节因此可以带来可观的性能提升。

二、如何编写管道函数：

      例1：

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

1. CREATE OR REPLACE PACKAGE BODY pkg1 AS  
2.     FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS  
3.         BEGIN  
4.             FOR i IN 1..x LOOP  
5.                 PIPE ROW(i);  
6.             END LOOP;  
7.             RETURN;  
8.         END;  
9. END pkg1; 

COLUMN_VALUE
------------------------
          1
          2
          3
          4
         5

三 管道函数用于数据转换：

例2：管道函数可以和常规函数一样接收任何参数，下面的管道函数中参数为ref cursor。

1. CREATE OR REPLACE PACKAGE refcur_pkg IS  
2.   TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;  
3.   TYPE outrec_typ IS RECORD (   
4.     var_num    NUMBER(6),  
5.     var_char1  VARCHAR2(30),  
6.     var_char2  VARCHAR2(30));  
7.   TYPE outrecset IS TABLE OF outrec_typ;  
8.  FUNCTION f_trans(p refcur_t)   
9.       RETURN outrecset PIPELINED;  
10. END refcur_pkg; 

1. CREATE OR REPLACE PACKAGE BODY refcur_pkg IS  
2.   FUNCTION f_trans(p refcur_t)   
3.    RETURN outrecset PIPELINED IS  
4.     out_rec outrec_typ;  
5.     in_rec  p%ROWTYPE;  
6.   BEGIN  
7.   LOOP  
8.     FETCH p INTO in_rec;  
9.     EXIT WHEN p%NOTFOUND;  
10.     -- first row  
11.     out_rec.var_num := in_rec.empno;  
12.     out_rec.var_char1 := in_rec.ename;  
13.     out_rec.var_char2 := in_rec.mgr;  
14.     PIPE ROW(out_rec);  
15.     -- second row  
16.     out_rec.var_num := in_rec.deptno;  
17.     out_rec.var_char1 := in_rec.deptno;  
18.     out_rec.var_char2 := in_rec.job;  
19.     PIPE ROW(out_rec);  
20.   END LOOP;  
21.   CLOSE p;  
22.   RETURN;  
23.   END;  
24. END refcur_pkg;

1. SELECT * FROM TABLE(  
2.    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 types CREATE 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 IS FUNCTION 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 function SELECT * 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 IS FUNCTION 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 result END; 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 table SELECT 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 触发器。下面给出操作实例：

 

1. CREATE OR REPLACE VIEW V_F_TRANS AS  
2. SELECT x.var_num, x.var_char1, x.var_char2  
3. FROM TABLE(refcur_pkg.f_trans(CURSOR (SELECT *  
4.                                 FROM emp))) x;

 

1. CREATE OR REPLACE TRIGGER tri_f_trans  
2. INSTEAD OF INSERT ON v_f_trans  
3. FOR EACH ROW  
4. BEGIN  
5.   dbms_output.put_line('Trigger of a pipelined funtion based view was on fire!');  
6. END; 

SCOTT@orcl> insert into v_f_trans values(102, 'abc','def');
Trigger of a pipelined funtion based view was on fire!
--

-- 刘轶鹤转