Oracle merge usage

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
SQL code
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的详细信息的。