Question

我正在学习PL/SQL这些日子,目前正在使用人权专册的程序与例外。

这里是我的简单程序。

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,它应当放弃一个例外,我应该真正获得我作为例外所传递的信息。

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

Answer 1

页: 1 没有记录。您的<代码>IF将对这一案例作出评价,并因此写上您的行文。卡福尔克声明成功执行。如果你自己从指挥线执行该表,你将获得更新/删除的0行,而不是甲骨质的错误。

如果你想要放弃一个例外,请在您的<条码>内使用,并注明你想要投的例外情况。

Answer 2

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".

也许,你会重新思考一下N_DATA_FOUND 如果你尝试“选择”条款,则提出例外,没有找到任何对应的记录。

Answer 3

为此,你需要使用

IF SQL%ROWCOUNT = 0 THEN
    RAISE no_delete;
END IF;

友情链接