Enforcing business rules using a Procedure in Oracle -

how write procedure shows 1 field's value cannot higher field's value, in terms of numbers. say. employee'a salary can't higher manager's salary. i've never done 1 before

there no declarative way enforce business rules in sql. has done code. there number of gotchas, not least of identifying all scenarios rule needs enforced..

here scenarios:

1. when insert employee need check whether salary greater 90% of manager's salary.
2. when update employee's salary need check still isn't greater 90% of manager's salary.
3. when update manager's salary need check still greater 110% of all subordinates' salaries.
4. if insert records simultaneously manager , subordinates (say using insert all) need make sure rule still enforced.
5. if move employee 1 manager need make sure rule still enforced.

here things make harder:

1. enforcing these rules involves selecting table manipulating cannot use before ... each row triggers, due ora-04088: mutating tables exceptions.
2. also, selecting table means cannot run in multi-user mode, because of read consistency (otherwise session #1 go ahead pay increase employee oblivious fact session #2 applying pay decrease employee's manager).

so, reasons, way enforce such business rules use api; build stored procedure , never let process have naked dml access table.

the following chunk o' code enforces rule when updating employee's salary. points of interest include:

• it has user-defined exceptions identify rule violations. these should defined in package specification, other program units can reference them.
• the use of select ... update lock rows of interest.

• the use of commit , rollback release locks. in real implementation might handled differently (i.e. calling program).

  create or replace procedure change_emp_sal ( p_eno in emp.empno%type , p_new_sal in emp.sal%type ) type emp_nt 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 employee record select * l_emp emp empno = p_eno update of sal;

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

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

here 4 employees of deptarment 50:

sql> select e.empno, e.ename, e.sal, m.ename mgr_name, m.empno mgr_no   2  emp e join emp m on (e.mgr = m.empno)   3  e.deptno = 50   4  order 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 give billy big raise, should fail...

sql> exec change_emp_sal (8060, 4500) error! manager earns 4750 begin change_emp_sal (8060, 4500); end;  * error @ line 1: ora-20000: ora-06512: @ "apc.change_emp_sal", line 67 ora-06512: @ line 1   sql> 

okay, let's give billy smaller raise, should succeed...

sql> exec change_emp_sal (8060, 4000)  pl/sql procedure completed.  sql> 

now let's try give steven swingeing pay cut, should fail...

sql> exec change_emp_sal (8061, 3500) error! subordinate earns 3500 begin change_emp_sal (8061, 3500); end;  * error @ line 1: ora-20001: ora-06512: @ "apc.change_emp_sal", line 71 ora-06512: @ line 1   sql> 

so let's give steven token pay cut, should succeed ...

sql> exec change_emp_sal (8061, 4500)  pl/sql procedure completed.  sql> 

here new pay structure...

sql> select e.empno, e.ename, e.sal, m.ename mgr_name, m.empno mgr_no   2  emp e join emp m on (e.mgr = m.empno)   3  e.deptno = 50   4  order 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 works, far goes. handles 2 of 5 scenarios. refactoring code satisfy other 3 left exercise reader.