本文通過一個BOM表的例子,分別介紹在SQL SERVER2000和SQL SERVER2005中如何編寫遞歸算法。
(一) 建立測試數據
CREATE TABLE BillOfMaterial(
ProductNO nvarchar(15), --父組件編號
ProductName nvarchar(50), --子組件名稱
PartNo nvarchar(15), --子組件編號
UseQty numeric(9,2), --子組件用量
MaterialClass nchar(1), --組件類, P=成品 M=半成品
UNIT varchar(10) --單位
)
INSERT INTO BillOfMaterial(ProductNO, PartNo, ProductName, UseQty, MaterialClass, UNIT)
SELECT '01651', '01651', N'CABLE ASS''Y', NULL, 'P', 'M'
UNION
SELECT '01651', '01-1001', N'汽車專用線OD=2.3mm', 1, 'M', 'S'
UNION
SELECT '01651', '01-1002', N'HOUSING白色', 1, 'M', 'S'
UNION
SELECT '01651', '01-1003', N'模塊A', 1, 'M', 'S'
UNION
SELECT '01-1003', '01-1004', N'模塊A-01', 1, 'M', 'S'
UNION
SELECT '01-1003', '01-1005', N'模塊A-02', 1, 'M', 'S'
UNION
SELECT '01-1005', '01-1006', N'模塊A', 1, 'M', 'S'
UNION
SELECT '01-1006', '01-1007', N'模塊A-01', 1, 'M', 'S'
UNION
SELECT '01-1007', '01-1008', N'模塊A-02', 1, 'M', 'S'
GO
(二) SQL SERVER2000的實現方法
IF OBJECT_ID('Fn_BOM_2000') IS NOT NULL
DROP FUNCTION [Fn_BOM_2000]
GO
CREATE FUNCTION [dbo].[Fn_BOM_2000]
(
@bo_no nvarchar(15)
)
RETURNS @Result TABLE(
Line varchar(255),
PartNo nvarchar(15),
PartName nvarchar(20),
UseQty float,
MaterialClass nchar(1),
[Level] int,
[Sid] varchar(255))
AS
BEGIN
DECLARE @i int, @ReStr varchar(50)
SELECT @i=0, @ReStr=' ';
INSERT INTO @Result(Line, PartNo, PartName
,UseQty, MaterialClass, [Level], [Sid])
SELECT ProductNO, PartNo, ProductName
,1, MaterialClass, @i, @bo_no
FROM BillOfMaterial
WHERE ProductNO=@bo_no AND UseQty is null
WHILE @@ROWCOUNT>0
BEGIN
SET @i=@i+1
INSERT INTO @Result(Line, PartNo, PartName
,UseQty, MaterialClass, [Level], [Sid])
SELECT SUBSTRING(Left(@ReStr,@i)+'├----------------------------',1,8)
,b.PartNo,b.ProductName
,b.UseQty*r.UseQty, b.MaterialClass, @i, r.Sid+'_'+b.PartNo
FROM BillOfMaterial b, @Result AS r
WHERE r.[Level]=@i -1
AND b.ProductNO=r.PartNo
AND b.UseQty>0
END
RETURN
END
GO
SELECT * FROM dbo.Fn_BOM_2000('01651')
(三) SQL SERVER2005的實現方法
IF OBJECT_ID('Fn_BOM_2005') IS NOT NULL
DROP FUNCTION [Fn_BOM_2005]
GO
CREATE FUNCTION [dbo].[Fn_BOM_2005]
(
@bo_no nvarchar(15)
)
RETURNS @Result Table(
line nvarchar(255),
PartNo nvarchar(15),
PartName nvarchar(20),
UseQty float,
MaterialClass nchar(1),
level int,
sid nvarchar(255))
AS
BEGIN
DECLARE @ReStr varchar(50)
SET @ReStr=' ';
WITH T(line,PartNo, PartName, UseQty, MaterialClass, [level], [sid]) AS
(
SELECT CAST(ProductNo AS VARCHAR(255))
,PartNo
,ProductName
,CONVERT(FLOAT,1.0)
,MaterialClass
,0
,CAST(@bo_no AS VARCHAR(255))
FROM BillOfMaterial
WHERE ProductNo=@bo_no AND UseQty IS NULL
UNION ALL
SELECT CAST(SUBSTRING(LEFT(@ReStr, T.[Level]+1)+'|------------------', 1, 8) AS VARCHAR(255))
,r.PartNo
,r.ProductName
,CONVERT(FLOAT,T.UseQty*r.UseQty)
,r.MaterialClass
,T.[Level]+1 AS [Level]
,CAST(T.[Sid]+'_'+r.PartNo AS varchar(255)) AS [Sid]
FROM BillOfMaterial r INNER JOIN T
ON r.ProductNo=T.PartNo and r.UseQty>0
)
INSERT INTO @Result SELECT * FROM T
RETURN
END
GO
SELECT * FROM dbo.Fn_BOM_2005('01651')