merge data deliveries with main table; report I/U's

{CREATE OR REPLACE PACKAGE klondike.merge_cnter
AS
   i_inserting CONSTANT   NUMBER := 0;
   i_updating CONSTANT    NUMBER := 1;

   FUNCTION merge_cnter (action_in IN number DEFAULT i_inserting )
      RETURN NUMBER;

   FUNCTION get_update_cnt
      RETURN NUMBER;

   FUNCTION get_update_cnt (merge_cnt_in IN number)
      RETURN NUMBER;

   FUNCTION get_insert_cnt
      RETURN NUMBER;

   FUNCTION get_insert_cnt (merge_cnt_in IN number)
      RETURN NUMBER;
END merge_cnter;
/

CREATE OR REPLACE PACKAGE BODY klondike.merge_cnter
AS
   g_update_cnter   NUMBER NOT NULL := 0;
   g_insert_cnter   NUMBER NOT NULL := 0;


   FUNCTION merge_cnter (action_in IN number DEFAULT i_inserting )
      RETURN NUMBER
   IS
   BEGIN
      CASE action_in
         WHEN i_updating
         THEN
            g_update_cnter   := g_update_cnter + 1;
         WHEN i_inserting
         THEN
            g_insert_cnter   := g_insert_cnter + 1;
         ELSE
            RAISE PROGRAM_ERROR;
      END CASE;

      RETURN 0;
   END merge_cnter;

   FUNCTION get_update_cnt
      RETURN NUMBER
   IS
   BEGIN
      RETURN g_update_cnter;
   END get_update_cnt;

   FUNCTION get_update_cnt (merge_cnt_in IN number)
      RETURN NUMBER
   IS
   BEGIN
      RETURN NVL (merge_cnt_in - g_insert_cnter, 0);
   END get_update_cnt;


   FUNCTION get_insert_cnt
      RETURN NUMBER
   IS
   BEGIN
      RETURN g_insert_cnter;
   END get_insert_cnt;


   FUNCTION get_insert_cnt (merge_cnt_in IN number)
      RETURN NUMBER
   IS
   BEGIN
      RETURN NVL (merge_cnt_in - g_update_cnter, 0);
   END get_insert_cnt;

   
END merge_cnter;
/

BEGIN
   MERGE INTO employee e
USING (SELECT *
       FROM employee_stg) estg
ON (e.empno = estg.empno)
   WHEN MATCHED
   THEN
      UPDATE SET e.ename = estg.ename, e.sal = estg.sal
   WHEN NOT MATCHED
   THEN
      INSERT (et.empno, et.ename)
      VALUES (
                CASE merge_counter.merge_cntr (merge_counter.i_inserting)
                   WHEN 0 THEN estg.empno
                END,
                estg.ename,
                estg.sal
             );

   DBMS_OUTPUT.put_line ('(' || TO_CHAR (sql%ROWCOUNT) || ') rows merged.');


   DBMS_OUTPUT.put_line(   '('
                        || TO_CHAR (merge_counter.get_merge_insert_count)
                        || ' )rows inserted.');
   DBMS_OUTPUT.put_line('('
                        || TO_CHAR(merge_counter.get_merge_update_count(sql%ROWCOUNT))
                        || ' )rows updated.');
END;
/

select NVL(cur.accno, -1) cur_accno, cur.prodid cur_prodid, cur.date cur_date, cur.descr cur_descr,
NVL(new.accno, -1) new_accno, new.prodid new_prodid, new.date new_date, new.descr new_descr
from cur_acct cur full join new_acct new
on cur.accno=new.accno
*where column name = some codition;*