Oracle 用函数返回数据集，用table形式展现

最新推荐文章于 2023-09-30 15:16:45 发布

最新推荐文章于 2023-09-30 15:16:45 发布 · 570 阅读

文章标签：

#Oracle #performance

点滴技术专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一个使用Oracle PL/SQL通过管道函数返回记录集的示例。该示例展示了如何创建包及包体，并通过FOR循环遍历结果集，使用PIPE ROW()函数逐行插入数据。

Oracle中返回结果集，keyWords:package,pipelined,for,loop,pipe row()
/*创建包*/
create or replace package performance_audit_time as
type udpr_table is record(
CUSTOMER_ID VARCHAR2(45),
ORACLEID VARCHAR2(45)
);
type udpr_type is table of udpr_table;

function get(etime Date) return udpr_type pipelined;
end performance_audit_time;

/*创建包体*/
create or replace package body performance_audit_time as
function get(etime Date) return udpr_type pipelined is
rc_udpr udpr_table;
begin
for rc_udpr in (select bc.CUSTOMER_ID CUSTOMER_ID,ORACLEID
from BA_CUSTOMER bc left outer join BA_PERSONINCHARGEHISTORY bpich on bc.CUSTOMER_ID = bpich.CUSTOMER_ID
where bc.IS_DELETED = '0' and bpich.IS_DELETED = '0' and bpich.EFFECTIVE_DATE >= trunc(etime,'mm') and bpich.EFFECTIVE_DATE < trunc(add_months

(etime,1),'mm')
)
loop
pipe row(rc_udpr);
end loop;
return;
end;
end performance_audit_time;

/*调用方式*/
select * from table(performance_audit_time.get(to_date('2010-06-27','yyyy-MM-dd')))