Developing Simple PL/SQL Stored Procedures for CRUD Operations - Validate information before inserting a row using a PL/SQL stored procedure: explanation
(Page 3 of 5 )
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');
Next: How to update a row in a table using a PL/SQL stored procedure >>
More Oracle Articles
More By Jagadish Chatarji
/ 30 
