oracle 表函数与管道函数结合示例

 

create table LX3
(
  XH   INTEGER not null,
  TYPE VARCHAR2(1)
)
/

alter table LX3
  add constraint PK$_LX3$XH primary key (XH)
/

insert into lx3 (XH, TYPE) values (1, 'a');
insert into lx3 (XH, TYPE) values (2, 'b');
insert into lx3 (XH, TYPE) values (3, 'c');
insert into lx3 (XH, TYPE) values (4, 'd');
insert into lx3 (XH, TYPE) values (5, 'g');
commit;

select * from lx3;

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 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 pipelined is
 begin
  for i in 1..100 loop
   pipe row(i);
  end loop;
  return;
 end;

end;
/

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