Oracle: как вставить INSERT, если строка не существует

Каков самый простой способ вставить строку, если она не существует, в PL/SQL (oracle)?

Мне нужно что-то вроде:

IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN
  INSERT INTO table VALUES ("jonny", null);
END IF;

Но он не работает.

Примечание. В этой таблице есть 2 поля, например имя и возраст. Но только name является PK.

Ответы

Ответ 1

INSERT INTO table
SELECT 'jonny', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = 'jonny'
                  )

Ответ 2

Предполагая, что вы находитесь на 10g, вы также можете использовать оператор MERGE. Это позволяет вставить строку, если она не существует, и игнорировать строку, если она существует. Люди склонны думать о MERGE, когда хотят сделать "upsert" (INSERT, если строка не существует и UPDATE, если строка существует), но часть UPDATE является необязательной, поэтому ее также можно использовать здесь.

SQL> create table foo (
  2    name varchar2(10) primary key,
  3    age  number
  4  );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  merge into foo a
  2    using (select 'johnny' name, null age from dual) b
  3       on (a.name = b.name)
  4   when not matched then
  5    insert( name, age)
  6*    values( b.name, b.age)
SQL> /

1 row merged.

SQL> /

0 rows merged.

SQL> select * from foo;

NAME              AGE
---------- ----------
johnny

Ответ 3

Если имя является PK, тогда просто вставьте и поймите ошибку. Причиной этого, а не проверки является то, что он будет работать даже при одновременном вставке нескольких клиентов. Если вы проверите и затем вставьте, вы должны удерживать блокировку в течение этого времени или ожидать ошибки в любом случае.

Код для этого будет что-то вроде

BEGIN
  INSERT INTO table( name, age )
    VALUES( 'johnny', null );
EXCEPTION
  WHEN dup_val_on_index
  THEN
    NULL; -- Intentionally ignore duplicates
END;

Ответ 4

Используя части ответа @benoit, я буду использовать это:

DECLARE
    varTmp NUMBER:=0;
BEGIN
    -- checks
    SELECT nvl((SELECT 1 FROM table WHERE name = 'john'), 0) INTO varTmp FROM dual;

    -- insert
    IF (varTmp = 1) THEN
        INSERT INTO table (john, null)
    END IF;

END;

Извините, я не использую полный ответ, но мне нужно IF проверить, потому что мой код намного сложнее, чем в этой таблице примеров с полями имени и возраста. Мне нужен очень четкий код. Ну, спасибо, я многому научился! Я приму ответ @benoit.

Ответ 5

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

Это то, что сработало для меня:

MERGE INTO table1 D
    USING (
        -- These are the row(s) you want to insert.
        SELECT 
        'val1' AS FIELD_A,
        'val2' AS FIELD_B
        FROM DUAL

    ) S ON (
        -- This is the criteria to find the above row(s) in the
        -- destination table.  S refers to the rows in the SELECT
        -- statement above, D refers to the destination table.
        D.FIELD_A = S.FIELD_A
        AND D.FIELD_B = S.FIELD_B
    )

    -- This is the INSERT statement to run for each row that
    -- doesn't exist in the destination table.
    WHEN NOT MATCHED THEN INSERT (
        FIELD_A,
        FIELD_B,
        FIELD_C
    ) VALUES (
        S.FIELD_A,
        S.FIELD_B,
        'val3'
    )

Ключевыми моментами являются:

Оператор SELECT внутри блока USING должен всегда возвращать строки. Если в этом запросе нет строк, никакие строки не будут вставлены или не обновлены. Здесь я выбираю из DUAL, поэтому всегда будет ровно одна строка.
Условие ON - это то, что устанавливает критерии для сопоставления строк. Если ON не имеет соответствия, то выполняется инструкция INSERT.
Вы также можете добавить предложение WHEN MATCHED THEN UPDATE, если вы хотите больше контролировать обновления.

Ответ 6

В дополнение к идеальным и достоверным ответам, данным до сих пор, есть также подсказка ignore_row_on_dupkey_index, которую вы можете использовать:

create table tq84_a (
  name varchar2 (20) primary key,
  age  number
);

insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Johnny',   77);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Pete'  ,   28);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Sue'   ,   35);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Johnny', null);

select * from tq84_a;

Подсказка описана на Таити.

Ответ 7

вы можете использовать этот синтаксис:

INSERT INTO table_name ( name, age )
select  'jonny', 18 from dual
where not exists(select 1 from table_name where name = 'jonny');

если его открыть всплывающее окно для запроса как "ввести переменную замещения", затем использовать это перед вышеуказанными запросами:

set define off;
INSERT INTO table_name ( name, age )
select  'jonny', 18 from dual
where not exists(select 1 from table_name where name = 'jonny');

Ответ 8

CTE и только CTE: -)

просто выбросьте лишние вещи. Вот почти полная и подробная форма для всех случаев жизни. И вы можете использовать любую краткую форму.

INSERT INTO reports r
  (r.id, r.name, r.key, r.param)

  -- Invoke this script from "WITH" to the end (";")
  -- to debug and see prepared values.
  WITH

  -- Some new data to add.
  newData AS(
          SELECT 'Name 1' name, 'key_new_1' key FROM DUAL
    UNION SELECT 'Name 2' NAME, 'key_new_2' key FROM DUAL
    UNION SELECT 'Name 3' NAME, 'key_new_3' key FROM DUAL
    ),
  -- Any single row for copying with each new row from "newData",
  -- if you will of course.
  copyData AS(
      SELECT r.*
      FROM reports r
      WHERE r.key = 'key_existing'
        -- ! Prevent more than one row to return.
        AND FALSE -- do something here for than!
    ),
  -- Last used ID from the "reports" table (it depends on your case).
  -- (not going to work with concurrent transactions)
  maxId AS (SELECT MAX(id) AS id FROM reports),

  -- Some construction of all data for insertion.
  SELECT maxId.id + ROWNUM, newData.name, newData.key, copyData.param
  FROM copyData
    -- matrix multiplication :)
    -- (or a recursion if you're imperative coder)
    CROSS JOIN newData
    CROSS JOIN maxId

  -- Let prevent re-insertion.
  WHERE NOT EXISTS (
      SELECT 1 FROM reports rs
      WHERE rs.name IN(
        SELECT name FROM newData
      ));

Я называю это " ЕСЛИ НЕ СУЩЕСТВУЕТ" на стероидах. Таким образом, это помогает мне, и я в основном делаю это.

Ответ 9

Вы должны использовать Merge: Например:

MERGE INTO employees e
    USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

или

MERGE INTO employees e
    USING hr_records h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

https://oracle-base.com/articles/9i/merge-statement