Каков самый простой способ вставить строку, если она не существует, в PL/SQL (oracle)?
Мне нужно что-то вроде:
IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN
INSERT INTO table VALUES ("jonny", null);
END IF;
Но он не работает.
Примечание. В этой таблице есть 2 поля, например имя и возраст. Но только name является PK.
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'
)
Предполагая, что вы находитесь на 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
Если имя является PK, тогда просто вставьте и поймите ошибку. Причиной этого, а не проверки является то, что он будет работать даже при одновременном вставке нескольких клиентов. Если вы проверите и затем вставьте, вы должны удерживать блокировку в течение этого времени или ожидать ошибки в любом случае.
Код для этого будет что-то вроде
BEGIN
INSERT INTO table( name, age )
VALUES( 'johnny', null );
EXCEPTION
WHEN dup_val_on_index
THEN
NULL; -- Intentionally ignore duplicates
END;
Используя части ответа @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.
Я нашел примеры немного сложными, чтобы следить за ситуацией, когда вы хотите, чтобы строка существовала в таблице назначения (особенно когда у вас есть два столбца в качестве первичного ключа), но первичный ключ может вообще не существовать там поэтому там ничего не выбрать.
Это то, что сработало для меня:
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, если вы хотите больше контролировать обновления.В дополнение к идеальным и достоверным ответам, данным до сих пор, есть также подсказка 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;
Подсказка описана на Таити.
вы можете использовать этот синтаксис:
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');
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
));
Я называю это " ЕСЛИ НЕ СУЩЕСТВУЕТ" на стероидах. Таким образом, это помогает мне, и я в основном делаю это.
Вы должны использовать 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);