oracle 更新/删除不存在的行时的 PL/SQL 异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8841892/
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
PL/SQL Exceptions on Update/Delete of non-existing row
提问by Adnan Bhatti
I am learning PL/SQL these days and currently working with Procedures and exceptions using oracle HR schema.
这些天我正在学习 PL/SQL，目前正在使用 oracle HR 模式处理过程和异常。
Here is my simple procedure.
这是我的简单程序。
create or replace
PROCEDURE DEL_JOB
(p_jobid jobs.job_id%TYPE)
AS
sqle NUMBER;
sqlm VARCHAR2(300);
BEGIN
DELETE FROM JOBS
WHERE JOB_ID = UPPER(p_jobid);
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No such record');
END IF;
EXCEPTION
WHEN OTHERS THEN
sqle := SQLCODE;
sqlm := SQLERRM;
DBMS_OUTPUT.PUT_LINE('There is no job with this id that could be deleted');
DBMS_OUTPUT.PUT_LINE('Error Code ='||sqle||' Error message =' ||sqlm);
END;
When I execute this procedure the output is
当我执行此过程时，输出是
No such record
PL/SQL procedure successfully complete.
However, according to the Oracle PDF it should throw an exception and I should really get the message I entered in the exception.
但是，根据 Oracle PDF 它应该抛出异常，我应该真正得到我在异常中输入的消息。
Same thing happened with the Update on non existing record. Please advise. Thanks
对非现有记录的更新也发生了同样的事情。请指教。谢谢
采纳答案by Dan
I believe SQL%NOTFOUNDreturns truewhen no records are found. Your IFwould evaluate to true in that case, and therefore write your put_line to terminal. The SQL statement executed successfully. If you execute that SQL statement by itself from command line, you will receive 0 rows updated/deleted, not an Oracle error.
我相信在找不到记录时会SQL%NOTFOUND返回true。IF在这种情况下，您将评估为 true，因此将您的 put_line 写入终端。SQL 语句执行成功。如果您从命令行单独执行该 SQL 语句，您将收到 0 行更新/删除，而不是 Oracle 错误。
If you want to have an exception thrown, you could use RAISEinside your IFand point it to the exception in the exception block you want to have thrown.
如果你想抛出一个异常，你可以RAISE在你的内部使用IF并将它指向你想要抛出的异常块中的异常。
回答by Gerrat
There is no "exception" - the sql executed successfully. It successfully deleted every record that matched the criteria...which was 0 records. Same thing if a similar update statement was executed. You used the SQL%NOTFOUND to determine there were no records that were affected, but this does not mean there was an "exception".
没有“异常”——sql 执行成功。它成功删除了符合条件的每条记录......这是 0 条记录。如果执行了类似的更新语句，则相同。您使用 SQL%NOTFOUND 来确定没有受影响的记录，但这并不意味着存在“异常”。
Perhaps you're thinking of the NO_DATA_FOUND Exception raised if you try a "select into" clause and it doesn't find any matching records.
也许您正在考虑如果您尝试使用“select into”子句并且没有找到任何匹配的记录，则会引发 NO_DATA_FOUND 异常。
回答by Ibrahim Koubeissy
to do so you need to use
为此，您需要使用
IF SQL%ROWCOUNT = 0 THEN
RAISE no_delete;
END IF;
and define your
并定义你的
