Oracle 11g:在PL/SQL中，是否有任何方法可以在MERGE DML语句之后获取有关插入和更新的行的信息?

问题描述

我想知道有什么方法可以在PL/SQL中接收信息，而在使用MERGE DML语句的PL/SQL脚本中已更新了多少行，以及插入了多少行.

I would like to know is there any way to receive information in PL/SQL how many rows have been updated and how many rows have been inserted while my PL/SQL script using MERGE DML statement.

让我们使用此处描述的Oracle合并示例: MERGE例子

Let's use Oracle example of merge described here: MERGE example

此功能已在我的函数中使用，但我也想记录信息已更新了多少行以及已插入了多少行.

This functionality is used in my function but also I'd like to log information how many rows has beed updated and how many rows have been inserted.

推荐答案

没有内置的方法来获取单独的插入和更新计数，不是.正如您可能已经知道的那样，SQL%ROWCOUNT会告诉您合并的行数，但是没有等效项可获取插入和更新的单独值.

There is a not a built-in way to get separate insert and update counts, no. SQL%ROWCOUNT would tell you the number of rows merged, as you probably already know, but there is no equivalent to get separate values for the inserts and updates.

Adrian Billington的这篇文章显示了一种获取信息的方法通过在合并中包含一个函数调用，可能会增加一些开销.

This article by Adrian Billington shows a way to get the information by including a function call in the merge, which might add a little overhead.

在Oracle论坛上， MichaelS上有一个类似的，也许更简单的技巧，当然，我也不能因此而功劳.我很想在这里重现它，但是我不确定是否允许这样做，但实际上它是使用sys_context来保持计数，这与Adrian的解决方案对package变量的处理方式大致相同.我会使用它，因为它更清洁，而且我认为它更容易遵循和维护.

There's a similar, and perhaps simpler, trick from MichaelS on the Oracle forums, which I can't take any credit for at all either, of course. I'm tempted to reproduce it here but I'm not sure if that's allowed, but essentially it's using sys_context to maintain a count, in much the same way that Adrian's solution did with a package variable. I'd use that one, as it's cleaner and I think it's easier to follow and maintain.

仍然非常危险地接近仅链接的答案，但我也不想窃他人的工作...

Still perilously close to a link-only answer but I don't want to plagiarise others' work either...

这篇关于Oracle 11g:在PL/SQL中，是否有任何方法可以在MERGE DML语句之后获取有关插入和更新的行的信息?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！