SQLServer 自定义函数 的递归

来说说数据库自定义函数的递归用法
先看下图

有这样一张树形结构表,
如: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函数还是很方便的

posted on 2007-11-26 14:09  冯岩  阅读(9581)  评论(6编辑  收藏

导航