从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'));