带条件插入的 Oracle Merge 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10600120/
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
Oracle Merge statement with conditional insert
提问by SWilk
I use following statement to insert or update current document versions our customers have signed:
我使用以下语句插入或更新我们的客户已签署的当前文档版本：
MERGE INTO schema.table ccv
USING (select :customer_id as customer_id, :doc_type as doc_type, :version as Version FROM DUAL) n
ON (ccv.customer_id = n.customer_id and ccv.doc_type = n.doc_type)
WHEN MATCHED
THEN UPDATE
set ccv.version = n.version
DELETE WHERE ccv.version is null
WHEN NOT MATCHED
THEN INSERT
( customer_id, doc_type, version)
VALUES
(:customer_id,:doc_type,:version)
Basically I want to avoid inserting on the same condition when I am deleting with DELETE WHERE statement.
基本上，当我使用 DELETE WHERE 语句删除时，我想避免在相同的条件下插入。
The thing is, that there is three different document types (doc_type), but only one or two might be simulatenously signed.
问题是，存在三种不同的文档类型 (doc_type)，但可能只有一两个可以模拟签名。
If a client signed a document, then I want to store it's version, if not, then I do not want a record with that document in database.
如果客户签署了一份文件，那么我想存储它的版本，如果没有，那么我不想在数据库中记录该文件。
So, when the new :version is null I delete the existing row. That works great. The problem is, when there were no documents of that customer stored, then oracle actually inserts a record with version = NULL.
因此，当新的 :version 为空时，我会删除现有行。这很好用。问题是，当没有存储该客户的文档时，oracle 实际上插入了一条 version = NULL 的记录。
How can I avoid inserting records when :version is null?
当 :version 为空时如何避免插入记录？
Is splitting the merge to separate delete, update and insert statement the only way to do that?
将合并拆分为单独的删除、更新和插入语句是唯一的方法吗？
采纳答案by Sebas
If you're using 10g, you may use conditions for the insert as well:
如果您使用 10g，您也可以使用插入条件：
http://www.oracle-developer.net/display.php?id=310
http://www.oracle-developer.net/display.php?id=310
Rgds.
Rgds。
