Oracle：如果一行不存在，如何插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3841441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: how to INSERT if a row doesn't exist
提问by Topera
What is the easiest way to INSERT a row if it doesn't exist, in PL/SQL (oracle)?
在 PL/SQL (oracle) 中，插入不存在的行的最简单方法是什么？
I want something like:
我想要这样的东西：
IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN
INSERT INTO table VALUES ("jonny", null);
END IF;
But it's not working.
但它不起作用。
Note: this table has 2 fields, say, nameand age. But only nameis PK.
注意：此表有 2 个字段，例如name和age。但只有名字是PK。
回答by Benoit
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'
)
回答by Justin Cave
Assuming you are on 10g, you can also use the MERGE statement. This allows you to insert the row if it doesn't exist and ignore the row if it does exist. People tend to think of MERGE when they want to do an "upsert" (INSERT if the row doesn't exist and UPDATE if the row does exist) but the UPDATE part is optional now so it can also be used here.
假设您使用的是 10g，您还可以使用 MERGE 语句。这允许您在该行不存在时插入该行，如果该行存在则忽略该行。人们在想要执行“更新插入”（如果行不存在则插入，如果行存在则更新）时往往会想到 MERGE，但 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
回答by Lou Franco
If name is a PK, then just insert and catch the error. The reason to do this rather than any check is that it will work even with multiple clients inserting at the same time. If you check and then insert, you have to hold a lock during that time, or expect the error anyway.
如果 name 是 PK，则只需插入并捕获错误。这样做而不是任何检查的原因是即使多个客户端同时插入它也能工作。如果您检查然后插入，您必须在此期间保持锁定，否则无论如何都会出现错误。
The code for this would be something like
代码类似于
BEGIN
INSERT INTO table( name, age )
VALUES( 'johnny', null );
EXCEPTION
WHEN dup_val_on_index
THEN
NULL; -- Intentionally ignore duplicates
END;
回答by Topera
Using parts of @benoit answer, I will use this:
使用@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;
Sorry for I don't use any full given answer, but I need IFcheck because my code is much more complex than this example table with name and age fields. I need a very clear code. Well thanks, I learned a lot! I'll accept @benoit answer.
抱歉，我没有使用任何完整的给定答案，但我需要IF检查，因为我的代码比这个带有名称和年龄字段的示例表复杂得多。我需要一个非常清晰的代码。好的谢谢，我学到了很多！我会接受@benoit 的回答。
回答by Malvineous
I found the examples a bit tricky to follow for the situation where you want to ensure a row exists in the destination table (especially when you have two columns as the primary key), but the primary key might not exist there at all so there's nothing to select.
我发现对于要确保目标表中存在一行的情况（尤其是当您有两列作为主键时）的情况下，这些示例有点难以遵循，但主键可能根本不存在，因此什么都没有选择。
This is what worked for me:
这对我有用：
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'
)
The key points are:
关键点是：
- The
SELECTstatement inside theUSINGblock must always return rows. If there are no rows returned from this query, no rows will be inserted or updated. Here I select fromDUALso there will always be exactly one row. - The
ONcondition is what sets the criteria for matching rows. IfONdoes not have a match then the INSERT statement is run. - You can also add a
WHEN MATCHED THEN UPDATEclause if you want more control over the updates too.
- 该
SELECT内部语句USING块必须总是返回行。如果此查询没有返回任何行，则不会插入或更新任何行。在这里我从中选择，DUAL所以总是只有一行。 - 该
ON条件是什么套用于匹配行的条件。如果ON没有匹配项，则运行 INSERT 语句。 WHEN MATCHED THEN UPDATE如果您还想对更新进行更多控制，也可以添加子句。
回答by René Nyffenegger
In addition to the perfect and valid answers given so far, there is also the ignore_row_on_dupkey_indexhint you might want to use:
除了迄今为止给出的完美有效的答案之外，还有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;
The hint is described on Tahiti.
Tahiti上描述了提示。
回答by user2667653
you can use this syntax:
您可以使用以下语法：
INSERT INTO table_name ( name, age )
select 'jonny', 18 from dual
where not exists(select 1 from table_name where name = 'jonny');
if its open an pop for asking as "enter substitution variable" then use this before the above queries:
如果它打开一个弹出窗口以询问“输入替换变量”，则在上述查询之前使用它：
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');
回答by it3xl
CTEand only CTE:-)
CTE并且只有CTE:-)
just throw out extra stuff.Here is almost complete and verbose form for all cases of life. And you can use any concise form.
只是扔掉多余的东西。这是适用于所有生活案例的几乎完整而冗长的表格。您可以使用任何简洁的形式。
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's prevent re-insertion.
WHERE NOT EXISTS (
SELECT 1 FROM reports rs
WHERE rs.name IN(
SELECT name FROM newData
));
I call it "IF NOT EXISTS" on steroids. So, this helps me and I mostly do so.
我在类固醇上称之为“如果不存在”。所以，这对我有帮助，而且我主要是这样做的。
回答by cahit beyaz
You should use Merge: For example:
您应该使用合并：例如：
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);
or
或者
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);
