No Image

Oracle if not exists

СОДЕРЖАНИЕ
326 просмотров
10 марта 2020

Базы данных

Это учебное пособие Oracle объясняет, как использовать Oracle условие EXISTS с синтаксисом и примерами.

Oracle условие EXISTS используется в сочетании с подзапросом (subquery), и считай «должно быть выполнено», если подзапрос возвращает хотя бы одну строку. EXISTS может использоваться в предложениях SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис условия EXISTS в Oracle/PLSQL:

Параметры или аргументы

Подзапрос — это предложение SELECT. Если подзапрос возвращает хотя бы одну запись в результирующем наборе, то условие EXISTS будет определяться как TRUE (истина), и условие EXISTS будет выполнено. Если подзапрос не возвратит никаких записей, то условие EXISTS будет определяться как FALSE (ложь), и условие EXISTS не будет выполнено.

Примечание

  • Oracle запрос SQL, использующий условие EXISTS очень неэффективен, так как подзапрос перезапускается для каждой строки в таблице внешнего запроса.
  • Есть более эффективные способы написания большинства запросов, которые бы не использовали условие EXISTS.

Пример с оператором SELECT

Рассмотрим простой пример.

Ниже приводится предложение SELECT, которое использует условие EXISTS:

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

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);

Coming late to the party, but.

Читайте также:  Склеить картинки в pdf

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:

This only inserts if the item to be inserted is not already present.

Works the same as:

may not be pretty, but it’s handy 🙂

We can combine the DUAL and NOT EXISTS to archive your requirement:

If you do NOT want to merge in from an other table, but rather insert new data. I came up with this. Is there perhaps a better way to do this?

It that code is on the client then you have many trips to the server so to eliminate that.

Insert all the data into a temportary table say T with the same structure as myFoo

This should work on other databases as well – I have done this on Sybase

It is not the best if very few of the new data is to be inserted as you have copied all the data over the wire.

I used the code above. It is long, but, simple and worked for me. Similar, to Micheal’s code.

This is an answer to the comment posted by erikkallen:

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? – erikkallen

Well, try it yourself and tell me whether you get the same error or not:

Читайте также:  Жужжит компьютер что делать

This Oracle tutorial explains how to use the Oracle EXISTS condition with syntax and examples.

The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the EXISTS condition in Oracle/PLSQL is:

Parameters or Arguments

  • Oracle SQL statements that use the Oracle EXISTS condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query’s table. There are more efficient ways to write most queries, that do not use the EXISTS condition.

Example – With SELECT Statement

Let’s look at a simple example.

The following is a SELECT statement that uses the EXISTS condition:

This Oracle EXISTS condition example will return all records from the customers table where there is at least one record in the order_details table with the matching customer_id.

Example – With SELECT Statement using NOT EXISTS

The Oracle EXISTS condition can also be combined with the NOT operator.

This Oracle EXISTS example will return all records from the customers table where there are no records in the order_details table for the given customer_id.

Example – With INSERT Statement

The following is an example of an INSERT statement that uses the EXISTS condition:

Example – With UPDATE Statement

The following is an example of an UPDATE statement that uses the EXISTS condition:

Example – With DELETE Statement

The following is an example of a DELETE statement that uses the EXISTS condition:

Комментировать
326 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock
detector