Validate information before inserting a row using a PL/SQL stored procedure: explanation - Oracle
In this article, I shall go through a set of PL/SQL stored procedures which are very frequently used for CRUD operations. These stored procedures are mainly helpful for the developers who develop client applications (involving business logic or user interface design and programming) and who need a data layer to be implemented using PL/SQL stored procedures. The article is not targeted at pure PL/SQL developers.
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');