BOM查詢(通過成品或半成品找最底層的物料)

2009-09-26 11:31:13

法1：遞歸法

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FUN_RECURRENT_PARENT_CHILDREN]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FUN_RECURRENT_PARENT_CHILDREN]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
功能：實現BOM查詢(通過成品或半成品找最底層的物料清單，不包括中間層)
方法：遞歸法
作者：鄧星傑
時間：2009.09.24
說明：ENGBOMM表為BOM表，字段ASSM_NO為父階料號、PART_NO為子階料號
：參數@Parent_Item_Disp隻是為了在插入結果時保持父階料號不變
：參數@Parent_Item為要查找的成品或半成品
：傳入值時，此兩參數值一樣
*/
CREATE FUNCTION FUN_RECURRENT_PARENT_CHILDREN(@Parent_Item_Disp varchar(36),@Parent_Item varchar(36))
RETURNS @Result TABLE(ParentItem varchar(36),ChildrenItem varchar(36))
AS
BEGIN
DECLARE @TmpPartNo varchar(36)
DECLARE @Children_Item varchar(36)
DECLARE BOM_CUR CURSOR LOCAL SCROLL FOR SELECT PART_NO FROM ENGBOMM WHERE ASSM_NO=@Parent_Item

OPEN BOM_CUR

FETCH NEXT FROM BOM_CUR
INTO @Children_Item

WHILE @@FETCH_STATUS=0
BEGIN
SET @TmpPartNo=''
SELECT TOP 1 @TmpPartNo=PART_NO FROM ENGBOMM WHERE ASSM_NO=@Children_Item
IF @TmpPartNo='' --最底層物料，插入結果表
INSERT INTO @Result SELECT @Parent_Item_Disp,@Children_Item
ELSE --非最底層物料，遞歸調用
BEGIN
INSERT INTO @Result SELECT* from FUN_RECURRENT_PARENT_CHILDREN(@Parent_Item_Disp,@Children_Item)
END

FETCH NEXT FROM BOM_CUR
INTO @Children_Item
END

CLOSE BOM_CUR

DEALLOCATE BOM_CUR

RETURN
END

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
調用舉例
SELECT * FROM FUN_RECURRENT_PARENT_CHILDREN('306-20822A' ,'306-20822A')
*/

 

 

方法2：篩選法

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FUN_FILTER_PARENT_CHILDREN]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FUN_FILTER_PARENT_CHILDREN]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
功能：實現BOM查詢(通過成品或半成品找最底層的物料清單，不包括中間層)
方法：篩選法 
作者：鄧星傑
時間：2009.09.24 
說明：ENGBOMM表為BOM表，字段ASSM_NO為父階料號、PART_NO為子階料號
         ：參數@Parent_Item為要查找的成品或半成品
*/

CREATE FUNCTION FUN_FILTER_PARENT_CHILDREN(@Parent_Item VARCHAR(36))
RETURNS @Result TABLE(ParentItem VARCHAR(36),ChildrenItem VARCHAR(36))
AS
BEGIN

 DECLARE @tmpParentItem TABLE(Parent_Item VARCHAR(36) COLLATE Chinese_Taiwan_Stroke_CI_AS)
 DECLARE @tmpChildrenItem TABLE(Children_Item VARCHAR(36) COLLATE Chinese_Taiwan_Stroke_CI_AS)
 
 --找出直接子階料號，插入臨時表
 INSERT INTO @tmpChildrenItem
 SELECT  PART_NO  FROM ENGBOMM WHERE ASSM_NO=@Parent Item

 
 --為了直觀才用此變量，否則直接可用@@ROWCOUNT
 DECLARE @Rows INT
 SET @Rows=0
 
 --插入最底層物料到結果表
 INSERT INTO @Result
 SELECT @Parent_Item as ASSM_NO,Children_Item  FROM @tmpChildrenItem a WHERE not exists (SELECT * FROM ENGBOMM WHERE  ASSM_NO=a.Children_Item)
 
 --手稿非最底層物料到臨時表
 INSERT INTO @tmpParentItem
 SELECT Children_Item FROM @tmpChildrenItem a WHERE  exists (SELECT * FROM ENGBOMM WHERE  ASSM_NO=a.Children_Item)
 SET @Rows=@@ROWCOUNT

 WHILE @Rows>0
 BEGIN
  SET @Rows=0

  DELETE FROM @tmpChildrenItem
  INSERT INTO @tmpChildrenItem
  SELECT  PART_NO  FROM ENGBOMM WHERE ASSM_NO in (SELECT Parent_Item FROM @tmpParentItem)
  
  --插入最底層物料到結果表
  INSERT INTO @Result
  SELECT @Parent_Item as ASSM_NO,Children_Item  FROM @tmpChildrenItem a WHERE not exists (SELECT * FROM ENGBOMM WHERE  ASSM_NO=a.Children_Item) 
 
  DELETE FROM @tmpParentItem
  INSERT INTO @tmpParentItem
  SELECT Children_Item FROM @tmpChildrenItem a WHERE  exists (SELECT * FROM ENGBOMM WHERE  ASSM_NO=a.Children_Item)
  SET @Rows=@@ROWCOUNT --如果@@ROWCOUNT>0，說明還有非最底層的物料存在，尚需進一步篩選

 END

 RETURN


END


/*
調用舉例 
SELECT * FROM FUN_FILTER_PARENT_CHILDREN('306-20822A')
*/

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO