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

UDF在层次型数据处理中的妙用(转)[@more@]

　　一、概述

　　

　　本文介绍如何在管理层次结构的数据时使用SQL Server UDF（用户定义函数），以及UDF在层次数据处理方面的强大功能。在本文例子中，我要用到一个Employees表。Employees表中的每一个雇员有一个mgrid属性，该属性保存的是雇员所属的管理员ID。每一个管理员同时也是雇员，从而形成一种层次关系。Listing 1显示了创建Employees表和插入数据的脚本。

　　

　　

　　

　　LISTING 1：Employees表结构和数据

　　

　　CREATE TABLE Employees(

　　empid int NOT NULL,

　　mgrid int NULL,

　　empname varchar(25) NOT NULL,

　　salary money NOT NULL,

　　CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),

　　CONSTRAINT FK_Employees_mgrid_empid

　　FOREIGN KEY(mgrid)

　　REFERENCES Employees(empid))

　　

　　CREATE INDEX idx_nci_mgrid ON Employees(mgrid)

　　

　　INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)

　　INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)

　　INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)

　　INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)

　　INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)

　　INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)

　　INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)

　　INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)

　　INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)

　　INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)

　　INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)

　　INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)

　　INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)

　　INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)

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

　　

　　

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

　　

　　

　　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特有的问题。影响较广的局限是指递归需要消耗大量的资源。每一个函数占有一个包含函数代码和函数变量的内存结构。随着递归调用的进行，多个函数的副本会被激活，从而消耗大量的资源。

　　

　　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，保证所有雇员――包括那些没有比他高两级的管理员的雇员――都包含在查询结果中。

　　三、计算合计数和子树深度

　　

　　

　　现在来看几个需要窍门才能解决的问题――如果不用递归就很难解决的问题。假定我们想要得到从指定管理员开始的特定子树下所有雇员的薪水总额。Listing 4显示了如何用dbo.ufn_GetSubtreeSalary函数来完成这个任务。

　　

　　

　　LISTING 4：计算子树的合计数

　　

　　CREATE FUNCTION dbo.ufn_GetSubtreeSalary

　　(

　　@mgrid AS int

　　)

　　RETURNS int

　　AS

　　BEGIN

　　

　　RETURN (SELECT Salary

　　FROM Employees WHERE empid = @mgrid) +

　　CASE

　　WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @mgrid) THEN

　　(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))

　　FROM Employees

　　WHERE mgrid = @mgrid)

　　ELSE 0

　　END

　　END

　　

　　

　　

　　

　　注意dbo.ufn_GetSubtreeSalary函数非常简短；虽然我们面临的任务很复杂，但函数只包含一个RETURN语句。dbo.ufn_GetSubtreeSalary函数查询指定管理员的薪水，再加上每一个管理员直接下属所包含子树的薪水总和。现在，试着用这个新的函数计算从Janet（empid是3）开始的子树的薪水总额，答案将是20000：

　　

　　

　　SELECT dbo.ufn_GetSubtreeSalary(3)

　　

　　

　　

　　

　　用类似的方法可以计算子树的深度，如Listing 5所示。dbo.ufn_GetSubtreeDepth函数也返回一个CASE表达式的结果，但两者的代码有所不同。

　　

　　

　　LISTING 5：计算子树的深度

　　

　　CREATE FUNCTION dbo.ufn_GetSubtreeDepth

　　(

　　@mgrid AS int

　　)

　　RETURNS int

　　AS

　　BEGIN

　　

　　RETURN CASE

　　WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @mgrid)

　　THEN 1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))

　　FROM Employees

　　WHERE mgrid = @mgrid)

　　WHEN EXISTS(SELECT * FROM Employees WHERE empid = @mgrid)

　　THEN 1

　　ELSE NULL

　　END

　　END

　　

　　

　　

　　

　　CASE表达式先检查指定的管理员是否有下属。如果有下属，函数返回1加上该管理员直接下属子树的最大深度――因此，这里出现了递归；如果指定的管理员没有下属，CASE表达式确定该管理员是否存在。如存在，则函数返回1（对于没有下属的管理员，他的深度是1）；如果该管理员不存在，则CASE表达式返回NU

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8403220/viewspace-954611/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8403220/viewspace-954611/