Validate information before inserting a row using a PL/SQL stored procedure: code - Oracle

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.