PLSQL入门与精通（第79章：递归调用与层级关系的例子）

上次，解说了一下递归调用的简单的例子。
上次的例子函数只是单纯的返回计算结果的，这次递归调用例子改用数据库的表。

这个例子是关于阶层查询相关内容。表中包含阶层（上下级）关系，我们通过递归调用把阶层关系显示出来。

例如，员工表（EMP表）有三个字段：员工编号EMPNO，员工姓名ENAME，该员工所属的上级员工编号MGR。
这个表中包含了上下级的关系,一个上级有多个下级。

用以下的是阶层查询的SQL就可以吧他们的阶层关系查询出来：

SELECT LEVEL , EMPNO, ENAME, MGR
FROM EMP
START WITH EMPNO = 7839
CONNECT BY PRIOR EMPNO = MGR
/

LEVEL EMPNO ENAME MGR

---

1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
4 7876 ADAMS 7788
3 7902 FORD 7566
4 7369 SMITH 7902
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
2 7782 CLARK 7839
3 7934 MILLER 7782

选择了14行。

“START WITH”指定了阶层查询的起点，“CONNESCT BY PRIOR”表示关联的阶层。
LEVEL是伪列，表示阶层结构的层次深度。
上面的例子是阶层查询的起点是职员编号7839号，从他开始把他所有关联下级都找出来。

吧查询结果改成如下的显示层次结构，更容易理解他们之间的的阶层关系。

SELECT LPAD( ’ ’ ,5*( LEVEL -1), ’ ’ ) || LEVEL || ’ ’ ||
EMPNO || ’ ’ || ENAME || ’ ’ || MGR
FROM EMP
START WITH EMPNO = 7839
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME
/

LPAD( ‘’ ,5*( LEVEL -1), ‘’ )|| LEVEL || ‘’ ||EMPNO|| ‘’ ||ENAME|| ‘’ ||MGR

1 7839 KING
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7900 JAMES 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7521 WARD 7698
2 7782 CLARK 7839
3 7934 MILLER 7782
2 7566 JONES 7839
3 7902 FORD 7566
4 7369 SMITH 7902
3 7788 SCOTT 7566
4 7876 ADAMS 7788

选择了14行。

这样上级和下级的关系变得直观易懂。

另外，查询的最后追加了「ORIDER SIBLINGS BY ENAME」，指定了排序字段。
这个排序并不是对整体结果进行排序，而是在同一阶层的兄弟（SIBLINGS）之间进行排序的。
也就是说在同一级别的阶层数据中，员工名称（ENAME）的顺序都是升序。
不加SIBLINGS关键字的话，整体结果数据就会被排序，这样的话显示的等级顺序就有点乱不容易理解。

这样的显示结果我们也可以使用递归调用的函数来实现。

假如这个程序函数的ID是“PROC79”，调用这个程序我们想想达到如下的显示结果：

EXEC PROC79（7839） --调用函数，参数是7839作为起点，把它所有的下级关系全部展现出来：
1 7839 KING
…2 7698 BLAKE 7839
…3 7499 ALLEN 7698
…3 7900 JAMES 7698
…3 7654 MARTIN 7698
…3 7844 TURNER 7698
…3 7521 WARD 7698
…2 7782 CLARK 7839
…3 7934 MILLER 7782
…2 7566 JONES 7839
…3 7902 FORD 7566
…4 7369 SMITH 7902
…3 7788 SCOTT 7566
…4 7876 ADAMS 7788

这个PROC79程序的源代码如下所示：

CREATE OR REPLACE
PROCEDURE PROC79( P_EMPNO IN EMP.EMPNO%TYPE, P_LEVEL IN NUMBER DEFAULT 1)

/************************************************************************/
–用递归调用进行层级查询的函数
/************************************************************************/
IS
REC EMP%ROWTYPE;
BEGIN

/**************************************************************/
--取得指定员工的行
/**************************************************************/
SELECT * INTO REC FROM EMP WHERE EMPNO = P_EMPNO;

/**************************************************************/
--把该员工的信息按照缩进的方式显示出来
/**************************************************************/
DBMS_OUTPUT.PUT_LINE(LPAD( '.' ,5*(P_LEVEL-1), '.' ) || P_LEVEL || ' ' ||
REC.EMPNO || ' ' || REC.ENAME || ' ' || REC.MGR);

/***********************************************************************/
--对该员工的下一级进行同样的查询处理
/**********************************************************************/
FOR REC IN ( SELECT * FROM EMP WHERE MGR = P_EMPNO ORDER BY ENAME) LOOP
    PROC79(REC.EMPNO，P_LEVEL+1）；--调用（递归调用）自己的副本（PROC79）
END LOOP;

END PROC79;
/

程序已创建。

首先，在这个过程的参数中有P_LEVEL，这个是阶层结构的层级深度，
他和阶层查询SQL的伪列LEVEL的作用相同。因为有默认值（=1），所以调用时没有或者没有指定这个参数的话，
这个值就是1。

接着按照职员名的顺序进行（ORDER BY ENAME）取得下级数据，该员工的下级的取得使用的是FOR循环，
对各自的下级的取得用递归调用反复进行同样的处理，此时，设置了一个阶层层次深度的参数（P_LEVEL+1）。
这样各级阶层的深度和缩进关系就会表示出来

如果下级员工没有的话，递归调用就结束了，再返回到上面的阶层继续进行下一个数据的处理。

这个就是表阶层关系的递归调用的例子。