更新/删除不存在的行的PL/SQL异常 [英] PL/SQL Exceptions on Update/Delete of non-existing row

问题描述

这些天我正在学习PL/SQL，目前正在使用oracle HR模式处理过程和异常.

I am learning PL/SQL these days and currently working with Procedures and exceptions using oracle HR schema.

这是我简单的过程.

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;

执行此过程时，输出为

  No such record
  PL/SQL procedure successfully complete. 

但是，根据Oracle PDF，它应该引发异常，我应该真正得到在异常中输入的消息.

However, according to the Oracle PDF it should throw an exception and I should really get the message I entered in the exception.

对不存在的记录进行更新时发生了同样的事情. 请指教.谢谢

Same thing happened with the Update on non existing record. Please advise. Thanks

推荐答案

我相信当找不到记录时，SQL%NOTFOUND返回true.在这种情况下，您的IF将评估为true，因此将put_line写入终端. SQL语句成功执行.如果您从命令行单独执行该SQL语句，则会收到0行已更新/删除的消息，而不是Oracle错误.

I believe SQL%NOTFOUND returns true when no records are found. Your IF would 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.

如果要引发异常，可以在IF中使用RAISE并将其指向要引发的异常块中的异常.

If you want to have an exception thrown, you could use RAISE inside your IF and point it to the exception in the exception block you want to have thrown.

这篇关于更新/删除不存在的行的PL/SQL异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！