Oracle“INSERT ALL”忽略重复 [英] Oracle 'INSERT ALL' ignore duplicates

问题描述

我有一个具有唯一约束的数据库表（唯一的（DADSNBR，DAROLEID）对）。我将同时插入多个值到这个表中，所以我想使用一个查询完成它 - 我假设这将是更快的方式。我的查询是：

  INSERT ALL 
 INTO ACCESS（DADSNBR，DAROLEID）VALUES（68，1）
 INTO ACCESS（DADSNBR，DAROLEID）值（68，2）
 INTO ACCESS（DADSNBR，DAROLEID）值（68,3）
 INTO ACCESS（DADSNBR，DAROLEID）VALUES（68，4）
 SELECT 1 FROM DUAL 
  

由于语句中有一些条目是已经重复的条目在数据库中，整个插入失败，没有插入任何行。

有没有办法忽略唯一约束失败的情况，只需插入它们这是独一无二的，而不必将其拆分成单独的INSERT语句？

编辑：我意识到我可能不想这样做无论如何，但我仍然很好奇是否可以。

解决方案

在Oracle中，语句成功完成或失败完全（它们是原子的）。但是，您可以在某些情况下添加子句来记录异常而不是提高错误：

• 使用批量收集 - 保存例如 = nofollow noreferrer> askTom上的这个线程，


• 或使用 DBMS_ERRLOG （自10g以来可用）。

第二种方法都是自动的，这里是一个演示（使用11gR2）：

  SQL> CREATE TABLE test（pk1 NUMBER，
 2 pk2 NUMBER，
 3 CONSTRAINT pk_test PRIMARY KEY（pk1，pk2））; 
 
创建表。 
 
 SQL> / *语句失败，因为重复* / 
 SQL> INSERT进入测试（SELECT 1，1 FROM dual CONNECT BY LEVEL< = 2）; 
 
第1行的错误：
 ORA-00001：唯一约束（VNZ.PK_TEST）违反
 
 SQL> BEGIN dbms_errlog.create_error_log（'TEST'）;结束; 
 2 / 
 
 PL / SQL程序成功完成。 
 
 SQL> / *语句成功，错误将被记录* / 
 SQL> INSERT into test（SELECT 1，1 FROM dual CONNECT BY LEVEL< = 2）
 2 LOG ERRORS REJECT LIMIT UNLIMITED; 
 
 1行插入。 
 
 SQL>从err $ _test选择ORA_ERR_MESG $，pk1，pk2; 
 
 ORA_ERR_MESG $ PK1 PK2 
 ---------------------------------- ----------------- --- --- 
 ORA-00001：唯一约束（VNZ.PK_TEST）违反1 1 
  

您可以使用 LOG ERROR 子句与 INSERT ALL （感谢 @Alex Poole ），但是您必须在每个表之后添加子句：

  SQL> INSERT ALL 
 2 INTO测试值（1，1）日志错误拒绝限制无限
 3 INTO测试值（1，1）日志错误拒绝限制无限
 4（SELECT * FROM dual）; 
 
 0行插入。 
  

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:

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?

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.

解决方案

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:

• using BULK COLLECT - SAVE EXCEPTIONS, as demonstrated in this thread on askTom,
• or using DBMS_ERRLOG (available since 10g I think).

The second method is all automatic, here's a demo (using 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 ERROR clause with INSERT ALL (thanks @Alex Poole), but you have to add the clause after each table:

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.

这篇关于Oracle“INSERT ALL”忽略重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！