SQL函数的种类很多，实现的功能也不太一样。下面为您介绍的是用于遍历BOM表的SQL函数，希望可以让您对SQL函数有更多的了解。
表结构如下:
ptype subptype amount
a a.120
a a.2 15
a a.3 10
a. 1 a.1.1 20
a.1a.1.2 15
a.1 a.1.330
a.2 a.2.110
a.2 a.2.2 20
a.1.1 a.1.1.1 45
a.1.1 a.1.1.2 15
a.2.1 a.2.1.1 20
a.2.2 a.2.2.1 13
create table matgroup(parentgroup varchar(50),childgroup varchar(50), mount float) insert into matgroup select 'a', 'a.1',20 union select 'a', 'a.2', 15 union select 'a', 'a.3', 10 union select 'a.1', 'a.1.1', 20 union select 'a.1','a.1.2', 15 union select 'a.1', 'a.1.3',30 union select 'a.2', 'a.2.1',10 union select 'a.2', 'a.2.2', 20 union select 'a.1.1', 'a.1.1.1', 45 union select 'a.1.1', 'a.1.1.2', 15 union select 'a.2.1' ,'a.2.1.1', 20 union select 'a.2.2', 'a.2.2.1', 13
函数如下：
create FUNCTION fn_aaa (@matgroup varchar(50),@mount int ) RETURNS @retPLExpand TABLE (parentgroup varchar(50),childgroup varchar(50), mount float) AS BEGIN DECLARE @RowsAdded int declare @PLExpand Table (parentgroup varchar(50),childgroup varchar(50), mount float,processed tinyint default(0)) INSERT @PLExpand SELECT b.parentgroup,b.childgroup, @mount*b.mount, 0 FROM matgroup b WHERE b.parentgroup=@matgroup SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @PLExpand SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees marked 1. INSERT @PLExpand SELECT a.parentgroup,a.childgroup,a.mount*b.mount , 0 FROM matgroup a inner join @PLExpand b on a.parentgroup=b.childgroup where b.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @PLExpand SET processed = 2 WHERE processed = 1 END -- copy to the result of the function the required columns INSERT @retPLExpand SELECT parentgroup,childgroup,mount FROM @PLExpand RETURN END
调用方法如下:
select * from fn_aaa('a.1')
意思是找出a.1下的所有儿子及孙子.