UDF在层次型数据处理中的妙用之二[MSSQL防范]

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

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

　　目前我们来看第一个例子——查找特定级别的管理员.比方,我们要提取比指定雇员高两级的管理员.这可以用两种办法实现:利用递归,大概利用循环.首先我们来看通过递归实现的筹划.本质上,递归意味着一个历程调用它自己.为了避免呈现历程无限地调用自己的情形,递归历程必须供应一个符合的终止条件.假如应用得当,递归可以轻松地办理在采取其他办法时很难办理的问题.但是,在很多情形下,递归都可以用循环来替换.普通,利用循环是一种更明智的挑选,因为递归调用比循环损耗的资源更多.

　　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.想自杀iTbulo.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.想自杀iTbulo.comw3n05