Применение бизнес-правил с помощью процедуры в Oracle

В SQL нет декларативного способа реализовать подобные бизнес-правила. Так что это должно быть сделано с помощью кода. Существует ряд ошибок, не последней из которых является определение всех сценариев, в которых необходимо применять правило.

Вот сценарии:

1. Когда мы вставляем сотрудника, нам нужно проверить, превышает ли его зарплата 90% зарплаты их менеджера.
2. Когда мы обновляем зарплату сотрудника, нам нужно убедиться, что она по-прежнему не превышает 90% зарплаты их менеджера.
3. Когда мы обновляем зарплату менеджера, нам нужно убедиться, что она по-прежнему превышает 110% от всех зарплат его подчиненных.
4. Если мы вставляем записи одновременно для менеджера и его подчиненных (скажем, используя INSERT ALL), нам нужно убедиться, что это правило все еще применяется.
5. Если мы переводим сотрудника от одного менеджера к другому, мы должны убедиться, что это правило по-прежнему соблюдается.

Вот вещи, которые делают все это сложнее:

1. Применение этих правил включает в себя выбор из таблицы, с которой мы манипулируем, поэтому мы не можем использовать триггеры BEFORE ... FOR EACH ROW из-за ORA-04088: исключения мутирующих таблиц.
2. Кроме того, выбор из таблицы означает, что мы не можем работать в многопользовательском режиме из-за непротиворечивости чтения (в противном случае сеанс № 1 может быть продолжен с повышением заработной платы сотруднику, не обращающему внимания на тот факт, что сеанс № 2 в настоящее время применяет снижение заработной платы к руководителя этого сотрудника).

Таким образом, по всем этим причинам единственный способ обеспечить соблюдение таких бизнес-правил — использовать API; создайте хранимую процедуру и никогда не позволяйте какому-либо процессу иметь открытый доступ DML к таблице.

Следующий кусок кода применяет правило только при обновлении зарплаты сотрудника. Достопримечательности включают в себя:

• он имеет определяемые пользователем исключения для выявления нарушений правил. На самом деле они должны быть определены в спецификации пакета, чтобы другие программные модули могли ссылаться на них.
• использование SELECT ... FOR UPDATE для блокировки интересующих строк.

• использование COMMIT и ROLLBACK для снятия блокировок. В реальной реализации это может быть обработано по-другому (например, вызывающей программой).

  создать или заменить процедуру change_emp_sal (p_eno в emp.empno%type, p_new_sal в emp.sal%type) типа emp_nt — это таблица emp%rowtype; l_emp emp%rowtype; l_mgr emp%rowtype; l_subords emp_nt; l_idx pls_integer; исключение x_mgr_not_paid_enough; прагма exception_init (x_mgr_not_paid_enough, -20000); исключение x_sub_paid_too_much; прагма exception_init (x_sub_paid_too_much, -20001); begin -- заблокировать запись сотрудника select * в l_emp from emp где empno = p_eno для обновления 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;
  

  исключение, когда x_mgr_not_paid_enough then dbms_output.put_line ('Ошибка! менеджер зарабатывает только '||l_mgr.sal'); откат; поднимать; когда x_sub_paid_too_much then dbms_output.put_line('Ошибка! подчиненный зарабатывает '||l_subords(l_idx).sal); откат; поднимать; конец change_emp_sal; /

Вот четверо сотрудников отдела 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>

Давайте попробуем дать Билли большую прибавку, но это должно провалиться...

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>

Хорошо, давайте дадим Билли меньшую прибавку, которая должна сработать...

SQL> exec change_emp_sal (8060, 4000)

PL/SQL procedure successfully completed.

SQL>

Теперь давайте попробуем резко сократить зарплату Стивену, но это должно провалиться...

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>

Итак, давайте урежем Стивену символическую зарплату, и это должно получиться...

SQL> exec change_emp_sal (8061, 4500)

PL/SQL procedure successfully completed.

SQL>

Вот новая структура оплаты...

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>

Так что это работает, насколько это возможно. Он обрабатывает только два из пяти сценариев. Рефакторинг кода, удовлетворяющий остальным трем требованиям, остается читателю в качестве упражнения.