ORACLE管道化表函数实例

公羊香馨

　　ORACLE 自定义函数，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。但是这种集合类型，并不能直接作为 FROM 的宾语。不过，自从 Oracle9i 之后，提供了一个叫做"管道化表函数"的概念。来解决此类问题，这种类型的函数，必须返回一个集合类型，且标明 PIPELINED。这个函数不能返回具体变量，必须以一个空的RETURN 返回。这个函数中，通过 PIPE ROW() 语句来送出要返回的表中的每一行。调用这个函数的时候，通过 TABLE() 关键字把管道流仿真为一个数据集。
　　管道化表函数，可以接受查询语句或游标作为输入参数，该函数可以平行执行，并可持续输出数据流，被称作管道式输出，应用表函数可将数据转换分阶段处理，并省去中间结果的存储和缓冲表。
　　下面是用Pipelined Table 实现的示例：
　　1）创建表

create table table1
(
id_  varchar2(45),
name_ varchar2(45),
update_time date,
sys_id varchar2(45)
);
insert into table1 (ID_, NAME_, UPDATE_TIME, SYS_ID)
values ('1', 'zhangshan', to_date('01-01-2015', 'dd-mm-yyyy'), '001');
insert into table1 (ID_, NAME_, UPDATE_TIME, SYS_ID)
values ('2', 'lisi', to_date('30-01-2014', 'dd-mm-yyyy'), '002');
insert into table1 (ID_, NAME_, UPDATE_TIME, SYS_ID)
values ('3', 'wangwu', to_date('12-06-2014', 'dd-mm-yyyy'), '003');
insert into table1 (ID_, NAME_, UPDATE_TIME, SYS_ID)
values ('4', 'xiaoxiao', to_date('06-01-2015', 'dd-mm-yyyy'), '004');
insert into table1 (ID_, NAME_, UPDATE_TIME, SYS_ID)
values ('5', 'zhouchang', to_date('13-08-2014', 'dd-mm-yyyy'), '005');
　　2）创建包（Packages）

create or replace package my_package as
type my_table is record(
id_ varchar2(45),
name_ varchar2(45),
update_time date,
sys_id varchar2(45)
);
type my_type is table of my_table;
function get_table(time_ date) return my_type pipelined;
end my_package;
　　3）创建包体（Packages bodies）

create or replace package body my_package as
function get_table(time_ date) return my_type
pipelined is
row_t my_table;
begin
for row_t in (select b.*
from (select id_, max(sys_id) maxsysid
from table1
where update_time < trunc(time_, 'month')
group by id_) a,
table1 b
where b.sys_id = a.maxsysid) loop
pipe row(row_t);
end loop;
return;
end;
end my_package;
　　4）PL/SQL调用此函数

select * from table(my_package.get_table(to_date('2014-12-31 10:34:12','yyyy-MM-dd hh24:mi:ss')));
　　运行结果：