UDF在层次型数据处理中的妙用之五

UDF在层次型数据处理中的妙用　　

　　要得到Andrew子树中的叶节点（不是管理员的雇员），查询命令如下：

SELECT * FROM ufn_GetSubtree(2) AS S
WHERE NOT EXISTS(SELECT * FROM Employees AS E
WHERE E.mgrid = S.empid)

　　最后一个任务是利用Listing 7显示的ufn_GetMgmtChain函数，报告通向特定雇员的管理链。


LISTING 7：获取通向特定雇员的管理链

CREATE FUNCTION ufn_GetMgmtChain
(
@empid AS int
)
RETURNS @tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL
)
AS

BEGIN

DECLARE @lvl AS int
SET @lvl = 0

INSERT INTO @tree
SELECT empid, mgrid, empname, salary, @lvl
FROM Employees
WHERE empid = @empid

WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1

INSERT INTO @tree
SELECT E.empid, E.mgrid, E.empname, E.salary, @lvl
FROM Employees AS E JOIN @tree AS T
ON E.empid = T.mgrid AND T.lvl = @lvl - 1
END

RETURN

END


　　注意ufn_GetMgmtChain函数的实现与ufn_GetSubtree函数的实现相似，区别在于现在我们顺着管理链而上，直至到达顶端。要得到通向James（empid是14）的管理链，可以执行如下查询：


SELECT * FROM ufn_GetMgmtChain(14)
ORDER BY lvl DESC


　　从SELECT查询的性能考虑，我们可以把lvl和path列的值直接加入到Employees表，然后通过触发器更新这两个列的值。采用这种方法要比在UDF中计算lvl和path值更有利于提高查询的性能。如果要使用一个用到了lvl列和path列的过滤器，ufn_GetSubtree必须先扫描整个表，计算这两个列的值。如果让lvl和path作为Employees表的列，我们可以在path列上建立一个索引，从而避免对整个表的扫描操作。然而，使用ufn_GetSubtree函数时，我们无需对Employees的结构做任何修改，而且和使用触发器时不同，我们不会增加任何维护lvl和path列的负担。UDF的出现拓展了无限的编程空间。在这里，UDF是不要求对表的结构做任何修改的强大层次型数据处理工具。