两张表合并---效率问题

[wanglg5201314]

表A：a1,a2,a3,a4,a5,a6 (字段)
表B：a1,a2,a3,b1,b2,b3,a5
表C：a1,a2,a3,a4,b3
其中a1，a2，a3在3张表中都是主键。

现在合并A,B表到C表中去。
create or replace procedure hb_table(tj A.a1%type)
is
declare
--
--声明--
--va1
--va2
cursor cur_hb is
select a.a1,a.a2,a.a3,a.a4,b.b3 from A a,B,b where a5 = '333' and a.a5 = b.a5;
begin
open v_zj;
loop
fetch v_zj into va1,va2,va3,va4,vb3;
exit when v_zj%notfound;
if v_zj%found then
insert into C values(va1,va2,va3,va4,vb3);
end if;
end loop;
close v_zj;
commit;
end;

（存储过程）
A和B表数据很多，执行时间很长，请教一下怎么提高执行效率，cursor那个游标写法怎么去优化？谢谢各位啦。

[shiyiwan]

create or replace procedure hb_table(tj A.a1%type) 
is 

-- 
--声明-- 
--va1 
--va2 
cursor cur_hb is 
select a.a1,a.a2,a.a3,a.a4,b.b3 from A a,B,b where a5 = '333' and a.a5 = b.a5; 

type t_a1 is table of a.a1%type index by binary_integer;
va1 t_a1;

-- same for va2,va3,va4,vb3
...

begin 
open v_zj; 
  loop 
      fetch v_zj bulk collect into va1,va2,va3,va4,vb3
      limit             1000;
  
      forall i in va1.first..va1.last
         insert into C values(va1(i),va2(i),va3(i),va4(i),vb3(i)); 
      exit when v_zj%notfound; 
  end loop; 
  close v_zj; 
commit; 
end;

[ruixindongdong]

insert into C
select a.a1,a.a2,a.a3,a.a4,b.b3 from A a,B,b where a5 = '333' and a.a5 = b.a5;

[wwwtyb]

可以试试这个：

create or replace procedure hb_table(tj A.a1%type)
is
begin
     for l_record in (select a.a1,a.a2,a.a3,a.a4,b.b3 from A a,B,b where a5 = '333' and a.a5 = b.a5)
         loop
                  insert into C values l_record;
         end loop;
commit;
end;