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

最新推荐文章于 2024-09-21 01:10:29 发布

zgqtxwd

最新推荐文章于 2024-09-21 01:10:29 发布

阅读量242

点赞数

文章标签： path tree 脚本测试

本文链接：https://blog.csdn.net/zgqtxwd/article/details/2338213

版权

四、查询管理链

　　至此为止，前面的所有例子都是用只返回一个值的数值型用户定义函数（UDF）。现在，我们来看看如何使用返回表的UDF。这类函数的返回值是一个记录集或表（即，可用于FROM子句）。例如，在处理层次结构的数据时，一个常见的需求是返回从指定管理员开始的整棵子树。

LISTING6：获得整棵子树

CREATEFUNCTIONufn_GetSubtree
(
@mgridASint
)
RETURNS@treetable
(
empidintNOTNULL,
mgridintNULL,
empnamevarchar(25)NOTNULL,
salarymoneyNOTNULL,
lvlintNOTNULL,
pathvarchar(900)NOTNULL
)
AS

BEGIN

DECLARE@lvlASint,@pathASvarchar(900)
SELECT@lvl=0,@path='.'

INSERTINTO@tree
SELECTempid,mgrid,empname,salary,
@lvl,'.'+CAST(empidASvarchar(10))+'.'
FROMEmployees
WHEREempid=@mgrid

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

INSERTINTO@tree
SELECTE.empid,E.mgrid,E.empname,E.salary,
@lvl,T.path+CAST(E.empidASvarchar(10))+'.'
FROMEmployeesASEJOIN@treeAST
ONE.mgrid=T.empidANDT.lvl=@lvl-1
END

RETURN

END

　　Listing6显示了创建ufn_GetSubtree函数的脚本。注意返回的表与原来的Employees表有着相同的结构，但增加了两个列：lvl和path。lvl列保存子树中从0开始计算的层次值，path列保存“.id0.id1...idn”格式的雇员管理路径。这个字符串包含从子树中最顶层的雇员开始、到当前的雇员为止的所有雇员ID。路径中所有雇员ID的前面和后面都是句点符号。

　　path列允许对本例中dbo.ufn_GetSubtree函数返回的子树的行进行适当的排序。对于特定的雇员来说，由于其所有下属的path值都以他的管理员的path值为前缀，排序中这些值能够正确地出现在它们的管理员之后。函数先把属于管理员的行插入@tree表变量（属于管理员的行在函数参数中指定）。接下来，函数开始一个循环，只要前一次插入操作有结果，迭代就一直进行。循环中的代码把前一次插入操作的直接下属追加到@tree表变量。上次插入的雇员的级别可以通过在@lvl变量中跟踪雇员在子树中的当前级别得到。绿色chinaipower.comCXdiE

　　现在我们可以测试一下ufn_GetSubtree函数。要得到Andrew（empid是2)以及他的所有下属（包括各个级别）的详细信息，查询命令如下：

SELECT*FROMufn_GetSubtree(2)
ORDERBYpath

　　要得到Employees表中所有雇员的层次图，查询命令如下：

SELECTREPLICATE('|',lvl)+empnameASemployee
FROMufn_GetSubtree(1)
ORDERBYpath

　　图一显示了上述查询命令的结果。