a表 a1,a2,a3,a4,a5没有主键
b表 a1,a2,a3,a4,b1其中a1,a2,a3,a4是主键c表c1,c2,a3,c4现在是a表和c表进行比较(比较a3字段值)后,如果a表中包含有c表中a3值不存在的记录,然后把这条记录插入到b表中。
如果b表中的有一条记录和要插入的记录完全一样就不能插入。
我写的存储过程如下:
create or replace procedure crsj()
is
va1 a.a1%type;
va2 a.a2%type;
va3 a.a3%type;
va4 a.a4%type;
JL_NUM number;
cursor my_cur is
select a1, a2, a3, a4 from a where a3 not in(select a3 from c);
begin
open my_cur;
loop
fetch my_cur into va1,va2,va3,va4;
exit when my_cur%notfound;
if my_cur%found then
select count(*) into JL_NUM from b where a1 = va1 and a2 = va2 and a3 = va3 and a4 = va4 and b1 = '1';
exit when JL_NUM = 1;
if JL_NUM = 0 then
insert into b values(va1,va2,va3,va4,'1');
end if;
end if;
end loop;
close my_cur;
commit;
end;问题是:
a表中有10000多条数据,c表中没有数据,最后b表中只有3000多条数据有很多数据没有插入到b表中,
估计是
select count(*) into JL_NUM from b where a1 = va1 and a2 = va2 and a3 = va3 and a4 = va4 and b1 = '1';
这条语句的问题,里面存在很多重复的问题,不知道怎么解决,各位大虾帮看看,谢谢。
b表 a1,a2,a3,a4,b1其中a1,a2,a3,a4是主键c表c1,c2,a3,c4现在是a表和c表进行比较(比较a3字段值)后,如果a表中包含有c表中a3值不存在的记录,然后把这条记录插入到b表中。
如果b表中的有一条记录和要插入的记录完全一样就不能插入。
我写的存储过程如下:
create or replace procedure crsj()
is
va1 a.a1%type;
va2 a.a2%type;
va3 a.a3%type;
va4 a.a4%type;
JL_NUM number;
cursor my_cur is
select a1, a2, a3, a4 from a where a3 not in(select a3 from c);
begin
open my_cur;
loop
fetch my_cur into va1,va2,va3,va4;
exit when my_cur%notfound;
if my_cur%found then
select count(*) into JL_NUM from b where a1 = va1 and a2 = va2 and a3 = va3 and a4 = va4 and b1 = '1';
exit when JL_NUM = 1;
if JL_NUM = 0 then
insert into b values(va1,va2,va3,va4,'1');
end if;
end if;
end loop;
close my_cur;
commit;
end;问题是:
a表中有10000多条数据,c表中没有数据,最后b表中只有3000多条数据有很多数据没有插入到b表中,
估计是
select count(*) into JL_NUM from b where a1 = va1 and a2 = va2 and a3 = va3 and a4 = va4 and b1 = '1';
这条语句的问题,里面存在很多重复的问题,不知道怎么解决,各位大虾帮看看,谢谢。
select count(1) into JL_NUM
from
(
select distinct a1,a2,a3,a4
from b
where a1 = va1 and a2 = va2 and a3 = va3 and a4 = va4 and b1 = '1'
);
然后用一个游标就能够实现(三个表的关联)
最后就是将循环插入B表就可以了。
楼上的处理的太复杂了。具体代码自己写。
在写sql的时候注意表之间的顺序和关联,以提高sql的执行效率。
不是退出循环而是不插入数据直接进行下一次循环吧?
也可以用merge into 来实现这个过程的功能
using (select * from a where not exists(select 1 from c where a3=a.a3))a
on (b.a1=a.a1 and b.a2=a.a2 and b.a3=a.a3 and b.a4=a.a4 and b.b1='1')
when not matched then
insert (b.a1,b.a2,b.a3,b.a4,b.b1) values (a.a1,a.a2,a.a3,a.a4,'1');如果a表中有重复记录,不想插入重复的值
using (select * from a where not exists(select 1 from c where a3=a.a3))a改成
using (select distinct a1,a2,a3,a4 from a where not exists(select 1 from c where a3=a.a3))a或者将过程这么改试试
create or replace procedure crsj
as
v_count number;
begin
for cur1 in (select a1,a2,a3,a4 from a where not exists(select 1 from c where a3=a.a3))
loop
select count(1) into v_count from b
where a1=cur1.a1 and a2=cur1.a2 and a3=cur1.a3 and a4=cur1.a4 and b1='1';
if v_count>0 then goto nextloop;
end if;
insert into b values(cur1.a1,cur1.a2,cur1.a3,cur1.a4,'1');
<<nextloop>>
null;
end loop;
commit;
end crsj;