This section explains the code provided in the previous section.  Let me start with the following:

      Invalid_sal       exception;
      Invalid_deptno    exception;

“Invalid_Sal” and “Invalid_Deptno” are two user-defined exceptions which can be raised within our logic according to our requirements.  The following is the code fragment which checks for the validity of salary:

      if p_sal<100 or p_sal>10000 then
            raise invalid_sal;
      end if;

In the above code fragment, if the salary is not in between 100 and 10000, it raises the user-defined exception “Invalid_sal.”  Once it is raised, the control (or flow of execution) jumps to the exception section, and gets the following to be executed:

      when invalid_sal then
            raise_application_error(-20001, 'Salary must be in
between 100 and 10000');

The above statement returns a user-defined message to the application which called the stored procedure.

Coming to the department validation, I need to check whether the given department number exists in the “dept” table or not.  If it is not available, I would like to raise an exception and send a message back to the user.  The following is the PL/SQL block which is nested into the main block to handle the same:

      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;

The above checks to see whether the department exists or not.  If it doesn’t exist, the SELECT statement fails.  If it fails, “no_data_found” gets raised, which in turn fires the “invalid_deptno” exception.  If any other error occurs, it simply raises a default error.

If the “Invalid_deptno” exception is raised, control (or flow of execution) jumps to the following:

      when invalid_deptno then
            raise_application_error(-20001, 'Department doesn''t exist');