When performing the MERGE operation, how do I obtain the number of updated rows and the number of inserted rows?
When performing the MERGE operation, how do I obtain the number of updated rows and the number of inserted rows?
When performing the MERGE operation, how do I obtain the number of updated rows and the number of inserted rows?
First, create a test table as follows:
Create table emp_source
SELECT * FROM emp; -- 14 rows
Create table emp_target
SELECT * FROM emp_source where rownum -- 8 rows
Create a package to obtain the number of inserted rows:
Create or replace package merge_demo
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
G_insert_counter PLS_INTEGER not null: = 0;
FUNCTION merge_counter RETURN PLS_INTEGER IS
BEGIN
G_insert_counter: = g_insert_counter + 1; -- Note: This function always returns 0, that is, this function does not affect-ring insertion, but counts every insert. This is the key.
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;
/
The following code uses the preceding package to obtain the number of inserted rows and uses SQL % ROWCOUNT to obtain the number of updated rows:
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;