管道函数:用于返回一个集合,通常函数只返回一个值.
create or replace package PKG_USER_ORG_EXTEND_HELPER is
type tp_map is record(
objId varchar2(50),
val varchar2(200)
);
type tp_maps is table of tp_map;
type cur is ref cursor;
function getUserExtendAttr(attrCode varchar2) return tp_maps pipelined;
function getOrgExtendAttr(attrCode varchar2) return tp_maps pipelined;
end;
/
create or replace package body PKG_USER_ORG_EXTEND_HELPER is
-- TODO
function getUserExtendAttr(attrCode varchar2) return tp_maps pipelined is
begin
return;
end;
function getOrgExtendAttr(attrCode varchar2) return tp_maps pipelined is
p_mark number(4) := 2; p_attri_field varchar2(30); p_map tp_map; p_sql varchar2(4000); p_list cur;
begin
select d.attri_field
into p_attri_field
from top_expand_attri_define d
where d.mark = p_mark
and d.attri_code = attrCode;
p_sql := 'select o.org_id,a.' || p_attri_field || '
from top_expand_attribute a,top_organization o
where a.attri_object_id=o.org_id
and o.state = 1 and a.mark = :mark';
open p_list for p_sql using p_mark;
loop fetch p_list into p_map.objId, p_map.val;
exit when p_list % notfound;
pipe row(p_map);
end loop; return;
end;
end;
/
