Enforcing business rules using a Procedure in Oracle

Question

How do you write a procedure which shows that one field's value cannot be higher than another field's value, in terms of numbers. Say. an employee'a salary can't be higher than his manager's salary. I've never done one before

Answer 1

There is no declarative way to enforce business rules like this in SQL. So it has to be done with code. There are a number of gotchas, not the least of which is identifying all the scenarios where the rule needs to be enforced..

Here are the scenarios:

1. When we insert an employee we need to check whether their salary is greater than 90% of their manager's salary.
2. When we update an employee's salary we need to check that it still isn't greater than 90% of their manager's salary.
3. When we update a manager's salary we need to check that it is still greater than 110% of all their subordinates' salaries.
4. If we insert records simultaneously for a manager and their subordinates (say using INSERT ALL) we need to make sure that rule is still enforced.
5. If we move an employee from one manager to another we need to make sure that rule is still enforced.

Here are the things which make all this harder:

1. Enforcing these rules involves selecting from the table we are manipulating so we cannot use BEFORE ... FOR EACH ROW triggers, due to the ORA-04088: mutating tables exceptions.
2. Also, selecting from the table means we cannot run in multi-user mode, because of read consistency (otherwise session #1 could go ahead with a pay increase to an employee oblivious to the fact that session #2 is currently applying a pay decrease to that employee's manager).

So, for all those reasons, the only way to enforce such business rules is to use an API; build a stored procedure and never let any process have naked DML access to the table.

The following chunk o' code enforces the rule just when updating an employee's salary. Points of interest include:

• it has user-defined exceptions to identify rule violations. Really these should be defined in a package specification, so other program units can reference them.
• the use of SELECT ... FOR UPDATE to lock the rows of interest.

• the use of COMMIT and ROLLBACK to release the locks. In a real implementation this might be handled differently (i.e. by the calling program).

  create or replace procedure change_emp_sal ( p_eno in emp.empno%type , p_new_sal in emp.sal%type ) is type emp_nt is table of emp%rowtype; l_emp emp%rowtype; l_mgr emp%rowtype; l_subords emp_nt; l_idx pls_integer; x_mgr_not_paid_enough exception; pragma exception_init(x_mgr_not_paid_enough, -20000); x_sub_paid_too_much exception; pragma exception_init(x_sub_paid_too_much, -20001); begin -- lock the employee record select * into l_emp from emp where empno = p_eno for update of sal;

  -- lock their manager's record (if they have one)
  if l_emp.mgr is not null
  then
      select * into l_mgr
      from emp
      where empno = l_emp.mgr
      for update;
  end if;
  
  
  -- lock their subordinates' records
  select * bulk collect into l_subords
  from emp
  where mgr = p_eno
  for update;
  
  
  -- compare against manager's salary
  if l_mgr.sal is not null
     and l_mgr.sal < ( p_new_sal * 1.1 )
  then
      raise x_mgr_not_paid_enough;
  end if;
  
  
  -- compare against subordinates' salaries
  for i in 1..l_subords.count()
  loop
      if l_subords(i).sal > ( p_new_sal * 0.9 )
      then
          l_idx := i;
          raise x_sub_paid_too_much;
      end if;
  end loop;
  
  
  -- no exceptions raised so we can go ahead
  update emp
  set    sal = p_new_sal
  where empno = p_eno;
  
  
  --  commit to free the locks
  commit;
  

  exception when x_mgr_not_paid_enough then dbms_output.put_line ('Error! manager only earns '||l_mgr.sal); rollback; raise; when x_sub_paid_too_much then dbms_output.put_line ('Error! subordinate earns '||l_subords(l_idx).sal); rollback; raise; end change_emp_sal; /

Here are the four employees of Deptarment 50:

SQL> select e.empno, e.ename, e.sal, m.ename as mgr_name, m.empno as mgr_no
  2  from emp e join emp m on (e.mgr = m.empno)
  3  where e.deptno = 50
  4  order by sal asc
  5  /

     EMPNO ENAME             SAL MGR_NAME       MGR_NO
---------- ---------- ---------- ---------- ----------
      8060 VERREYNNE        2850 FEUERSTEIN       8061
      8085 TRICHLER         3500 FEUERSTEIN       8061
      8100 PODER            3750 FEUERSTEIN       8061
      8061 FEUERSTEIN       4750 SCHNEIDER        7839

SQL>

Let's try to give Billy a big raise, which should fail...

SQL> exec change_emp_sal (8060, 4500)
Error! manager only earns 4750
BEGIN change_emp_sal (8060, 4500); END;

*
ERROR at line 1:
ORA-20000:
ORA-06512: at "APC.CHANGE_EMP_SAL", line 67
ORA-06512: at line 1


SQL>

Okay, let's give Billy a smaller raise, which should succeed...

SQL> exec change_emp_sal (8060, 4000)

PL/SQL procedure successfully completed.

SQL>

Now let's try to give Steven a swingeing pay cut, which should fail...

SQL> exec change_emp_sal (8061, 3500)
Error! subordinate earns 3500
BEGIN change_emp_sal (8061, 3500); END;

*
ERROR at line 1:
ORA-20001:
ORA-06512: at "APC.CHANGE_EMP_SAL", line 71
ORA-06512: at line 1


SQL>

So let's give Steven a token pay cut, which should succeed ...

SQL> exec change_emp_sal (8061, 4500)

PL/SQL procedure successfully completed.

SQL>

Here is the new pay structure...

SQL> select e.empno, e.ename, e.sal, m.ename as mgr_name, m.empno as mgr_no
  2  from emp e join emp m on (e.mgr = m.empno)
  3  where e.deptno = 50
  4  order by sal asc
  5  /

     EMPNO ENAME             SAL MGR_NAME       MGR_NO
---------- ---------- ---------- ---------- ----------
      8085 TRICHLER         3500 FEUERSTEIN       8061
      8100 PODER            3750 FEUERSTEIN       8061
      8060 VERREYNNE        4000 FEUERSTEIN       8061
      8061 FEUERSTEIN       4500 SCHNEIDER        7839

SQL>

So it works, as far as it goes. It only handles two of the five scenarios. Refactoring the code to satisfy the other three is left as an exercise for the reader.