ORACLE 表函数

从oracle 9i 开始，提供了一个叫做“管道化表函数”的概念，可以利用管道化来返回表函数。
但这种类型的函数，必须返回一个集合类型，且标明 pipelined以及不能返回具体变量，而是以一个空 return 返回!
这个函数中，通过 pipe row () 语句来送出要返回的表中的每一行
在调用这个函数的时候，通过 table() 关键字把管道流仿真为一个数据集!
见下面示例:
1、建立一个三列的对象：
create or replace type rowType_CustOrd_Line is object(order_no varchar2(12),line_no varchar2(4),rel_no varchar2(4));
2、建立table对象
create or replace type tabType_CustOrd_Line is table of rowType_CustOrd_Line;
3、建立包头：

CREATE OR REPLACE PACKAGE Dj_Test_API IS
module_  CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'DjTest';

Function Get_Customer_Order_Line(
 order_no_                IN VARCHAR2
)
return tabType_CustOrd_Line pipelined;

END Dj_Test_API;
/
4、建立包体：

CREATE OR REPLACE PACKAGE BODY Dj_Test_API IS

Function Get_Customer_Order_Line(
 order_no_                IN VARCHAR2
)
return tabType_CustOrd_Line pipelined as row_ rowType_CustOrd_Line;
begin
for custOrdRow in (select t.order_no, t.line_no,t.rel_no
                  from customer_order_line t
                  where t.order_no=order_no_) loop
   row_ := rowType_CustOrd_Line(custOrdRow.order_no, custOrdRow.line_no,custOrdRow.rel_no);
   pipe row (row_);
end loop;
return;
END Get_Customer_Order_Line;
------------------------------------------------------------------------    
END Dj_Test_API;
/

5、利用 table() 关键字把管道流仿真为一个数据集:
select * from table(Dj_Test_API.Get_Customer_Order_Line('M200800177'));

另外表函数还可以通过表类型集合返回，然后通过 table() 关键字把管道流仿真为一个数据集!
如：

CREATE OR REPLACE PACKAGE Dj_Test_API IS
module_  CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'DjTest';

Function Get_Customer_Order_Line(
 order_no_                IN VARCHAR2
)
return tabType_CustOrd_Line pipelined;

Function  Get_Cust_Order_Line_Tab(
  order_no_                IN VARCHAR2)
Return tabType_CustOrd_Line;

END Dj_Test_API;
/
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------ 
CREATE OR REPLACE PACKAGE BODY Dj_Test_API IS
 
Function Get_Customer_Order_Line(
 order_no_                IN VARCHAR2
)
return tabType_CustOrd_Line pipelined as row_ rowType_CustOrd_Line;
begin
for custOrdRow in (select t.order_no, t.line_no,t.rel_no
                  from customer_order_line t
                  where t.order_no=order_no_) loop
   row_ := rowType_CustOrd_Line(custOrdRow.order_no, custOrdRow.line_no,custOrdRow.rel_no);
   pipe row (row_);
end loop;
return;
END Get_Customer_Order_Line;
---------------------------又一示例------------------------------------
Function  Get_Cust_Order_Line_Tab(
  order_no_                IN VARCHAR2)
Return tabType_CustOrd_Line
is
   custOrd_Line_   tabType_CustOrd_Line :=tabType_CustOrd_Line();
   cursor get_attr_ is
   select t.order_no, t.line_no,t.rel_no
   from customer_order_line t
   where t.order_no=order_no_;
begin
  for rec_ in get_attr_ loop
      custOrd_Line_.extend;
      custOrd_Line_(custOrd_Line_.count) := rowType_CustOrd_Line(NULL,NULL,NULL);
      custOrd_Line_(custOrd_Line_.count).order_no :=rec_.order_no;  
      custOrd_Line_(custOrd_Line_.count).order_no :=rec_.order_no;   
      custOrd_Line_(custOrd_Line_.count).line_no :=rec_.line_no;   
      custOrd_Line_(custOrd_Line_.count).rel_no :=rec_.rel_no;
  end loop; 
   return(custOrd_Line_);
end Get_Cust_Order_Line_Tab;
------------------------------------------------------------------------    
END Dj_Test_API;
/

利用 table() 关键字把管道流仿真为一个数据集:
select *
from table(Dj_Test_API.Get_Customer_Order_Line_Tab('M200800177'));