使用Merge的时候，如何知道更新了多少行，插入了多少行？

Question

使用Merge的时候，如何知道更新了多少行，插入了多少行？

Answer 1

这个，问题有些难。不过，还是有解决办法的。
1. 创建一个包:
CREATE OR REPLACE PACKAGE etl AS
 
    c_inserting CONSTANT PLS_INTEGER := 0;
    c_updating  CONSTANT PLS_INTEGER := 1;
 
    FUNCTION merge_counter (
             action_in IN PLS_INTEGER DEFAULT c_inserting
             ) RETURN PLS_INTEGER;
 
    FUNCTION get_merge_update_count RETURN PLS_INTEGER;
 
    FUNCTION get_merge_update_count (
             merge_count_in IN PLS_INTEGER
             ) RETURN PLS_INTEGER;
 
    FUNCTION get_merge_insert_count RETURN PLS_INTEGER;
 
    FUNCTION get_merge_insert_count (
             merge_count_in in PLS_INTEGER
             ) RETURN PLS_INTEGER;
 
    PROCEDURE reset_counters;
 
 END etl;
 /
 
 CREATE OR REPLACE PACKAGE BODY etl AS
 
    g_update_counter PLS_INTEGER NOT NULL := 0;
    g_insert_counter PLS_INTEGER NOT NULL := 0;
 
 /*-------------- START OF FUNCTION merge_counter --------------------*/
 FUNCTION merge_counter (
          action_in IN PLS_INTEGER DEFAULT c_inserting
          ) RETURN PLS_INTEGER IS
 BEGIN
    CASE action_in
       WHEN c_updating
          THEN g_update_counter := g_update_counter + 1;
       WHEN c_inserting
          THEN g_insert_counter := g_insert_counter + 1;
       ELSE
          RAISE PROGRAM_ERROR;
    END CASE;
    RETURN 0;
 END merge_counter;
 
 /*----------- START OF FUNCTION get_merge_update_count V1 ---------------*/
 FUNCTION get_merge_update_count
    RETURN PLS_INTEGER is
 BEGIN
    RETURN g_update_counter;
 END get_merge_update_count;
 
 /*----------- START OF FUNCTION get_merge_update_count V2 ---------------*/
 FUNCTION get_merge_update_count (
          merge_count_in IN PLS_INTEGER
          ) RETURN PLS_INTEGER IS
 BEGIN
    RETURN NVL( merge_count_in - g_insert_counter, 0 );
 END get_merge_update_count;
 
 /*----------- START OF FUNCTION get_merge_insert_count V1 ---------------*/
 FUNCTION get_merge_insert_count
    RETURN PLS_INTEGER IS
 BEGIN
    RETURN g_insert_counter;
 END get_merge_insert_count;
 
 /*----------- START OF FUNCTION get_merge_insert_count V2 ---------------*/
 FUNCTION get_merge_insert_count (
          merge_count_in IN PLS_INTEGER
          ) RETURN PLS_INTEGER IS
 BEGIN
    RETURN NVL( merge_count_in - g_update_counter, 0 );
 END get_merge_insert_count;
 
 /*-------------- START OF FUNCTION reset_counters --------------------*/
 PROCEDURE reset_counters IS
 BEGIN
    g_update_counter := 0;
    g_insert_counter := 0;
 END reset_counters;
 
 END etl;
 /2. 创建一个验证的sql文件：run_mrg.sql
set serverout on
set echo on
set pagesize 100
--
-- Run merge...
--
begin   etl.reset_counters;   merge into target tgt
      using source src
      on (src.id = tgt.id)
   when matched then
      update
      set value = (case etl.merge_counter(etl.c_updating)
                        when 0
                       then src.value
                    end)
    when not matched then
       insert
          ( tgt.id
          , tgt.value )
       values
          ( case etl.merge_counter(etl.c_inserting)
               when 0
               then src.id
            end
          , src.value );
 
    /* Use update count... */
    dbms_output.put_line(sql%rowcount || ' rows merged.');
    dbms_output.put_line(etl.get_merge_update_count || ' rows updated.');
    dbms_output.put_line(etl.get_merge_insert_count(sql%rowcount) || ' rows inserted.');
 
    /* Use insert count... */
    dbms_output.put_line(etl.get_merge_update_count(sql%rowcount) || ' rows updated.');
    dbms_output.put_line(etl.get_merge_insert_count || ' rows inserted.');
 
 end;
 /3. 验证上述用法：
create table source  ( id int, value varchar2(1) );
create table target  ( id int, value varchar2(1) );insert into source select rownum, substr(object_type,1,1) from user_objects where rownum <= 15;
insert into target select * from source where rownum <= 10;
commit;运行结果如下：
SQL> insert into source select rownum, substr(object_type,1,1) from user_objects where rownum <= 15;已创建11行。SQL> insert into target select * from source where rownum <= 10;已创建10行。SQL> commit;提交完成。
SQL> @E:\MyDocument\notes\oracle_run_merge.sql
SQL> set serverout on
SQL> set echo on
SQL> set pagesize 100
SQL> --
SQL> -- Run merge...
SQL> --
SQL> begin
  2
  3     etl.reset_counters;
  4
  5     merge into target tgt
  6        using source src
  7        on (src.id = tgt.id)
  8     when matched then
  9        update
 10        set value = (case etl.merge_counter(etl.c_updating)
 11                          when 0
 12                         then src.value
 13                      end)
 14      when not matched then
 15         insert
 16            ( tgt.id
 17            , tgt.value )
 18         values
 19            ( case etl.merge_counter(etl.c_inserting)
 20                 when 0
 21                 then src.id
 22              end
 23            , src.value );
 24
 25      /* Use update count... */
 26      dbms_output.put_line(sql%rowcount || ' rows merged.');
 27      dbms_output.put_line(etl.get_merge_update_count || ' rows updated.');
 28      dbms_output.put_line(etl.get_merge_insert_count(sql%rowcount) || ' rows inserted.');
 29
 30      /* Use insert count... */
 31      dbms_output.put_line(etl.get_merge_update_count(sql%rowcount) || ' rows updated.');
 32      dbms_output.put_line(etl.get_merge_insert_count || ' rows inserted.');
 33
 34   end;
 35   /
22 rows merged.
20 rows updated.
2 rows inserted.
20 rows updated.
2 rows inserted.PL/SQL 过程已成功完成。SQL>上述过程我是从asktom上整理出来的。希望对你有所帮助。
merge本身是不带有updated和inserted的详细信息的。

Answer 2

定义一个变量,
然后INSERT INTO....  RETURNING 变量+1 INTO...变量

Answer 3

目前没这个东西，你也别想办法整这个东西了，整出来的话效率也不佳tom说目前oracle中没这个，而且这个东西对他来说没有什么意义。http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122741200346595110

Answer 4

没有用过merge，涨一下见识。