blocks|key|4547889|text|我不明白你为什么要这么做。dbms_output的输出需要有人在场查看。不仅如此，它需要有人查看所有的输出，否则它是毫无意义的。如果有超过20行，那么没有人会费心这么做。如果没有人查看所有的输出来验证，但您需要实际记录它，那么您这样做是在积极地伤害自己。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4547890|如果您确实需要记录哪些行被更新或删除，有几个选项；不过，这两个选项都涉及到性能影响。|4547891|4547892|您可以切换到ROWID，这使您能够使用临时表的ROWID创建游标。您将两个表的连接批量收集到下面的代码中。基于行‘s并根据您的业务逻辑更新/删除目标表，然后使用某种标志更新临时表，以指示所执行的操作。|ordered-list-item|4547893|您在目标表上创建一个trigger，用于记录另一个表发生的情况。|4547894|4547895|实际上，除非知道更新/删除的数量这一点很重要，否则您不应该做任何事情。创建MERGE语句的方式应确保在出现任何错误时都会出错，并使用error+logging子句记录收到的任何错误。这些更有可能是你应该注意的事情。|4547896|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7004.htm#SQLRF01405^0|D|B|0|0|0|0|A|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|12|8|@]|D|@]|E|$]]|$1|I|3|J|5|K|7|13|8|@]|D|@]|E|$]]|$1|L|3|M|5|K|7|14|8|@]|D|@$9|15|A|16|1|17]]|E|$]]|$1|N|3|-4|5|6|7|18|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|19|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]]]

I don't understand why you would want to do this. The output of <code>dbms_output</code> requires someone to be there to look at it. Not only that it requires someone to look through all of the output otherwise it's pointless. If there are more than say 20 rows then no one will be bothered to do so. If no one looks through all the output to verify but you need to actually log it then you are actively harming yourself by doing it this way.

If you really need to log which rows are updated or deleted there are a couple of options; both involve performance hits though.

<ol>
<li>You could switch to a <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#LNPLS891" rel="nofollow">BULK COLLECT</a>, which enables you to create a cursor with the ROWID of the temporary table. You BULK COLLECT a JOIN of your two tables into this. Update / delete from the target table based on rowid and according to your business logic then you update the temporary table with a flag of some kind to indicate the operation performed.</li>
<li>You create a <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7004.htm#SQLRF01405" rel="nofollow">trigger</a> on your target table which logs what's happening to another table.</li>
</ol>

In reality unless it is important that the number of updates / deletes is known then you should not do anything. Create your MERGE statement in a manner that ensures that it errors if anything goes wrong and use the error logging clause to log any errors that you receive. Those are more likely to be the things you should be paying attention to.

blocks|key|5030411|text|之前的帖子已经说过，这种方法是可疑的，因为光标/循环和用于审查的输出日志。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5030412|在SQL+Server上，MERGE语句中有一个OUTPUT子句，它允许您使用$action+(insert、update、delete)在另一个表中插入一行，以及插入或删除/覆盖所需数据中的任何列。这可以让您完全按照您的要求进行总结。|5030413|等效的Oracle+RETURNING子句可能不适用于MERGE，但适用于UPDATE和DELETE。|5030414|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Previous posters already said that this approach is suspicious, both because of the cursor/loop and the output log for review.

On SQL Server, there is an OUTPUT clause in the MERGE statement that allows you to insert a row in another table with the $action taken (insert,update,delete) and any columns from the inserted or deleted/overwritten data you want. This lets you summarize exactly as you asked.

The equivalent Oracle RETURNING clause may not work for MERGE but does for UPDATE and DELETE.

I have 2 tables: A temporary table with raw data. Rows in it may be repeating (more then 1 time). The second is the target table with actual data (every row is unique).

I'm transfering rows using a cursor. Inside the cursor I use a MERGE statement. How can I print to the console using DBMS_OUTPUT.PUT_LINE which rows are updated and which are deleted?

According to the <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm" rel="nofollow">official documentation</a> there is no such feature for this statement.

Are there any workaround?

Logging the results of a MERGE statement

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有两个表:一个包含原始数据的临时表。其中的行可能是重复的(超过1次)。第二个是包含实际数据的目标表(每一行都是唯一的)。我正在使用游标传输行。在游标中，我使用了MERGE语句。如何使用DBMS_OUTPUT.PUT_LINE打印到控制台，哪些行被更新，哪些行被删除？根据的说法，这条语句没有这样的功能。有什么变通方法吗...

问记录MERGE语句的结果
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问记录MERGE语句的结果EN