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

浏览数：33 / 时间：2015年06月12日

一概述：

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 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 触发器。以下给出操作实例：

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

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

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