求大大们帮忙写个ORACLE能用的存储过程,我用LOOP循环逐条查询始终有错,
还望哪位高人不吝赐教。
表一
1 2
abc abc
abc ab
abc abc
ab a表二
1 2
abc ab查询插入表二输出的结果是
1 2
abc abc
abc ab
ab a1和2是列名 在线等 本人小白 还请莫怪
还望哪位高人不吝赐教。
表一
1 2
abc abc
abc ab
abc abc
ab a表二
1 2
abc ab查询插入表二输出的结果是
1 2
abc abc
abc ab
ab a1和2是列名 在线等 本人小白 还请莫怪
using (select * from tab1) a
on a.col1=b.col1 and a.col2=b.col2
when not matched then
insert values(b.col1,b.col2)
insert into tb2 select distinct a.* from (select * from tb1 minus select * from tb2) a
merge into tab2 b
using (select distinct * from tab1) a
on (a.col1=b.col1 and a.col2=b.col2)
when not matched then
insert values(a.col1,a.col2)
SQL> ed
已写入 file afiedt.buf 1 merge into b
2 using (select distinct col1,col2 from a) aa
3 on(aa.col1=b.col1 and aa.col2=b.col2)
4 when not matched then
5 insert
6* values (aa.col1,aa.col2)
SQL> /2 行已合并。SQL> select * from b;COL COL
--- ---
abc ab
abc abc
ab a
merge into 就能满足了!
begin
for c1 in (select col1,col2 from tab1) loop
for c2 in (select * from dual where (c1.col1,c1.col2) not in (select col1,col2 from tab2)) loop
insert into tab2 values c1;
end loop;
end loop;
end;
/
SQL code
begin
i := 0;
for c1 in (select col1,col2 from tab1) loop
for c2 in (select * from dual where (c1.col1,c1.col2) not in (select col1,col2 from tab2)) loop
i := i+1 ;
insert into tab2 values c1;
if(mod(i/10)=0)then
commit;
end if ;
end loop;
commit;
end loop;
end;
/