Oracle 存储过程 删除表记录时删除不存在的记录也是显示删除成功

Question

create or replace procedure delDept(p_deptno in dept.deptno%type) is
begin
delete from dept where deptno=p_deptno;
dbms_output.put_line('部门删除成功...');
exception when others then
dbms_output.put_line('部门删除失败...');
end;

Answer 1

删除不存在的数据并不会促发Oracle的异常
对于delete from dept where deptno=p_deptno;
判断是否有数据被删除可以用sql%rowcount，sql%notfound 来判断
if sql%rowcount = 0 then
dbms_output.put_line('部门删除失败...');
end if;
或者
if sql%notfound then
dbms_output.put_line('部门删除失败...');
end if;

Answer 2

create or replace procedure delDept(p_deptno in dept.deptno%type) is
EmpCount NUMBER; --影响的记录数
begin
delete from dept where deptno=p_deptno;
EmpCount := SQL%ROWCOUNT;
if(EmpCount <>0)
then
dbms_output.put_line('部门删除成功...');
exception when others then
dbms_output.put_line('部门删除失败...');
end;