ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Pipelined Function

Oracle Pipelined Function

原创 Linux操作系统 作者:abstractcyj 时间:2013-08-24 18:13:28 0 删除 编辑
建立一个测试表:
create table t(id,owner,object_id) as                                                      
  select rownum,owner,object_id from all_objects;    

创建必要的类型:
create type typ_test is object(
 id number,
 owner varchar2(50),
 object_id number
);

create type typ_test_tab is table of typ_test;

创建存储过程:

CREATE OR REPLACE FUNCTION pipe_func RETURN typ_test_tab
  PIPELINED IS
  CURSOR cur_t IS
    SELECT * FROM t;
  TYPE tab_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE tab_owner IS TABLE OF t.owner%TYPE INDEX BY PLS_INTEGER;
  TYPE tab_objectid IS TABLE OF t.object_id%TYPE INDEX BY PLS_INTEGER;
  v_ids    tab_id;
  v_owners tab_owner;
  v_objs   tab_objectid;
BEGIN
  OPEN cur_t;
  LOOP
    FETCH cur_t BULK COLLECT
      INTO v_ids, v_owners, v_objs LIMIT 10000;
    EXIT WHEN cur_t%NOTFOUND;
    FOR i IN 1 .. v_ids.count LOOP
      PIPE ROW(typ_test(v_ids(i), v_owners(i), v_objs(i)));
    END LOOP;
  END LOOP;
  CLOSE cur_t;
  RETURN;

END pipe_func;


调用:
SELECT * FROM TABLE(CAST(pipe_func AS typ_test_tab)) WHERE id = 1

SELECT * FROM TABLE(pipe_func) WHERE id = 1

经测试,以上两种方式调用貌似都可行.

可以参考:
http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-769215/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle的锁
请登录后发表评论 登录
全部评论
曾从事java方向开发多年。近年已经转入数据库方向。主要擅长SQL优化,Oracle数据库问题诊断,Oracle备份与恢复等。服务于医药物流,医院等行业

注册时间:2010-01-26

  • 博文量
    555
  • 访问量
    828967