定义type:create or replace type type_rows as object( id varchar2(10), number_1 varchar2(10), name varchar2(50) );create or replace type type_table is table of type_rows; 创建表:create table tt ( id varchar2(10), number_1 varchar2(10), name varchar2(50) );
insert into tt values('1','2','abc'); insert into tt values('2','3','abcd'); 编写function:create or replace function for_tt return type_table is rs type_table := type_table(); cursor c_find is select * from tt; begin for cc in c_find loop rs.extend; rs(rs.count) := type_rows(cc.id, cc.number_1, cc.name); end loop; return rs; end; 测试:SQL> select * from table(for_tt);
ID NUMBER_1 NAME ---------- ---------- -------------------------------------------------- 1 2 abc 2 3 abcd
id varchar2(10),
number_1 varchar2(10),
name varchar2(50)
);create or replace type type_table is table of type_rows;
创建表:create table tt (
id varchar2(10),
number_1 varchar2(10),
name varchar2(50)
);
insert into tt values('1','2','abc');
insert into tt values('2','3','abcd');
编写function:create or replace function for_tt return type_table is
rs type_table := type_table();
cursor c_find is
select * from tt;
begin
for cc in c_find loop
rs.extend;
rs(rs.count) := type_rows(cc.id, cc.number_1, cc.name);
end loop;
return rs;
end;
测试:SQL> select * from table(for_tt);
ID NUMBER_1 NAME
---------- ---------- --------------------------------------------------
1 2 abc
2 3 abcd
SQL>