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

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

二、查找特定级别的管理员

　　现在我们来看第一个例子——查找特定级别的管理员。例如，我们要提取比指定雇员高两级的管理员。这可以用两种方法实现：使用递归，或者使用循环。首先我们来看通过递归实现的方案。本质上，递归意味着一个过程调用它自己。为了避免出现过程无限地调用自己的情况，递归过程必须提供一个合适的终止条件。如果运用得当，递归可以轻松地解决在采用其他方法时很难解决的问题。然而，在许多情况下，递归都可以用循环来替代。通常，使用循环是一种更明智的选择，因为递归调用比循环消耗的资源更多。

　　Listing 2显示了dbo.ufn_GetAncestor函数，它利用递归返回合适的管理员。dbo.ufn_GetAncestor有两个参数：@empid是雇员ID，@lvl指定了管理员在雇员之上的级别数。函数首先进行一些安全方面的检查，确保调用参数的合法性。如果参数不合法，则函数返回NULL。接下来，函数进行递归终止条件检查：如果表示管理员层次的参数是0，函数直接返回另一个表示雇员ID的参数。最后，函数执行递归调用，非常简单——函数调用自己，传递的参数是指定雇员的管理员以及级别数字减1。实际上，函数是在查找指定雇员的管理员的n-1级的上级管理员，其中n是前面指定的级别数字。当级别数字减到0或者不能找到更高级别的管理员时，递归过程立即终止。在后面这种情况下，函数返回NULL，表示请求未能满足。


LISTING 2：通过递归查找管理员

CREATE FUNCTION dbo.ufn_GetAncestor
(
@empid AS int,
@lvl AS int = 1 -- 高于指定雇员的级别数
)
RETURNS int
AS
BEGIN

IF @lvl IS NULL or @empid IS NULL or @lvl < 0
RETURN NULL

IF @lvl = 0
RETURN @empid

RETURN dbo.ufn_GetAncestor(
(SELECT mgrid FROM Employees WHERE empid = @empid),
@lvl -1)

END


　　递归有两个局限。第一个局限的影响面比较广，对大多数环境来说这个问题都存在；第二个局限是SQL Server特有的问题。影响较广的局限是指递归需要消耗大量的资源。每一个函数占有一个包含函数代码和函数变量的内存结构。随着递归调用的进行，多个函数的副本会被激活，从而消耗大量的资源。www.想自杀chinai tp ow er.comw3n05

　　SQL Server特有的局限是指，SQL Server的设计规范限制调用嵌套层次不超过32。这个限制赋予递归调用一定的弹性，同时避免了因代码质量不高可能导致的无限递归调用——例如，递归过程不带终止条件检查。因此，上面的函数只支持对参数中指定雇员之上32级之内的管理员的查询。在现实的组织结构中，级别数量超过32的情况很罕见。但是，假设组织结构超过了32层，我们必须提供一种不涉及递归调用的方案。在本文中，我利用循环技术提供了一个不带递归调用的简单方案。Listing 3显示了修改后的函数。在每一次迭代中，我提取出雇员的管理员，同时缩减@lvl参数。一旦当前的级别已经减到了0，则函数完成任务。


LISTING 3：通过循环查找管理员


CREATE FUNCTION dbo.ufn_GetAncestor2
(
@empid AS int,
@lvl AS int = 1 --高于指定雇员的级别数
)
RETURNS int
AS
BEGIN

IF @lvl IS NULL or @empid IS NULL or @lvl < 0
RETURN NULL

DECLARE @mgrid AS int
SET @mgrid = @empid

WHILE @lvl > 0 AND @mgrid IS NOT NULL
SELECT @mgrid = mgrid, @lvl = @lvl - 1
FROM Employees WHERE empid = @mgrid

RETURN @mgrid

END



　　现在你可以对上述任意函数进行测试。如果提供的参数相同，则两个函数返回的结果也相同。例如，要提取比David（他的empid是11）高两级的管理员，你可以执行如下命令：


SELECT dbo.ufn_GetAncestor(11, 2)


　　这个命令返回Janet的雇员ID，即3。如果要找出比David高两级的管理员的所有信息，可以执行如下命令：


SELECT * FROM Employees WHERE empid =
dbo.ufn_GetAncestor(11, 2)




　　要找出所有的雇员以及比他们高两级的管理员，可以执行如下命令：


SELECT E.empname AS employee, A.empname AS ancestor
FROM Employees AS E LEFT OUTER JOIN Employees AS A
ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)


　　这个查询在Employees表和它自身之间执行自我连接。连接条件保证了对每一个雇员和比他高两级的管理员进行匹配。在这里我用了一个LEFT OUTER JOIN，保证所有雇员——包括那些没有比他高两级的管理员的雇员——都包含在查询结果中。 www.想自杀chinai tp ow er.comw3n05