其实我这样做主要是为了提高查询效率,因为这三个表都比较大,如果用多表联查会很慢,所以我想用plsql表返回多条记录,不知道有没有其它的好方法,万分感谢!create or replace package pkg is cursor rc is select a.a01,b.b01,c.c01 from a,b,c; type mytbtype is table of rc%rowtype index by binaty_integer;
function func return mytbtype;end pkg;create or replace package body IndexAndCheck asfunction func return mytbtype is mytable mytbtype; cursor rc1; i number;begin i = 1; open rc1 for select a01 from a; loop fetch rc1 into mytable(i).a01; exit when rc1%notfound; select b01 into rc(i).b01 from b where b01 = mytable(i).a01; select c01 into rc(i).c01 from c where c01 = mytable(i).a01; end loop; return rc; end func; end pkg;
type mytbtype is table of rc%rowtype index by binaty_integer;
function func return mytbtype;end pkg;create or replace package body IndexAndCheck asfunction func return mytbtype is
mytable mytbtype;
cursor rc1;
i number;begin
i = 1;
open rc1 for select a01 from a;
loop
fetch rc1 into mytable(i).a01;
exit when rc1%notfound;
select b01 into rc(i).b01 from b where b01 = mytable(i).a01;
select c01 into rc(i).c01 from c where c01 = mytable(i).a01;
end loop;
return rc;
end func;
end pkg;
可以避免与web service的交互造成的网络负载,还可以加快速度!
你所要做的只是优化你的三个表连接的查询语句!做好索引。
使你的sql更有效率。