SQL函數(shù)的種類(lèi)很多，實(shí)現(xiàn)的功能也不太一樣。下面為您介紹的是用于遍歷BOM表的SQL函數(shù)，希望可以讓您對(duì)SQL函數(shù)有更多的了解。

表結(jié)構(gòu)如下:
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  

函數(shù)如下：

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  

調(diào)用方法如下:
select * from fn_aaa('a.1')
意思是找出a.1下的所有兒子及孫子.
 
 

【編輯推薦】

動(dòng)態(tài)sql中使用臨時(shí)表的實(shí)例

Oracle存儲(chǔ)過(guò)程使用動(dòng)態(tài)SQL

SQL Server刪除視圖的兩種方法

SQL Server視圖的使用

sql server表格變量的用法