实现MERGE操中获取UPDATE行数和INSERT行数

1、创建测试表：
 源表：
  create table TEST_SOURCE
  (
    id   VARCHAR2(10),
    name VARCHAR2(100)
    ) ;


create unique index IDX_TEST_SOURCE on TEST_SOURCE (ID);


insert into TEST_SOURCE (id, name)
values ('1', 'aa1');
insert into TEST_SOURCE (id, name)
values ('2', 'aa2');
insert into TEST_SOURCE (id, name)
values ('3', 'aa3');
insert into TEST_SOURCE (id, name)
values ('4', 'aa4');
insert into TEST_SOURCE (id, name)
values ('5', 'aa5');
insert into TEST_SOURCE (id, name)
values ('6', 'aa6');
insert into TEST_SOURCE (id, name)
values ('7', 'aa7');
insert into TEST_SOURCE (id, name)
values ('8', 'aa8');
insert into TEST_SOURCE (id, name)
values ('9', 'aa9');
commit;


目标表：
create table TEST_TARGE
(
  id   VARCHAR2(10),
  name VARCHAR2(100)
);
create unique index IDX_TEST_TARGE on TEST_TARGE (ID);


insert into TEST_TARGE (id, name)
values ('1', 'aa1');
insert into TEST_TARGE (id, name)
values ('2', 'aa2');
insert into TEST_TARGE (id, name)
values ('3', 'aa3');
insert into TEST_TARGE (id, name)
values ('4', 'aa4');
commit;


创建用于获取插入行数的包：


CREATE OR REPLACE PACKAGE merge_count AS
  FUNCTION merge_counter RETURN PLS_INTEGER;
  FUNCTION get_merge_insert_count RETURN PLS_INTEGER;
  PROCEDURE reset_counters;
END merge_count;
/




CREATE OR REPLACE PACKAGE BODY merge_count 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_count;
/




BEGIN
     MERGE INTO test_targe a
        USING ( SELECT * FROM test_source ) b
        ON   ( a.id = b.id )
     WHEN MATCHED THEN
          UPDATE
          SET a.name = b.name
     WHEN NOT MATCHED THEN
          INSERT  ( a.id,a.name)
          VALUES ( CASE merge_count.merge_counter
                              WHEN 0 THEN b.id
                         END
                       , b.name
                       );
    DBMS_OUTPUT.PUT_LINE( 'Total ' || SQL%ROWCOUNT || ' rows merged.' );
    DBMS_OUTPUT.PUT_LINE(merge_count.get_merge_insert_count || ' rows inserted.');
    DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT - merge_count.get_merge_insert_count || ' rows updated.');
    merge_count.reset_counters;
END;