Oracle PLS-00103 错误。您如何检查现有记录并根据该条件进行更新或插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1463119/
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 PLS-00103 error. How do you check for an existing record and do update or insert based on that condition?
提问by Hyman
I need to check if a record exists in the table or not from a SELECT statement. If the record exists, do an update otherwise create a record on the table. I'm trying to but i'm getting PLS-00103error.
我需要从 SELECT 语句中检查表中是否存在记录。如果记录存在，则进行更新，否则在表上创建记录。我正在尝试，但PLS-00103出现错误。
These are the errors that I'm getting when i run my code in DBVisaulzier:
这些是我在 DBVisaulzier 中运行代码时遇到的错误：
18:00:09 [DECLARE - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
:= . ( @ % ; not null range default character
18:00:09 [BEGIN - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000]
ORA-06550: line 2, column 97:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
;
18:00:09 [IF - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement
18:00:09 [ELSE - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000]
ORA-00900: invalid SQL statement
18:00:09 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement
18:00:09 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement
... 6 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 6 errors]
The following is my code:
以下是我的代码：
DECLARE a NUMBER;
BEGIN
SELECT 1
INTO a
FROM FREC_EMAIL t
WHERE t.FranchiseNo = '208254846'
AND t.ReportID = 1
AND t.id = 165;
IF a=1 THEN
UPDATE FREC_EMAIL
SET email = '[email protected]'
WHERE FranchiseNo = '208254846'
AND ReportID = 1
AND ID = 165;
ELSE
INSERT INTO FREC_EMAIL
(FranchiseNo, Email, ReportID)
VALUES
('208254846', '[email protected]', 1);
END IF;
END;
回答by APC
We should always use SQL whenever possible, and avoid using Pl/SQL unless it is strictly necessary. SQL statements perform faster, they usually require less typing and they are easier to get right.
我们应该始终尽可能使用 SQL，除非绝对必要，否则应避免使用 Pl/SQL。SQL 语句执行得更快，它们通常需要更少的输入并且更容易正确。
Since 9i Oracle has provided MERGE, a single SQL statement which executes an "upsert" statement.
由于 9i Oracle 提供了 MERGE，这是一个执行“upsert”语句的单个 SQL 语句。
MERGE INTO frec_email t
USING (SELECT '[email protected]' as email
, '208254846' as FranchiseNo
, 1 as ReportID
, 165 as ID
FROM dual ) s
ON (s.ID = t.ID)
WHEN MATCHED THEN
UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN
INSERT (t.FranchiseNo, t.Email, t.ReportID)
VALUES (s.FranchiseNo, s.Email, s.ReportID)
/
回答by Karl T.
In a pl/sql block, you can do this:
在 pl/sql 块中，您可以这样做：
update table set column=.... where.....;
更新表集 column=.... where.....;
if SQL%ROWCOUNT = 0 THEN insert...... END IF;
如果 SQL%ROWCOUNT = 0 THEN insert...... END IF;
K
钾
回答by Pop
回答by Ian Carpenter
On a side note, if you are new to Oracle, it is worth spending time getting to grips with the offical documentation. Although it might appear difficult where to start with it, Tom Kyte's Road Map is good place to get a list of must reads.
附带说明一下，如果您是 Oracle 的新手，那么花时间掌握官方文档是值得的。尽管从哪里开始可能看起来很困难，但 Tom Kyte 的路线图是获取必读清单的好地方。
Good luck!
祝你好运！
回答by torzech
I usually use the following concept, which I think is more readable than merge::
我通常使用以下概念，我认为它比合并更具可读性：
BEGIN
UPDATE FREC_EMAIL
SET email = '[email protected]'
WHERE FranchiseNo = '208254846'
AND ReportID = 1
AND ID = 165;
IF SQL%NOTFOUND THEN
INSERT INTO FREC_EMAIL
(FranchiseNo, Email, ReportID, ID)
VALUES
('208254846', '[email protected]', 1, 165);
END IF;
END;
