Oracle Pipelined Table Functions简介

//这种问题经常出现，或许不是关于怎么生成6个随机数，而是怎么样得到N行？ //例如，我们想要得到包括在2011-05-01和2011-05-1

//概况  
//基本上，当你希望一个PLSQL(或者java或者c)程序作为数据源，而不是表，  
//你可能会用到管道函数(pipelined function).  
//pipelined function操作起来就像一张表  
//一个pl/sql函数可能会用于数据仓库的数据库里面，转换大量的数据。  
//这可能包括格式化一系列转换数据，它们是不同的函数执行后得到的结果。  
//在Oracle database 9i之前，大量的数据转换，既需要显著的内存开销，  
//又需要在转换的每个阶段将数据存储在中间表里面。在这两种情况下，  
//加载进程都会致使性能的下降。 

//使用PL/SQL表函数，可以在数据转换的时候有效的减少开销。PL/SQL表函数  
//可以接收和返回多行，交付这些数据，当他们准备好的时候，而不是一次性的处理；  
//而且PL/SQL表函数还可以并行执行操作。  
--  
//实例1：生成随机数  
//你怎么样用一条sql语句生成在1-49之间的互不相同的随机数呢？  
//我们可能从一组已经生成的数中去查询(注意下面的最内层的查询)；  
//任何拥有大于等于49行记录的表都可以做到。若不使用管道函数的话，下面是最好的解决办法：  
select r  
  from (select r  
           from (select rownum r  
                   from all_objects  
                  where rownum           order by dbms_random.value)  
where rownum /  
         R  
----------  
        36  
        40  
        30  
        26  
         3  
        42  
//我们将最内部的查询叫做内嵌视图，它产生了1..49这些数，我们通过DBMS_RANDOM.VALUE，  
//来对这49个数进行排序。我们将结果集打包在另一个内嵌视图中只返回6行，  
//如果我们反复的执行这个查询，我们每次得到的6个数都会不同。  
--  
//这种问题经常出现，或许不是关于怎么生成6个随机数，而是怎么样得到N行？  
//例如，我们想要得到包括在2011-05-01和2011-05-15之间的所有日期；怎样不用"真实"表，  
//解决这个问题呢？Oracle9i/10g内置的PIPELINED function将会告诉你答案。  
//我们编写一个PL/SQL函数，它工作起来就像一个表。我们需要借助于sql的集合类型，  
//它描述了PIPELINED function返回的值。这个例子中，我们选择了一个数的表，我们创建的虚拟表，  
//它将会返回下面的数：1,2,3,...N :  
create type array  
    as table of number;  
/  
//下一步，我们创建这个PIPELINED function，它将会接收一个输入来限制返回的行数。如果没有输入，  
//这个函数将会一直长时间的生成很多行(所以，在这个查询中，一定要细心，确保使用rownum，  
//或其他的数来限制产生的行数)。  
//位于第4行的PIPELINED关键字，将会使这个函数工作起来想像一个表：  
create function  
  gen_numbers(n in number default null)  
  return array  
  PIPELINED  
  as 
  begin  
     for i in 1 .. nvl(n,999999999)  
     loop  
         pipe row(i);  
     end loop;  
     return;  
  end;  
/  
//假设我们需要3行数据，我们现在可以使用下面的其中一个查询实现:  
select * from TABLE(gen_numbers(3));  
COLUMN_VALUE  
------------  
           1  
           2  
           3  
or  
select * from TABLE(gen_numbers)  
where rownum COLUMN_VALUE  
------------  
           1  
           2  
           3  
//现在我们准备去回答最开始的问题了，语句如下：  
select *  
  from (  
  select *  
    from (select * from table(gen_numbers(49)))  
  order by dbms_random.random  
  )  
where rownum /  
COLUMN_VALUE  
------------  
          27  
          24  
          46  
          17  
          44  
          45  
//我们可以使用这个虚拟表的功能去做很多事情，例如产生一个范围内的所有日期：  
select to_date('2011-05-01','yyyy-mm-dd')+  
        column_value-1  
  from TABLE(gen_numbers(15))  
/  
TO_DATE('2' 
-----------  
2011-05-01   
2011-05-02   
2011-05-03   
2011-05-04   
2011-05-05   
2011-05-06   
2011-05-07   
2011-05-08   
2011-05-09   
2011-05-10   
2011-05-11   
2011-05-12   
2011-05-13   
2011-05-14   
2011-05-15   
//注意上面我们使用了column_value，这是一个PIPELINED function返回的默认列的名字。  
//典型的Pipelined例子  
//当使用PL/SQL表函数时，下面是典型的步骤  
//  ·生产函数必须在其定义中使用PIPELINED关键字  
//  ·生产函数必须使用一个out参数，，这个参数是一个集合，对应到返回的结果集  
//  ·一旦有结果产生，通过使用PIPE ROW关键字将其输送给消费函数  
//  ·生产函数必须以RETURN语句结束，但是不需要制定返回值  
//  ·消费函数必须使用TABLE关键字，将从PIPELINE function返回的行集当成一个常规表  
//第一步.定义一个返回的行集的格式。  
//       在这个实例中，我们返回这三个类型的值:int,date,varchar2(25)  
CREATE OR REPLACE TYPE myObjectFormat   
AS OBJECT  
(  
  A   INT,  
  B   DATE,  
  C   VARCHAR2(25)  
)  
/  
//下一步，为第一步定义的类型定义一个集合类型  
CREATE OR REPLACE TYPE myTableType  
   AS TABLE OF myObjectFormat  
/  
//最后，生产函数被打包到一个包里面，它是一个pipelined function就像被pipelined关键字所标记一样。  
CREATE OR REPLACE PACKAGE myDemoPack  
AS  
      FUNCTION prodFunc RETURN myTableType PIPELINED;  
END;  
/  
CREATE OR REPLACE PACKAGE BODY myDemoPack AS  
FUNCTION prodFunc RETURN myTableType PIPELINED IS  
BEGIN  
  FOR i in 1 .. 5  
    LOOP  
      PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));  
    END LOOP;  
    RETURN;  
  END;  
END;  
/  
//测试结果：  
alter session set nls_date_format='yyyy-mm-dd';  
SELECT * FROM TABLE(myDemoPack.prodFunc());  
                A B           C  
----------------- ----------- ------------  
                1 2011-05-05  Row 1  
                2 2011-05-06  Row 2  
                3 2011-05-07  Row 3  
                4 2011-05-08  Row 4  
                5 2011-05-09  Row 5  
//结论：  
//在一个select语句里面，我们需要一个数据源，而不是一张表的话，Pipelined functions非常有用。