论坛交流
首页办公自动化| 网页制作| 平面设计| 动画制作| 数据库开发| 程序设计| 全部视频教程
应用视频: Windows | Word2007 | Excel2007 | PowerPoint2007 | Dreamweaver 8 | Fireworks 8 | Flash 8 | Photoshop cs | CorelDraw 12
编程视频: C语言视频教程 | HTML | Div+Css布局 | Javascript | Access数据库 | Asp | Sql Server数据库Asp.net  | Flash AS
当前位置 > 文字教程 > Sql Server教程
Tag:注入,存储过程,分页,安全,优化,加密,索引,日志,压缩,base64,函数,内存,PDF,迁移,结构,破解,编译,配置,进程,分词,触发器,socket,安装,sqlserver2000,sqlserver2005,sqlserver2008,视频教程

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

文章类别:Sql Server | 发表日期:2008-10-5 21:35:58

四、查询管理链

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


LISTING 6:获得整棵子树

CREATE FUNCTION ufn_GetSubtree
(
@mgrid 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,
path varchar(900) NOT NULL
)
AS

BEGIN

DECLARE @lvl AS int, @path AS varchar(900)
SELECT @lvl = 0, @path = '.'

INSERT INTO @tree
SELECT empid, mgrid, empname, salary,
@lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @mgrid

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

INSERT INTO @tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @tree AS T
ON E.mgrid = T.empid AND T.lvl = @lvl - 1
END

RETURN

END


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

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


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


SELECT * FROM ufn_GetSubtree(2)
ORDER BY path


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


SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path


绿色c hinaip ower. comCXdiE

绿色c hinaip ower. comCXdiE

  图一显示了上述查询命令的结果。绿色c hinaip ower. comCXdiE

视频教程列表
文章教程搜索
 
Sql Server推荐教程
Sql Server热门教程
看全部视频教程
购买方式/价格
购买视频教程: 咨询客服
tel:15972130058