Oracle 'INSERT ALL'忽略重复项

我有一个对它有唯一约束的数据库表(唯一的(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

由于语句中的某些条目与数据库中已有的条目重复，因此整个插入操作将失败，并且不会插入任何行。

有没有一种方法可以忽略唯一约束失败的情况，而只插入唯一约束，而不必将其拆分为单独的INSERT语句？

编辑:我意识到我可能还是不想这样做，但是我仍然对是否可行感到好奇。

最佳答案

在Oracle中，语句要么完全成功要么完全失败(它们是原子的)。但是，您可以在某些情况下添加子句以记录异常，而不会引发错误:

使用BULK COLLECT - SAVE EXCEPTIONS，如this thread on askTom和

所示

或使用 DBMS_ERRLOG (我认为10g起可用)。

第二种方法是全自动的，这是一个演示(使用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

您可以将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.