用SQL語句實現遞歸算法

          本文通過一個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')