Oracle’INSERT ALL’忽略一再项

我有一个数据库表,它有一个独一的束缚(独一(DADSNBR,DAROLEID)对).我将同时在这个表中插入多个值,以是我想行使一个查询完成它 – 我假设这将是更快的方法.我的查询是这样的：

INSERT ALL
    INTO ACCESS (DADSNBR,DAROLEID) VALUES (68,1)
    INTO ACCESS (DADSNBR,2)
    INTO ACCESS (DADSNBR,3)
    INTO ACCESS (DADSNBR,4)
SELECT 1 FROM DUAL

因为语句中的某些条目与数据库中已存在的条目一再,因此整个插入失败而且未插入任何行.

有没有步伐忽略独一束缚失败的环境,只需插入独一的那些,而不必将其拆分成单独的INSERT语句？

编辑：我意识到我也许不想这样做,但我如故很好奇它是否也许.

>行使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.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!