PLSQL:使用合并语句时，获取更新记录与插入记录的数量 [英] PLSQL: Get number of records updated vs inserted when a merge statement is used

问题描述

合并将始终为您提供合并的记录数，无论我的记录是如何使用SQL％ROWCOUNT插入或更新的.

Merge will always give you the number of records merged regardless of how my records inserted or updated using SQL%ROWCOUNT.

但是如何找出实际插入的记录数与实际更新的记录数.

But how to find out number of records that were actually inserted vs number of records that were actually updated.

我尝试了这篇文章中的选项，但这似乎不起作用-

I tried options from this post but this doesn't seem to work -

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:122741200346595110

有帮助吗?

推荐答案

通常，您无法区分受MERGE语句影响的行是如何受到影响的，以便获得插入，更新和删除的行的单独计数.

You cannot, in general, differentiate how a row affected by a MERGE statement was affected in order to get separate counts for inserted, updated, and deleted rows.

如果确实需要单独的数字，则可以发出单独的INSERT和UPDATE语句，尽管这可能会降低效率.有一些非通用的解决方案取决于特定的查询计划，但这些解决方案将非常脆弱，通常不推荐使用.

If you really need separate figures, you could issue separate INSERT and UPDATE statements though that is likely to be less efficient. There are non-general solutions that depend on particular query plans but those are going to be rather brittle and generally wouldn't be recommended.

这篇关于PLSQL:使用合并语句时，获取更新记录与插入记录的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！