CODEWITHSUNDEEP
sqloracle-database
DZN
DZN

Reputation: 1553

How do I know what action was taken by the MERGE statement (ORACLE)?

Is there a way to find out what action was taken by the MERGE statement in an ORACLE procedure?

For example, if I need to subsequently perform different procedures depending on the action performed (INSERT or UPDATE)

p.s. Forgot to clarify, I am considering a case where the MERGE statement processes exactly one row

Upvotes: 1

Views: 1266

Answers (2)

Connor McDonald
Connor McDonald

Reputation: 11591

Without modifying the table, its hard to capture what has been done. There are solutions out there that add a PL/SQL layer into the MERGE statement to force the execution of PL/SQL function for each row, but that will hurt performance.

If you really need it, an additional column could be added, eg

SQL> create table t as select empno, ename, sal, ' '  tag from scott.emp where empno != 7934;

Table created.

SQL> create table t1 as select empno, ename, sal*5 sal from scott.emp where job = 'CLERK';

Table created.

SQL>
SQL> select * from t;

     EMPNO ENAME             SAL T
---------- ---------- ---------- -
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000

13 rows selected.

SQL> select * from t1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            4000
      7876 ADAMS            5500
      7900 JAMES            4750
      7934 MILLER           6500

SQL>
SQL> merge into t
  2  using ( select * from t1) t1
  3  on ( t.empno = t1.empno )
  4  when matched then
  5    update
  6    set t.sal = t1.sal, t.tag = 'U'
  7  when not matched then
  8    insert (t.empno,t.ename,t.sal,t.tag)
  9    values (t1.empno,t1.ename,t1.sal,'I');

4 rows merged.

SQL>
SQL> select * from t;

     EMPNO ENAME             SAL T
---------- ---------- ---------- -
      7369 SMITH            4000 U
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            5500 U
      7900 JAMES            4750 U
      7902 FORD             3000
      7934 MILLER           6500 I

14 rows selected.

I've just used U/I but this column could be (say) a numeric field or similar to handle multiple MERGE's over time.

But most people heading down this route, typically end up using separate INSERT and UPDATE blocks

Upvotes: 1

APC
APC

Reputation: 146239

sql%rowcount tells us how many rows were merged (inserted / updated / deleted). There is no way to separate that count into sub-totals for each action.

For example, if I need to subsequently perform different procedures depending on the action performed

Is this a hypothetical? If not, edit your question to outline your actual situation. But generally, the options are:

  1. replace the MERGE with separate DML statements for each action;
  2. use row-level auditing to track actions (be careful introducing this if you don't already have it).

Upvotes: 0

Related Questions