有三张表TA、TB,TC,数量都是上千万条以上,怎么写一个含有游标而且要有循环操作的存储过程,当TA.A1=TB.B1时,将TA的A1、A2、A3和TB的B2、B4插入TC表中,当不相等时,将TA的A1、A2、A3插入,其它字段置空。
创建表脚本如下:
create table TA(
A1 varchar2(10),
A2 varchar2(10),
A3 varchar2(10),
A4 varchar2(10),
primary key (A1)
);
create table TB(
B1 varchar2(10),
B2 varchar2(10),
B3 varchar2(10),
B4 varchar2(10),
primary key (B1)
);
create table TC(
A1 varchar2(10),
A2 varchar2(10),
A3 varchar2(10),
B2 varchar2(10),
B4 varchar2(10),
primary key (A1)
);
insert into TA values ('1','A1','B1','C1');
insert into TA values ('2','A2','B2','C2');
insert into TA values ('3','A3','B3','C3');
insert into TA values ('4','A4','B4','C4');
insert into TB values ('1','D1','E1','F1');
insert into TB values ('2','D2','E2','F2');
insert into TB values ('5','D5','E5','F5');
commit;
--drop table TA;
--drop table TB;
--drop table TC;
---------------------------------------------------------------
create or replace procedure test_proce
is
v_ta1 ta.a1%type;
v_ta2 ta.a2%type;
v_ta3 ta.a3%type;
v_tb2 tb.b2%type;
v_tb4 tb.b4%type;
Cursor cur_test is select a.a1,a.a2,a.a3,b.b2,b.b4 from ta a,tb b where a.a1=b.b1(+);
reccount number(3);
begin
open cur_test;
reccount:=1;
loop
fetch cur_test into v_ta1,v_ta2,v_ta3,v_tb2,v_tb4;
exit when cur_test%notFound;
begin
reccount:=reccount+1;
if reccount=2 then
commit;
reccount:=1;
end if;
insert into tc values (
v_ta1,
v_ta2,
v_ta3,
v_tb2,
v_tb4);
end;
end loop;
commit;
close cur_test;
end;
/