有人可以解释一下MERGE语句在Oracle中的实际作用吗? [英] Could somebody explain what the MERGE statement really does in Oracle?

问题描述

我正在寻找有关Oracle中的MERGE语句真正作用的清晰解释.

I am looking for a clear explanation of what the MERGE statement in Oracle really does.

这就是我所追求的:

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 中没有对应关系时".我说的对吗?
• what kind of join is performed? I think it is full outer join, am I right?
• regarding the WHEN MATCHED part: what happens when a row from t matches multiple rows from s?
• regarding the WHEN NOT MATCHED part I believe it means "when a row in s has no correspondence in t". Am I right?

谢谢.

推荐答案

执行哪种联接?一世 认为这是完全的外部连接，是吗 对吧?

what kind of join is performed? I think it is full outer join, am I right?

不，这是常规的外部联接.查询需要知道目标表中的行是否也在源表中，以及源表中的记录何时不在目标表中.由于查询不需要响应目标表中但源表中没有的行，因此不需要外部联接即可.

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.

但是，如果没有not matched子句(完全有效)，则不会执行外部联接.优化器非常聪明，足以知道在这种情况下，内部联接就足够了.

However, the outer join will not be performed if there is no not matched clause (which is perfectly valid). The optimizer is smart enough to know that in that case, an inner join is sufficient.

关于匹配时间"部分:什么 当t的一行匹配时发生 来自s的多行?

regarding the WHEN MATCHED part: what happens when a row from t matches multiple rows from s?

有多个匹配项时，将为每个匹配项执行更新.这意味着无论最后哪个更新都将是提交中写入的更新.没有命令的顺序，因此在这种情况下，更新的源实际上是随机的(来自匹配项).

正如@ Vincent Malgrat指出的那样，这是不正确的.如果存在多个匹配项，Oracle似乎会产生"ORA-40926:无法在源表中获得稳定的行集"错误.

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.

关于何时未匹配"第一部分 相信它的意思是当s中有一行 t中没有对应关系".我说的对吗?

regarding the WHEN NOT MATCHED part I believe it means "when a row in s has no correspondence in t". Am I right?

那是正确的.

这篇关于有人可以解释一下MERGE语句在Oracle中的实际作用吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！