oracle sql - 如果不存在则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8185250/
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
sql - insert if not exists
提问by Kaskade
I am having trouble with a sql query. I need to insert a row if the same row does not exist already. This is what I have so far:
我在使用 sql 查询时遇到问题。如果同一行不存在，我需要插入一行。这是我到目前为止：
DECLARE
BEGIN
FOR FOLDER_ROW IN (SELECT FOLDERID, USERID FROM DATA1.FOLDERS)
LOOP
IF NOT EXISTS (SELECT * FROM DATA1.FOLDER_USER WHERE FOLDER_ID = FOLDER_ROW.FOLDERID AND USER_ID = FOLDER_ROW.USERID)
INSERT INTO DATA1.FOLDER_USER (FOLDER_ID, USER_ID) VALUES (FOLDER_ROW.FOLDERID, FOLDER_ROW.USERID);
END LOOP;
COMMIT;
END;
I would not be very familiar with sql particularly the not exists syntax so when I execute I get the following error:
我不会很熟悉 sql 特别是不存在的语法，所以当我执行时，我收到以下错误：
ORA-06550: line 37, column 11: PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:
then and or
ORA-06550：第 37 行，第 11 列：PLS-00103：在预期以下情况之一时遇到符号“INSERT”：
然后和或
The symbol "then" was substituted for "INSERT" to continue.
符号“then”被替换为“INSERT”以继续。
ORA-06550: line 38, column 10: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if ORA-06550: line 40, column 5: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map
ORA-06550: line 38, column 10: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if ORA-06550: line 40, column 5: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map
回答by Ollie
Do it all in SQL rather than context switching into PL/SQL:
在 SQL 中完成所有操作，而不是将上下文切换到 PL/SQL：
INSERT INTO DATA1.FOLDERS
(folder_id,
user_id)
SELECT f1.folder_id,
f1.user_id
FROM DATA1.FOLDERS f1
WHERE NOT EXISTS (SELECT 1
FROM DATA1.FOLDERS f2
WHERE f1.folder_id = f2.folder_id
AND f1.user_id = f2.user_id);
回答by Mark J. Bobak
A better (Oracle specific?) solution may be the MERGE statement.
更好的（特定于 Oracle 的？）解决方案可能是 MERGE 语句。
See here for a nice explanation, with examples:
请参阅此处以获得很好的解释，并附有示例：
http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
Hope that helps.
希望有帮助。
回答by Luc M
You forgot the THEN
你忘记了THEN
IF condition THEN
...
ELSE
...
END IF;
回答by Ghasem Aladaghlou
DECLARE
N_COUNTS NUMBER;
BEGIN
select count(ID) into N_COUNTS from TABLE_NAME where ID = 1;
IF N_COUNTS=0 THEN
INSERT QUERY....
ELSE
UPDATE QUERY....
END IF;
END;
