当前位置：文江博客话题详情

使用 Oracle 中的过程强制执行业务规则

发布于 2024-08-30 21:59:41 字数 67 浏览 7 评论 0原文

如何编写一个过程来显示一个字段的值不能高于另一个字段的值（就数字而言）。说。员工的工资不能高于经理的工资。我以前从未做过

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初熏 2024-09-06 21:59:41

在 SQL 中没有声明性的方式来强制执行这样的业务规则。所以必须用代码来完成。有很多问题，其中最重要的是确定需要执行规则的所有场景。

以下是场景：

当我们插入员工时，我们需要检查他们的薪水是否超过经理工资的90%。
当我们更新员工的工资时，我们需要检查它是否仍然不超过其经理工资的 90%。
当我们更新经理的工资时，我们需要检查它是否仍然大于其所有下属工资的 110%。
如果我们同时为经理及其下属插入记录（例如使用 INSERT ALL），我们需要确保该规则仍然得到执行。
如果我们将一名员工从一位经理调到另一位经理，我们需要确保该规则仍然得到执行。

以下是让这一切变得更加困难的事情：

强制执行这些规则涉及从我们正在操作的表中进行选择，因此由于 ORA-04088：变异表异常，我们不能使用 BEFORE ... FOR EACH ROW 触发器。
此外，从表中进行选择意味着我们不能在多用户模式下运行，因为读取一致性（否则会话 #1 可能会继续为员工加薪，而不会注意到会话 #2 目前正在对员工应用减薪）该员工的经理）。

因此，出于所有这些原因，执行此类业务规则的唯一方法是使用 API；构建一个存储过程，并且永远不要让任何进程对表进行裸 DML 访问。

以下代码块仅在更新员工的工资时强制执行该规则。有趣的地方包括：

它具有用户定义的例外情况来识别违反规则的情况。实际上，这些应该在包规范中定义，以便其他程序单元可以引用它们。
使用 SELECT ... FOR UPDATE 锁定感兴趣的行。
使用COMMIT和ROLLBACK来释放锁。在实际的实现中，这可能会以不同的方式处理（即通过调用程序）。
创建或替换过程change_emp_sal
（ emp.empno%type 中的 p_eno
, emp.sal%type 中的 p_new_sal )
是
type emp_nt 是 emp%rowtype 的表；
l_emp emp%行类型；
l_mgr emp%行类型；
l_subords emp_nt；
l_idx pls_整数；
x_mgr_not_paid_enough 异常；
pragma exception_init(x_mgr_not_paid_enough, -20000);
x_sub_paid_too_much 异常；
pragma exception_init(x_sub_paid_too_much, -20001);
开始
-- 锁定员工记录
选择*进入l_emp
来自雇员
其中 empno = p_eno
用于更新萨尔；
```
-- 锁定其经理的记录（如果有的话）
如果 l_emp.mgr 不为空
然后
    选择*进入l_mgr
    来自雇员
    其中 empno = l_emp.mgr
    用于更新；
结束如果；

-- 锁定下属的记录
select * 批量收集到 l_subords 中
来自雇员
其中 mgr = p_eno
用于更新；

-- 与经理的薪资比较
如果 l_mgr.sal 不为空
   且 l_mgr.sal < ( p_new_sal * 1.1 )
然后
    提高x_mgr_not_paid_enough；
结束如果；

-- 与下属的工资比较
对于 1..l_subords.count() 中的 i
环形
    如果 l_subords(i).sal > ( p_new_sal * 0.9 )
    然后
        l_idx := 我;
        提高x_sub_paid_too_much；
    结束如果；
结束循环；

-- 没有提出例外，所以我们可以继续
更新雇员
设置萨尔 = p_new_sal
其中 empno = p_eno;

-- 承诺释放锁
犯罪;
```
异常
当 x_mgr_not_paid_enough 时
dbms_output.put_line('错误！经理只赚'||l_mgr.sal);
回滚;
增加;
当 x_sub_paid_too_much 时
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>

所以它是有效的，就目前而言。它仅处理五种情况中的两种。重构代码以满足其他三个要求作为读者的练习。

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:

When we insert an employee we need to check whether their salary is greater than 90% of their manager's salary.
When we update an employee's salary we need to check that it still isn't greater than 90% of their manager's salary.
When we update a manager's salary we need to check that it is still greater than 110% of all their subordinates' salaries.
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.
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:

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.
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.

回复收藏 0 原文

~没有更多了~