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表格變量的用法