在进行MERGE操作的时候，如何取得更新的行数和插入的行数？
在进行merge操作的时候，如何取得更新的行数和插入的行数？
首先创建测试表如下：
create table emp_source as
select * from emp; -- 14 rows
create table emp_target as
select * from emp_source where rownum -- 8 rows
创建用于获取插入行数的包：
create or replace package merge_demo as
function merge_counter return pls_integer;
function get_merge_insert_count return pls_integer;
procedure reset_counters;
end merge_demo;
/
create or replace package body merge_demo as
g_insert_counter pls_integer not null := 0;
function merge_counter return pls_integer is
begin
g_insert_counter := g_insert_counter + 1;--注意：此函数永远返回0，即此函数不影--响插入，，但在每次插入都进行计数。此是关键。
return 0;
end merge_counter;
function get_merge_insert_count return pls_integer is
begin
return g_insert_counter;
end get_merge_insert_count;
procedure reset_counters is
begin
g_insert_counter := 0;
end reset_counters;
end merge_demo;
/
以下代码通过上述包获取插入行数，并结合使用sql%rowcount取得更新行数：
begin
merge into emp_target et
using ( select * from emp_source ) es
on ( et.empno = es.empno )
when matched then
update
set et.ename = es.ename, et.sal = es.sal, et.mgr = es.mgr, et.deptno = es.deptno
when not matched then
insert ( et.empno, et.ename, et.sal, et.mgr, et.deptno)
values ( case merge_demo.merge_counter
when 0 then es.empno
end
, es.ename, es.sal, es.mgr, es.deptno
);
dbms_output.put_line( 'total ' || sql%rowcount || ' rows merged.' );
dbms_output.put_line(merge_demo.get_merge_insert_count || ' rows inserted.');
dbms_output.put_line( sql%rowcount - merge_demo.get_merge_insert_count || ' rows updated.');
merge_demo.reset_counters;
end;
