SQL Server 递归

查看文章
SQLServer 自定义函数 的递归2008年05月23日 星期五 17:40来说说数据库自定义函数的递归用法
先看下图

有这样一张树形结构表，
如：C18 数码摄像机 是在 C12 数码产品 类别下
而 C12 数码产品 又在C2 IT产品 类别下！
C2 IT产品 在 000(根节点下)
即分类为 C2 IT产品-C12 数码产品-C18 数码摄像机
现在假使有这样一种需要 ，通过SQLserver平台，达到给定ClassID得到其所有父节点的ClassID，并且通过“_”连接起来！如 C18 的结果为 C2_C12_C18 如下表
可以看到 数码摄像机的Place字段为 C2_C12_C18,字段Place为标识字段，需求中有一定的作用！
这样，当我们添加一个分类时，除了指定基本信息外，还需要指定其Place，才可以顺利添加一条记录！
当时可以通过很多方法得到新添加记录的Place，但假使现在的需求是通过SQL自定义递归函数来完成功能！
如何实现？给定ClassID（该数据可以通过添加时得到的@@identity 全局变量获取） 得到Place!
看如下SQL语句
/**//*
-------------------------------------------
项目：公司Cmwin产品库
名称： F_ContactAllParentPlaceByClassID
功能： 通过classID生成其Place 前缀。除自己的Place
链接其所有父ClassID
相关表：ClassTree_update
编写：冯岩
时间：2007-11-22 11:22

-------------------------------------------
*/
CREATE function F_ContactAllParentPlaceByClassID
(
@classID varchar(15) --参数
)
returns varchar(500)
as
begin

declare @ParentClassID varchar(15) --变量父ID
declare @Place varchar(500) --变量Place
set @Place = ''
--首先根据传入的ClassID获取其父ID，ParentClassID
SELECT @ParentClassID = ParentClassID FROM ClassTree_update
where ClassID = @classID
/**//*到此，我们得到 其父ID 例如 C18 我们可以得到 C12 但问题还没有完，
C12本身也还有父节点，即C2。我们还需要将C2取出，C2的父节点为000即为根节点。
至此结束。
因此，由此问题可以看出，这是一个递归过程，根据指定ClassID获取其父ID。再
根据父ID继续获取父ID的父ID，一直递归，直至父ID为000为止！
因此下面实现SQL函数的递归 ，注意看
*/
if (@ParentClassID <> '000')--如果不是根节点
begin
-- 再将@ParentClassID父ID作为ClassID传入 进行自调用
set @Place = dbo.F_ContactAllParentPlaceByClassID(@ParentClassID) + @ParentClassID+'_'

end
return @Place
end已有详细说明！运行看
留心一下，有的SQL版本运行上述SQL语句在dbo.F_ContactAllParentPlaceByClassID(@ParentClassID)会报错。原因是，此时正的创建dbo.F_ContactAllParentPlaceByClassID函数，而还未创建，在又在此处调用dbo.F_ContactAllParentPlaceByClassID(@ParentClassID)，因此会提示没有这个对象，解决办法是先去掉dbo.F_ContactAllParentPlaceByClassID(@ParentClassID)创建后再Alter修改 即可！我遇见过一次！

运行效果


给定'C18'即得到其所有父节点的Place 并且使用"_"链接起来，离要求已经很近了，只是最后再将C18连在其后就达到要求，C18又是已知的！ClassID字段为递增，在实际添加分类时可以通过@@identity变量获取到！连在其后即是 C2_C12_C18_
至此，通过SQL自定义函数实现给定ClassID 拼截出其对应的Place标识符！

总结：这里只是演示一下SQL自定义函数的用法，并且实现一个简单的递归。当然，要实现这个功能，不一定要使用函数。我在此只是作为演示函数用法而讲讲！有时使用SQL函数还是很方便的！