遍历BOM表的SQL函数

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下的所有儿子及孙子.
 
 

【编辑推荐】

动态sql中使用临时表的实例

Oracle存储过程使用动态SQL

SQL Server删除视图的两种方法

SQL Server视图的使用

sql server表格变量的用法