Developing Simple PL/SQL Stored Procedures for CRUD Operations - Validate information before inserting a row using a PL/SQL stored procedure: code
(Page 2 of 5 )
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.
Next: Validate information before inserting a row using a PL/SQL stored procedure: explanation >>
More Oracle Articles
More By Jagadish Chatarji
