有人能解释一下 MERGE 语句在 Oracle 中的真正作用吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4863960/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Could somebody explain what the MERGE statement really does in Oracle?
提问by Benoit
I am looking for a clear explanation of what the MERGEstatement in Oracle really does.
我正在寻找对MERGEOracle 中的语句的真正作用的明确解释。
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?
- 执行什么样的连接？我认为这是完全外连接，对吗？
- 关于
WHEN MATCHED部分：当一行来自t匹配多行时会发生什么s？ - 关于
WHEN NOT MATCHED部分，我认为它的意思是“当一行中s没有对应时t”。我对吗？
Thank you.
谢谢你。
采纳答案by Allan
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.
不，这是一个常规的外连接。查询需要知道何时目标表中存在源表中的行，以及源表中何时存在目标表中不存在的记录。由于查询不需要响应目标表中但不在源表中的行，因此它不需要外部连接双向进行。
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.
但是，如果没有not matched子句（这是完全有效的），则不会执行外连接。优化器足够聪明，知道在这种情况下，内部联接就足够了。
regarding the WHEN MATCHED part: what happens when a row from t matches multiple rows from s?
关于 WHEN MATCHED 部分：当 t 中的一行与 s 中的多行匹配时会发生什么？
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.
正如@Vincent Malgrat 指出的那样，这是不正确的。如果有多个匹配项，Oracle 似乎会产生“ORA-40926：无法在源表中获得一组稳定的行”错误。
regarding the WHEN NOT MATCHED part I believe it means “when a row in s has no correspondence in t”. Am I right?
关于 WHEN NOT MATCHED 部分，我认为它的意思是“当 s 中的一行在 t 中没有对应关系时”。我对吗？
That is correct.
那是正确的。
回答by shane87
pretty good article here http://www.oracle-developer.net/display.php?id=203
