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?

Редактировать: я понял, что, вероятно, все равно не хочу этого делать, но мне все еще любопытно, возможно это или нет.


duplicates oracle insert unique-constraint
person Maccath    schedule 16.11.2012    source источник
comment
Ни одна из ваших строк не повторяется (?!)   -  person Vincent Malgrat    schedule 16.11.2012
comment
Я имею в виду, что они являются дубликатами значений, которые уже существуют в базе данных до выполнения оператора вставки. Отредактировал мой вопрос для ясности.   -  person Maccath    schedule 16.11.2012

Ответы (2)


arrow_upward
12
arrow_downward

В Oracle операторы либо завершаются успешно, либо полностью завершаются неудачей (они атомарны). Однако в некоторых случаях вы можете добавить предложения для регистрации исключений вместо того, чтобы вызывать ошибки:

Второй метод полностью автоматический, вот демонстрация (с использованием 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.
person Vincent Malgrat    schedule 16.11.2012
comment
Это работает с INSERT ALL, но вам нужно поместить предложение LOG ERRORS после каждого INTO ..., как показано немного косо в документы. Так что в данном случае INSERT ALL INTO test VALUES (1, 1) LOG ERRORS REJECT LIMIT UNLIMITED INTO test VALUES (1, 1) LOG ERRORS REJECT LIMIT UNLIMITED SELECT * FROM dual. Что, возможно, имеет смысл, поскольку INTO могут быть для разных таблиц. - person Alex Poole; 16.11.2012
comment
@AlexPoole Спасибо, я этого не знал. Это имеет смысл ! - person Vincent Malgrat; 19.11.2012
comment
DBMS_ERRLOG отлично сработал, и мне не пришлось менять структуру запроса — спасибо, что поделились! Одна вещь, однако, разумно ли использовать этот метод с осторожностью из-за количества места/ресурсов, которые он занимает при регистрации ошибок, а не во-первых, чтобы избежать их? - person Maccath; 19.11.2012
comment
@Maccath требует дополнительной работы. Если вы выполняете много вставок и вас не интересует журнал, возможно, было бы лучше переписать ваши запросы в соответствии с Ответ @a_horse_with_no_name. - person Vincent Malgrat; 19.11.2012

arrow_upward
11
arrow_downward

Используйте оператор 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);
person a_horse_with_no_name    schedule 16.11.2012
comment
Привет, я попытался использовать следующий запрос, но получил ошибку. Я не могу сразу понять, что с ним не так, но я пока не смотрю на Oracle. ORA-00928: missing SELECT keyword: merge into DADSNBR using ( select :q0 as DADSNBR, :q1 as DAROLEID from dual union all ) t on (t.DADSNBR = access.DADSNBR and t.DAROLEID = access.DAROLEID) when not matched then insert (DADSNBR, DAROLEID) values (t.DADSNBR, t.DAROLEID) Я изменил второй DADSNBR в вашем запросе на DAROLEID, так как я предполагаю, что вы имели в виду это, но это не решило проблему. :( - person Maccath; 19.11.2012
comment
@Maccath — union all объединяет несколько операторов select - четыре в этом примере, как и в вашем исходном вопросе, - в один набор результатов. Ваша версия имеет завершающий union all; это не должно появляться после последнего подоператора select. - person Alex Poole; 19.11.2012
comment
Этот сценарий слишком запутан, у вас есть DADSNBR дважды как имя столбца и как имя таблицы, тогда ваше предложение соответствия имеет это дважды. Я удивлен, что Oracle даже не выдал ошибку, если вы даже проверили это. Наконец, ваш запрос не отвечает на вопрос. Наконец, если вы должны были использовать правильное имя таблицы в качестве OP, этот оператор по-прежнему не предотвращает вставку туда дубликатов, вы просто удаляете из источника, а не цели. - person sksallaj; 01.11.2013
comment
@sksallaj: спасибо, что указали на мои опечатки, я их исправил. Он действительно отвечает на вопрос, поскольку предотвращает вставку дубликатов с помощью одного оператора. Потому что если строки есть, то MERGE будет иметь совпадение, но поскольку нет предложения WHEN MATCHED, ничего не произойдет. Таким образом, в целевую таблицу будут вставлены только несуществующие строки. См. мой пример здесь: sqlfiddle.com/#!4/79c66/3 - person a_horse_with_no_name; 01.11.2013