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;  
CREATE OR REPLACE PACKAGE pkg1 AS
    TYPE numset_t IS TABLE NUMBER;
    FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
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;  
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;
  1. SELECT * FROM TABLE(pkg1.f1(5));  
SELECT * FROM TABLE(pkg1.f1(5));
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;  
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;

  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;  
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;

  1. SELECT * FROM TABLE(  
  2.    refcur_pkg.f_trans(CURSOR(SELECT * FROM emp WHERE empno=7782)));  
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

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

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值