如何擷取MERGE操作中UPDATE行數和INSERT行數

在進行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;