merge 绑定变量批量DML

来源：互联网发布：mac桌面如何显示图标编辑：程序博客网时间：2024/05/25 13:34

一.SQL CODE

begin

for ii in (select a,c,nn from tt22) loop
merge into tt2
using(select 1 from dual)
on (a=ii.a)
when matched then
update
set n=ii.nn,
c=ii.c
when not matched then
insert values(
ii.a,ii.c,ii.nn);
end loop;

end;

二.修改前

SQL> select * from tt2;

A        C                 N
-------- -------- ----------
a        cc               11
d        c                11
e        a                11

SQL> select * from tt22;

A        C                NN
-------- -------- ----------
a        gg               22
d        gg               22
e        gg               22

f jj 22

三.修改后

SQL> select * from tt2;

A        C                 N
-------- -------- ----------
a        gg               22
d        gg               22
e        gg               22
f        jj               22

SQL> select * from tt22;

A        C                NN
-------- -------- ----------
a        gg               22
d        gg               22
e        gg               22
f        jj               22