Oracle 'INSERT ALL' 忽略重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13420461/
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 'INSERT ALL' ignore duplicates
提问by Maccath
I have a database table with a unique constraint on it (unique (DADSNBR, DAROLEID)pair). I am going to be inserting multiple values into this table simultaneously, so I'd like to get it done using one query - I'm assuming this would be the faster way. My query is thus:
我有一个带有唯一约束的数据库表（唯一(DADSNBR, DAROLEID)对）。我将同时向这个表中插入多个值，所以我想使用一个查询来完成它 - 我假设这将是更快的方法。我的查询是这样的：
INSERT ALL
INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 1)
INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 2)
INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 3)
INTO ACCESS (DADSNBR, DAROLEID) VALUES (68, 4)
SELECT 1 FROM DUAL
Since there are some entries within the statement that are duplicates of those already in the database, the whole insert fails and none of the rows are inserted.
由于语句中的某些条目与数据库中已有的条目重复，因此整个插入失败并且没有插入任何行。
Is there a way to ignore the cases where the unique constraint fails, and just insert the ones that are unique, without having to split it up into individual INSERT statements?
有没有办法忽略唯一约束失败的情况，只需插入唯一约束，而不必将其拆分为单独的 INSERT 语句？
Edit:I realised I probably don't want to do this anyway, but I'm still curious as to whether it's possible or not.
编辑：我意识到无论如何我可能不想这样做，但我仍然很好奇这是否可能。
回答by Vincent Malgrat
In Oracle, statements either succeed completely or fail completely (they are atomic). However, you can add clauses in certain cases to log exceptions instead of raising errors:
在 Oracle 中，语句要么完全成功，要么完全失败（它们是原子的）。但是，您可以在某些情况下添加子句来记录异常而不是引发错误：
- using
BULK COLLECT - SAVE EXCEPTIONS, as demonstrated in this thread on askTom, - or using
DBMS_ERRLOG(available since 10g I think).
- 使用
BULK COLLECT - SAVE EXCEPTIONS，如askTom 上的这个线程所示， - 或使用
DBMS_ERRLOG（我认为从 10g 开始可用）。
The second method is all automatic, here's a demo (using 11gR2):
第二种方法是全自动的，这是一个演示（使用 11gR2）：
SQL> CREATE TABLE test (pk1 NUMBER,
2 pk2 NUMBER,
3 CONSTRAINT pk_test PRIMARY KEY (pk1, pk2));
Table created.
SQL> /* Statement fails because of duplicate */
SQL> INSERT into test (SELECT 1, 1 FROM dual CONNECT BY LEVEL <= 2);
ERROR at line 1:
ORA-00001: unique constraint (VNZ.PK_TEST) violated
SQL> BEGIN dbms_errlog.create_error_log('TEST'); END;
2 /
PL/SQL procedure successfully completed.
SQL> /* Statement succeeds and the error will be logged */
SQL> INSERT into test (SELECT 1, 1 FROM dual CONNECT BY LEVEL <= 2)
2 LOG ERRORS REJECT LIMIT UNLIMITED;
1 row(s) inserted.
SQL> select ORA_ERR_MESG$, pk1, pk2 from err$_test;
ORA_ERR_MESG$ PK1 PK2
--------------------------------------------------- --- ---
ORA-00001: unique constraint (VNZ.PK_TEST) violated 1 1
You can use the LOG ERRORclause with INSERT ALL(thanks @Alex Poole), but you have to add the clause after each table:
您可以将LOG ERROR子句与INSERT ALL（感谢@Alex Poole）一起使用，但您必须在每个表之后添加该子句：
SQL> INSERT ALL
2 INTO test VALUES (1, 1) LOG ERRORS REJECT LIMIT UNLIMITED
3 INTO test VALUES (1, 1) LOG ERRORS REJECT LIMIT UNLIMITED
4 (SELECT * FROM dual);
0 row(s) inserted.
回答by a_horse_with_no_name
Use the MERGE statement to handle this situation:
使用 MERGE 语句来处理这种情况：
merge into "ACCESS" a
using
(
select 68 as DADSNBR,1 as DAROLEID from dual union all
select 68,2 from dual union all
select 68,3 from dual union all
select 68,4 from dual
) t
on (t.DADSNBR = a.DADSNBR and t.DAROLEID = a.DAROLEID)
when not matched then
insert (DADSNBR, DAROLEID)
values (t.DADSNBR, t.DAROLEID);
