PLSQL入门与精通（第80章：利用递归调用进行层级关系数据的更新）

上次，介绍了阶层查询SQL的使用，也介绍了使用递归调用实现递归查询的逻辑。
这次我们介绍使用递归调用进行数据更新的例子。

我们继续用职员表（EMP表）的例子来说明。

比如有这样的一个例子：
我们假设把一定数量钱（奖金？）交给某个员工（A）。
A把这个钱的一半作为自己的奖励，加在自己的工资（SAL）里，
剩下的钱均等地分给自己的下级。
每个下级都会重复做类似以上的事情。
直到员工再没有下级的时候，那个员工会把拿到的钱全部作为自己奖励加在自己的工资（SAL）里。处理就结束了

以下是这个处理过程的代码例子。

1 CREATE OR REPLACE PROCEDURE PROC80 ( P_EMPNO IN NUMBER , P_MONEY IN NUMBER)
2 IS
3 MEM_NUMBER NUMBER；–下级人员数量
4 MEM_MONEY NUMBER；–下级人员人均分配金额
5 MY_MONEY NUMBER；–加在自己身上的金额
6 BEGIN

7 //
8 --查询自己所属的下级的数量
9 //
10 SELECT COUNT (*) INTO MEM_NUMBER FROM EMP WHERE MGR = P_EMPNO;

11 //
12 --如果没有下级的话，将收到的全部金额（P_MONEY）加到自己的工资（SAL）里
13 //
14 IF MEM_NUMBER = 0 THEN
15 UPDATE EMP SET SAL = SAL + P_MONEY WHERE EMPNO = P_EMPNO;

16 //
17 --如果有下级的话，自己的奖金份额减半，剩下的平均分配给下级
18 //
19 ELSE

20 --P_MONEY的一半属于自己
21 MY_MONEY := P_MONEY / 2;

22 --将金额加在自己的工资（SAL列）上
23 UPDATE EMP SET SAL = SAL + MY_MONEY WHERE EMPNO = P_EMPNO;

24 --将剩下的金额除以下级的人数，计算每个下级的分配额
25 MEM_MONEY := ( P_MONEY - MY_MONEY ) / MEM_NUMBER;

26 FOR REC IN（SELECT * FROM EMP WHERE MGR=P_EMPNO）LOOP --循环游标
27 --给每个下级分配金额，从每个下级角度上需要进行同样的事情
28 PROC80(REC.EMPNO，MEM_MONEY)；–递归调用
29 END LOOP;
30 END IF;
31 END PROC80;
/

程序已创建。

简单地解说一下。

首先，要取得自己的下级的人员的数量。（第10行）
如果没有下级的话，就把参数指定的金额（P_MONEY）全部加到自己的工资（SAL列）里。（第14～15行）
然后，希望大家注意的是，如果没有下级的话，就没有进行递归调用，所以递归调用就会停止。
我们前边有说明，递归调用要连续进行，必须有个终止递归调用的时机，否则就成死循环了。
在这个例子里边，递归调用的重视条件就是但员工在没有下级的职员的时候进行终止调用的。

相反，如果有下级的话，递归调用会继续。
首先，有下级的话，把P_MONEY的一半作为自己的份额，加到工资（SAL列）里。（第21～23行）
然后计算每个下级的分配额（第25行），

给每个下级分配一个金额，在那个下级的角度做以上同样的事情，也就是说递归调用（PROC80程序调用PROC80程序）。（第28行）

这样更新处理就会一直往下层去处理，直到再没有下一层为止。

接下来马上进行确认一下执行结果。
确认结果的时候，我们需要将该程序更新前后的值进行比较，
所以事先将当前的员工表（EMP表）COPY一份到EMP2的表里。

CREATE TABLE EMP2 AS SELECT * FROM EMP；–当前的员工表（EMP表）COPY一份到EMP2的表里。

表创建成功。

然后，以职员号码7839的职员（职员名为KING）为起点，分配的金额总数为30000，
调用上述程序PROC80。

EXEC PROC80(7839,30000)

PL/SQL过程成功完成。

将EMP2表（更新前的表）和EMP表（更新后的表）更新前后的值进行查询比较，
来看看没层次的每个人变动的金额差异。

通过以下这个查询，可以查询每个层次的员工以及他们工资的增加额度：

SELECT
LPAD( ’ ’ ,5*( LEVEL -1), ’ ’ ) || LEVEL || ’ ’ || RPAD( AFTER.ENAME,10, ’ ’ ) || ’ ’ || TO_CHAR( AFTER.SAL - BEFORE.SAL）AS“SAL列的增加额”
FROM
EMP2 BEFORE,
EMP AFTER
WHERE
BEFORE.EMPNO = AFTER.EMPNO
START WITH AFTER.EMPNO = 7839
CONNECT BY PRIOR AFTER.EMPNO = AFTER.MGR
ORDER SIBLINGS BY AFTER.ENAME
/

SAL列的增加额

1 KING 15000
2 BLAKE 2500
3 ALLEN 500
3 JAMES 500
3 MARTIN 500
3 TURNER 500
3 WARD 500
2 CLARK 2500
3 MILLER 2500
2 JONES 2500
3 FORD 625
4 SMITH 625
3 SCOTT 625
4 ADAMS 625

选择了14行。

我们从表面上来看看结果，
首先，以KING为起点，给了30000总金额，KING的工资增加额是其中一半为15000。
而且KING的直属下级是BLAKE、CLARK、JONES三人，这三人平均分配了剩下的15000。
每人5000的分配额，其中5000的一半（2500）加在自己的SAL列里，剩下的（每个人2500）继续分配给下级，
看来结果都是正确的。证明数据被正确地更新了。

接下来，我们再看看增加的金额的总和是否是是30000：

(15000+2500+500+500+500+500+500+2500+2500+2500+625+625+625+625=30000)

整体也是正确的。

我们日常作业中递归调用并不太常见，但如果在阶层关系中可以使用递归调用来描述他们的关系。

本次到此为止。