oracle实现用函数返回表集合

(1)CREATE OR REPLACE Type rdtltype As Object (id1 Number(10)); 

(2)CREATE OR REPLACE Type rdtlTable As Table Of rdtltype;

(3)create or replace function ReturnTableTest return rdtlTable Pipelined Is
  rows Number(10);
  Cursor myRow Is Select rentdtlid From fc_rdetail t;
Begin
  Open myrow;
  Fetch myRow Into rows;
  While(myrow%Found) Loop
    Pipe Row(rdtltype(Rows));
      Fetch myRow Into rows;
  End Loop;
  Close myrow;
  Return;
end ReturnTableTest;

 

一个全的示例：

（1）

create table tb1(k number, v varchar2(10));

insert into tb1(k, v) values(100,'aaa');
insert into tb1(k, v) values(200,'bbb');
insert into tb1(k, v) values(200,'ccc');

select * from tb1;

create type row_type1 as object(k number, v varchar2(10));

create type table_type1 as table of row_type1;

create or replace function fun1 return table_type1 pipelined as
v row_type1;
begin
  for myrow in (select k, v from tb1) loop
    v := row_type1(myrow.k, myrow.v);
    pipe row (v);
  end loop;
  return;
end;

select * from table(fun1);

 

（2）

SQL> create or replace type acc_type as object
(
acc varchar2(50)
);
/

类型已创建。

SQL> create or replace type acc_table as table of acc_type;
/

类型已创建。

SQL> create or replace package mypkg
as
function str2table (acc_str in varchar2) return acc_table pipelined;
end;
/

程序包已创建。

SQL> create or replace package body mypkg
as
function str2table (acc_str in varchar2) return acc_table pipelined
is
v_str varchar2(30000);
v_acc varchar2(30);
v_str_len pls_integer;
begin
v_acc := ' ';
v_str:=acc_str;
v_str_len := lengthb(regexp_replace(acc_str,'[^/,]*'));
for i in 1..v_str_len loop
v_str := regexp_replace(v_str,'^'||v_acc);
v_acc := regexp_substr(v_str,'^/d+/,');
pipe row(acc_type(rtrim(v_acc,',')));
end loop;
return;
end;
end;
/

程序包体已创建。

SQL> select * from table(mypkg.str2table('1,12,123,55,99,'));