欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

如何获取MERGE操作中UPDATE行数和INSERT行数

程序员文章站 2024-02-06 21:50:04
...

在进行MERGE操作的时候,如何取得更新的行数和插入的行数?

在进行MERGE操作的时候,如何取得更新的行数和插入的行数?

首先创建测试表如下:
CREATE TABLE emp_source AS
SELECT * FROM emp; -- 14 rows

CREATE TABLE emp_target AS
SELECT * FROM emp_source WHERE ROWNUM -- 8 rows

创建用于获取插入行数的包:
CREATE OR REPLACE PACKAGE merge_demo AS
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 AS
g_insert_counter PLS_INTEGER NOT NULL := 0;

FUNCTION merge_counter RETURN PLS_INTEGER IS
BEGIN
g_insert_counter := g_insert_counter + 1;--注意:此函数永远返回0,即此函数不影--响插入,,但在每次插入都进行计数。此是关键。
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;
/

以下代码通过上述包获取插入行数,并结合使用SQL%ROWCOUNT取得更新行数:
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;

如何获取MERGE操作中UPDATE行数和INSERT行数