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]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGO/*功能：實現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))ASBEGINDECLARE @TmpPartNo varchar(36)DECLARE @Children_Item varchar(36)DECLARE BOM_CUR CURSOR LOCAL SCROLL FOR SELECT PART_NO FROM ENGBOMM WHERE ASSM_NO=@Parent_ItemOPEN BOM_CURFETCH NEXT FROM BOM_CURINTO @Children_ItemWHILE @@FETCH_STATUS=0BEGINSET @TmpPartNo=''SELECT TOP 1 @TmpPartNo=PART_NO FROM ENGBOMM WHERE ASSM_NO=@Children_ItemIF @TmpPartNo='' --最底層物料，插入結果表INSERT INTO @Result SELECT @Parent_Item_Disp,@Children_ItemELSE --非最底層物料，遞歸調用BEGININSERT INTO @Result SELECT* from FUN_RECURRENT_PARENT_CHILDREN(@Parent_Item_Disp,@Children_Item)ENDFETCH NEXT FROM BOM_CURINTO @Children_ItemENDCLOSE BOM_CURDEALLOCATE BOM_CURRETURNEND GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO/*調用舉例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]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGO/*功能：實現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))ASBEGIN 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 RETURNEND/*調用舉例 SELECT * FROM FUN_FILTER_PARENT_CHILDREN('306-20822A')*/ GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO