The following is the stored procedure which can be used to modify the information belonging to an employee:

create or replace procedure p_emp_update (p_empno emp.empno%type,
p_ename emp.ename%type, p_sal emp.sal%type, p_deptno emp.deptno%
type) as
      Invalid_sal exception;
      Invalid_deptno    exception;
      Invalid_empno exception;
begin
      if p_sal<100 or p_sal>10000 then
            raise invalid_sal;
      end if;
      declare
            dummy_var   varchar(10);
      begin
            select 'exists' into dummy_var
            from dept
            where deptno = p_deptno;
      exception
            when no_data_found then
                  raise Invalid_deptno;
            when others then
                  raise_application_error(-20011, sqlerrm);
      end;
      update emp set
            ename=p_ename,
            sal=p_sal,
            deptno=p_deptno
      where empno=p_empno;
      if sql%notfound or sql%rowcount=0 then
            rollback;
            raise Invalid_empno;
      end if;
      commit;
exception
      when invalid_sal then
            raise_application_error(-20001, 'Salary must be in
between 100 and 10000');
      when invalid_deptno then
            raise_application_error(-20001, 'Department doesn't
exist');
      when invalid_empno then
            raise_application_error(-20001, 'Employee does not
exist');
      when others then
            raise_application_error(-20011, sqlerrm);
end;
/

In the above stored procedure, the following is the code fragment which needs some attention:

      if sql%notfound or sql%rowcount=0 then
            rollback;
            raise Invalid_empno;
      end if;

The above IF condition evaluates whether the previous DML statement (UPDATE in this scenario) updated any information successfully or not.