昨天的一个需求，需要返回一个嵌套表，借此机会今天对嵌套表以及VARRAY常用的oracle集合操作略加总结如下，

准备工作，

create table big_test as select object_id,object_name from dba_objects ;

create or replace type rec_result as object(object_id number,object_name varchar2(100)) ;
create or replace type tab_result as table of rec_result ;

1. 定义一个函数返回嵌套表

create or replace function func_test1 return tab_result is
Result tab_result := tab_result() ;
begin
select new rec_result(object_id,object_name) bulk collect into result from big_test ;
return(Result);
end func_test1;

2. 通过管道实时返回一个嵌套表，

create or replace function func_test3(n in Number) return tab_result PIPELINED
as
sql_string varchar2(1000) ;
type cursor_type is ref cursor ;
c1 cursor_type ;
t_id big_test.object_id%type ;
t_name big_test.object_name%type ;
begin
sql_string := 'select object_id,object_name from big_test where object_id<'|| n ;
open c1 for sql_string ;
loop
exit when c1%notfound ;
fetch c1 into t_id,t_name ;
pipe row(rec_result(t_id,t_name)) ;
end loop ;
close c1 ;
return;
end func_test3 ;

3. 在存储过程中使用嵌套表或变长数组做临时变量

create or replace procedure proc_test is
type cur_type is ref cursor ;
c1 cur_type ;
type rec_result is record(object_id number,object_name varchar2(100)) ;
type list_rec is varray(200000) of rec_result ;
list_temp list_rec ;
i number ;
Type tab_objid Is Table Of big_test.object_id%Type ;
Type tab_objname Is Table Of big_test.object_name%Type ;
v_objid tab_objid ;
v_objname tab_objname ;
begin
/*-- explicit cursor, normal for loop
open c1 for 'select object_id,object_name from big_test' ;
fetch c1 bulk collect into list_temp ;
close c1 ;
for i in 1 .. list_temp.count loop
dbms_output.put_line(list_temp(i).object_id||' '||list_temp(i).object_name) ;
end loop ;*/
-- explicit cursor, forall no loop
open c1 for 'select object_id,object_name from big_test where object_id < 1000 order by object_id';
--fetch c1 bulk collect into list_temp ;
fetch c1 bulk collect into v_objid,v_objname ;
close c1 ;
Forall i in v_objname.First .. v_objname.Last
--Insert Into temp_test(object_id,object_name) Values(list_temp(i).object_id,list_temp(i).object_name) ;
--dbms_output.put_line(v_objid(i)||' '||v_objname(i)) ;
Insert Into temp_test(object_id,object_name) Values(v_objid(i),v_objname(i)) ;
Commit ;
--dbms_output.put_line(v_objid(i)||' '||v_objname(i)) ;

/*-- explicit cursor limited
open c1 for 'select object_id,object_name from big_test where object_id < 1000 order by object_id';
loop
exit when c1%notfound ;
fetch c1 bulk collect into list_temp limit 200 ;
for i in 1 .. list_temp.count loop
dbms_output.put_line(list_temp(i).object_id||' '||list_temp(i).object_name) ;
end loop ;
dbms_output.put_line('--- This is a Break! ---') ;
end loop ;
close c1 ;*/
/*-- implicit cursor
select object_id,object_name bulk collect into list_temp from big_test;
for i in 1 .. list_temp.count loop
dbms_output.put_line(list_temp(i).object_id||' '||list_temp(i).object_name) ;
end loop ;*/
end proc_test;

先列举于此，以后再做更多的总结。

早睡早起。。。。。。