Oracle sql вставить если не существует
операция UPSERT обновляет или вставляет строку в таблицу, в зависимости от того, есть ли в таблице строка, соответствующая данным:
поскольку Oracle не имеет конкретного оператора UPSERT, каков наилучший способ сделать это?
альтернатива слиянию ("старомодный способ"):
на инструкция слияния объединяет данные между двумя таблицами. Использование DUAL позволяет использовать эту команду. Обратите внимание, что это не защищены от параллельного доступа.
еще одна альтернатива без исключения check:
- вставить, если не существует
- обновление:
в качестве примера, вот как код Grommit может быть обернут в цикл, чтобы сделать его безопасным при одновременном запуске:
Б. Н. В режим транзакции SERIALIZABLE , который я не рекомендую кстати, вы можете столкнуться ORA-08177: не удается сериализовать доступ для этой транзакции исключения.
Примечание относительно двух решений, которые предполагают:
1) вставить, если исключение после обновления
2) Обновление, если sql%rowcount = 0, то вставьте
вопрос о том, вставлять или обновлять первым, также зависит от приложения. Вы ожидаете больше вставок или больше обновлений? Тот, кто, скорее всего, добьется успеха, должен идти первым.
Если вы выберете неправильный, вы получите кучу ненужных индексов. Не огромная сделка, но все же стоит подумать.
Я использую первый образец кода в течение многих лет. Заметьте, что вы не нашли, а не сосчитали.
код ниже, возможно, новый и улучшенный код
в первом примере обновление выполняет поиск индекса. Он должен, чтобы обновить правую строку. Oracle открывает неявный курсор, и мы используем его, чтобы обернуть соответствующую вставку, поэтому мы знаем, что вставка произойдет только тогда, когда ключ не существует. Но insert является независимой командой, и это должен сделать второй поиск. Я не знаю внутреннюю работу команды merge, но поскольку команда является единым блоком, Oracle может выполнить правильную вставку или обновление с помощью одного поиска индекса.
Я думаю, что слияние лучше, когда у вас есть некоторая обработка, которая должна быть сделана, что означает получение данных из некоторых таблиц и обновление таблицы, возможно, вставка или удаление строк. Но для случая с одной строкой вы можете рассмотреть первый случай, так как синтаксис более распространен.
I need to be able to run an Oracle query which goes to insert a number of rows, but it also checks to see if a primary key exists and if it does, then it skips that insert. Something like:
Is this at all possible with Oracle?
Bonus points if you can tell me how to do this in PostgreSQL or MySQL.
10 Answers 10
Coming late to the party, but.
With oracle 11.2.0.1 there is a semantic hint that can do this: IGNORE_ROW_ON_DUPKEY_INDEX
UPDATE: Although this hint works (if you spell it correctly), there are better approaches which don't require Oracle 11R2:
First approach—direct translation of above semantic hint:
Second aproach—a lot faster than both above hints when there's a lot of contention:
Looking for an alternative of how I was tackling that situation, I came across your answer that reinforces that my approach was correct. I implemented the second approach, and it is clear and fast! +1
@user3454439 the speedup depends on your use case; if you don't have as much contention then you may not even care. Profile!
The statement is called MERGE. Look it up, I'm too lazy.
Beware, though, that MERGE is not atomic, which could cause the following effect (thanks, Marius):
SESS2: insert into t1 values(2, 2);
Again, without locking the table(or the master record first), there is a race. This method always requires using a temp table. I wouldn't say it's wrong, but sometimes can be just too much.
Yes, MERGE itself is atomic. But. Sess1: INSERT pk=1 INTO myFoo; Sess2: MERGE INTO myFoo d USING tmpTable s ON (d.pk = s.pk). Sess1: COMMIT; Sess2: ORA-00001; For cases when the number of rows inserted is low, it really doesn't make sense to use a temp table. Everything has its price, and CREATE TABLE and MERGE don't come cheap(look at the required latches/locks and the like).
You don't need a temp table. If you only have a few rows, (SELECT 1 FROM dual UNION SELECT 2 FROM dual) will do. Why would your example give ORA-0001? Wouldn't merge take the update lock on the index key and not continue until Sess1 has either committed or rolled back?
Erik, please see the answer below. There wasn't enough space to post it as a comment, nor was any formatting available.
Мне нужно иметь возможность запускать запрос Oracle, который идет, чтобы вставить несколько строк, но он также проверяет, существует ли первичный ключ, и если да, то он пропускает эту вставку. Что-то вроде:
возможно ли это вообще с Oracle?
бонусные баллы, если вы можете сказать мне, как это сделать в PostgreSQL или MySQL.
оператор называется MERGE. Посмотри, я слишком ленив.
остерегайтесь, однако, что слияние не является атомарным, что может вызвать следующий эффект (спасибо, Мариус):
SESS2: insert into t1 values(2, 2);
поздно пришел на вечеринку, но.
С oracle 11.2.0.1 есть семантический намек что можно сделать это: IGNORE_ROW_ON_DUPKEY_INDEX
обновление: хотя эта подсказка работает (если правильно пишется), есть лучше всего подходит которые не требуют Oracle 11R2:
первый подход-прямой перевод выше смысловой подсказка:
второй aproach-a много быстрее, чем оба выше подсказки, когда есть много разногласий:
это только вставляет, если вставляемый элемент еще не присутствует.
работает так же, как:
может быть не очень красиво, но это удобно:)
мы можем объединить двойной и не существует, чтобы архивировать ваше требование:
Если вы не хотите объединяться из другой таблицы, а скорее вставлять новые данные. Я придумал это. Может быть, есть лучший способ сделать это?
Это код на клиенте, тогда у вас есть много поездок на сервер, чтобы устранить это.
вставьте все данные в таблицу temportary, скажем T с той же структурой, что и myFoo
Это должно работать и на других базах данных - я сделал это на Sybase
это не самое лучшее, если очень мало новых данных будет вставлено, поскольку вы скопировали все данные по проводу.
я использовал код выше. Это долго, но, просто и сработало для меня. Похоже на код Майкла.
Это ответ на комментарий, опубликованный erikkallen:
вам не нужна временная таблица. Если вы есть только несколько строк, (выберите 1 из двойное соединение выберите 2 от двойного) будет делать. Почему ваш пример дает Ора-0001? Не сольют взять обновить блокировку на ключе индекса, а не продолжайте, пока Sess1 либо фиксация или откат? – erikkallen
Ну, попробуйте сами и скажите, получаете ли вы ту же ошибку, или не:
SESS2: insert into t1 values(2, 2);
Если ваша таблица "независима" от других (я имею в виду, что она не вызовет каскадного удаления или не установит отношения внешних ключей к нулю), хорошим трюком может быть сначала удалить строку, а затем вставить ее снова. Это могло бы выглядеть так:
удалить из MyTable, где prop1 = 'aaa'; / / предполагая, что он выберет не более одной строки!
каков самый простой способ вставить строку, если она не существует, в PL/SQL (oracle)?
Я хочу что-то вроде:
но это не работает.
Примечание: эта таблица имеет 2 поля, скажем,имя и возраст. Но только имя ПК.
предполагая, что вы находитесь на 10g, вы также можете использовать оператор MERGE. Это позволяет вставить строку, если она не существует и игнорировать строку, если она существует. Люди склонны думать о слиянии, когда они хотят сделать "upsert" (вставить, если строка не существует, и обновить, если строка существует), но часть обновления теперь необязательна, поэтому ее также можно использовать здесь.
Если имя является ПК, то просто вставьте и поймайте ошибку. Причина этого, а не любая проверка заключается в том, что она будет работать даже с несколькими клиентами, вставляющими одновременно. Если вы проверяете и затем вставляете, вы должны удерживать блокировку в течение этого времени или ожидать ошибки в любом случае.
код для этого будет чем-то вроде
используя части ответа @benoit, я буду использовать это:
Извините, что я не использую полный ответ, но мне нужно IF проверьте, потому что мой код намного сложнее, чем эта примерная таблица с полями имени и возраста. Мне нужен очень четкий код. Ну спасибо, я многому научился! Я приму ответ @benoit.
Я нашел примеры немного сложно следить за ситуацией, когда вы хотите обеспечить ряд существует в целевой таблице (особенно, когда у вас есть два столбца в качестве первичного ключа), но первичный ключ может не существовать вообще, так что нечего выбрать.
вот что сработало для меня:
- на SELECT инструкция USING блок всегда должен возвращать строки. Если строки не возвращаются в этом запросе строки вставляться или обновляться не будут. Здесь я выбираю из DUAL таким образом, всегда будет ровно одна строка.
- на ON условие-это то, что задает критерии для сопоставления строк. Если ON не имеет соответствия, затем выполняется инструкция INSERT.
- вы также можете добавить WHEN MATCHED THEN UPDATE предложение, если вы хотите больше контроля над обновлениями тоже.
подсказка описана на Таити.
вы можете использовать следующий синтаксис:
если его открыть pop для запроса как "введите переменную подстановки" , то используйте это перед вышеуказанными запросами:
CTE и только CTE : -)
просто выбросить лишние вещи. вот почти полная и многословная форма для всех случаев жизни. И вы можете использовать любую краткую форму.
Я называю это "ЕСЛИ НЕ СУЩЕСТВУЕТ" на стероидах. Это помогает мне, и я в основном делаю это.
Каков самый простой способ ВСТАВИТЬ строку, если она не существует, в PL/SQL (оракул)?
Я хочу что-то вроде:
Но это не работает.
Примечание: в этой таблице есть 2 поля, скажем, имя и возраст . Но только имя ПК.
Ожидаете ли вы, что оператор INSERT вообще будет необходим (т.е. строки вообще не будет существовать)? Или что ряд вообще будет существовать?
dual — это фиктивная таблица в Oracle с одним столбцом и одной строкой. Это плохо (в SQLite можно просто Select без from, в Oracle приходится использовать dual при выборе из ниоткуда).
-1 Это не будет работать, когда несколько сеансов пытаются вставить одну и ту же строку одновременно - ни один из них не увидит данные другого сеанса, пока не зафиксирует, а в это время уже слишком поздно. Лучшее решение — применить уникальное ограничение.
Поздний комментарий: @JeffreyKemp, есть варианты использования, которые не требуют беспокойства об одновременных сеансах.
@JustinSkiles, эти варианты использования являются особыми случаями и не имеют отношения к этому вопросу. Когда разработчик решает, что базовая функция СУБД (в данном случае параллелизм) не должна «беспокоить», это именно то, что позже, скорее всего, укусит их клиентов.
Предполагая, что у вас 10 г, вы также можете использовать оператор MERGE. Это позволяет вам вставить строку, если она не существует, и игнорировать строку, если она существует. Люди склонны думать о MERGE, когда хотят выполнить «upsert» (INSERT, если строка не существует, и UPDATE, если строка существует), но часть UPDATE теперь необязательна, поэтому ее также можно использовать здесь.
Если имя является ПК, то просто вставьте и перехватите ошибку. Причина сделать это, а не какую-либо проверку, заключается в том, что она будет работать даже при одновременной вставке нескольких клиентов. Если вы проверите, а затем вставите, вам придется удерживать блокировку в течение этого времени или все равно ожидать ошибки.
Код для этого будет примерно таким
Код: BEGIN INSERT INTO table VALUES('jonny', null);EXCEPTION WHEN sqlcode != -1 THEN RAISE;END; / sqlcode = -1, когда ORA-00001
Имеет ли смысл пробовать вставку и перехватывать исключение, зависит от того, как часто вы ожидаете, что INSERT будет успешным. Если в 99% случаев вы вставляете неповторяющееся значение, а ошибка возникает только в 1% случаев, перехват и игнорирование исключения — хороший вариант. Если в 99 % случаев строка уже существует, перехват исключения может быть проблематичным с точки зрения производительности.
Кроме того, метод слияния работает с несколькими строками во вставке. выберите, что это не так. (Я знаю, что ОП делал одну строку в качестве примера, но для этого (и проблемы с производительностью, указанной Джастином Кейвом) я думаю, что слияние - лучшее решение.
Читайте также: