有人能解释一下 Oracle 中 MERGE 语句的真正作用吗？
我正在寻找 Oracle 中 MERGE 语句真正作用的清晰解释。
这就是我所追求的：
MERGE INTO (target_table) t
USING (source_view) s
ON (join condition)
WHEN MATCHED THEN UPDATE SET col1 = val1 [, ...]
WHEN NOT MATCHED THEN INSERT (col1 [, ...]) VALUES ( val1 [, ...])
- 执行什么样的连接？我认为这是完全外连接，对吗？
- 关于
WHEN MATCHED部分：当t中的一行与s中的多行匹配时会发生什么？ - 关于
WHEN NOT MATCHED部分，我认为这意味着“当s中的一行在t中没有对应关系时” 。我说得对吗？
谢谢。
I am looking for a clear explanation of what the MERGE statement in Oracle really does.
Here is what I am after:
MERGE INTO (target_table) t
USING (source_view) s
ON (join condition)
WHEN MATCHED THEN UPDATE SET col1 = val1 [, ...]
WHEN NOT MATCHED THEN INSERT (col1 [, ...]) VALUES ( val1 [, ...])
- what kind of join is performed? I think it is full outer join, am I right?
- regarding the
WHEN MATCHEDpart: what happens when a row fromtmatches multiple rows froms? - regarding the
WHEN NOT MATCHEDpart I believe it means “when a row inshas no correspondence int”. Am I right?
Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不，这是常规的外连接。查询需要知道目标表中何时存在也在源表中的行以及源表中何时存在目标表中没有的记录。由于查询不需要响应目标表中但不在源表中的行，因此不需要双向外连接。
但是，如果没有
notmatched子句（这是完全有效的），则不会执行外连接。优化器足够聪明，知道在这种情况下，内部联接就足够了。当有多个匹配项时，针对每个匹配项执行更新。这意味着最后出现的更新将是提交中写入的更新。无法指定顺序，因此在这种情况下，更新源实际上是随机的（来自匹配集）。正如 @ Vincent Malgrat 指出的那样，这是不正确的。如果有多个匹配，Oracle 似乎会产生“ORA-40926：无法在源表中获取稳定的行集”错误。
这是正确的。
No, it's a regular outer join. The query needs to know when there are rows in the target table that are also in the source table and when there are records in the source table that are not in the target table. Since the query doesn't need to respond to rows that are in the target table but are not in the source table, it doesn't need the outer join to go both ways.
However, the outer join will not be performed if there is no
not matchedclause (which is perfectly valid). The optimizer is smart enough to know that in that case, an inner join is sufficient.When there are multiple matches, the update is performed for each match. This means that whichever update comes last will be the one written in the commit. There's no way to dictate an order, so in this case the source of the update is effectively random (from the set of matches).As @ Vincent Malgrat pointed out, this was incorrect. It seems that Oracle will produce an "ORA-40926: unable to get a stable set of rows in the source table" error if there are multiple matches.
That is correct.
这里有一篇不错的文章 http://www.oracle-developer.net/display.php ?id=203
pretty good article here http://www.oracle-developer.net/display.php?id=203