oracle 嵌套表各种使用方法示例

 

create or replace package dp_nest_test is
 
  function get_lx3_types return t_nest_type;
 
  function ret_lx3_point(r sys_refcursor) return t_nest_type;
 
  function ret_pipe return t_nest_type_r pipelined;
 
end;
/
create or replace package body dp_nest_test is
 
 function get_lx3_types return t_nest_type is
  v_nest_type t_nest_type:=t_nest_type('');
 begin
  select type bulk collect into v_nest_type from lx3;
  return v_nest_type;
 end;
 
 function ret_lx3_point(r sys_refcursor) return t_nest_type is
   v_nest_tab t_nest_type:=t_nest_type();
   k int :=1;
   t varchar2(200);
 begin
 
  loop
   fetch r into t;
  
   if r%notfound then
     exit;
   end if; 
  
   v_nest_tab.extend;
   v_nest_tab(k):=t||'('||ascii(t)||')';
   k:=k+1;  
  end loop;
  return v_nest_tab;
 end;
 
 function ret_pipe return t_nest_type_r pipelined is      
    v_lx3_r r_lx3:=r_lx3('','');
 begin
  for i in(select xh,type from lx3) loop
     v_lx3_r.xh:=i.xh;
     v_lx3_r.type:=i.type;
     --pipe row(v_lx3_r);   
     pipe row(r_lx3(i.xh,i.type));
  end loop;
 
  return;
 end;

end;
/

select * from table(cast(dp_nest_test.get_lx3_types as t_nest_type));
select *
  from table(dp_nest_test.ret_lx3_point(cursor (select type as t_nest_type
                                           from lx3)));
select * from table(dp_nest_test.ret_pipe);