Validate information before inserting a row using a PL/SQL stored procedure: code - 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.
In the previous stored procedure, we didnít do any validations for the information passed to it. But validating data is the most important priority for any type of solution.
Validation of data can be achieved using several methods (at the database level). The simplest one involves using constraints. But constraints may not be suitable if we have any complex validations. The better option is to use database triggers. Database triggers are very similar to stored procedures, but they are automatically executed when the respective DML statement is issued on a table. We can use database triggers to validate the information and to perform a few calculations as well.
As we are dealing with stored procedures, I would like to deal with validations (or even calculations) using stored procedures. Let us modify the previous stored procedure to handle a few validations as follows:
create or replace procedure p_emp_insert (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; 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; insert into emp ( empno, ename, sal, deptno ) values ( p_empno, p_ename, p_sal, p_deptno ); 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 dup_val_on_index then raise_application_error(-20001, 'Employee already exists'); when others then raise_application_error(-20011, sqlerrm); end; /
The next section will give you a complete explanation for the above stored procedure.