实战计算BOM成本的存储过程
1 USE [TD_ManagerInfo] 2 GO 3 /****** 对象: StoredProcedure [dbo].[pro_GetBOMList] 脚本日期: 06/20/2013 20:55:43 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 9 -- ============================================= 10 -- Author: JOY 11 -- Create date: 2013-6-6 12 -- Description: 根据品号查询所有BOM清单 13 -- ============================================= 14 ALTER PROCEDURE [dbo].[pro_GetBOMList] 15 -- 存储过程参数 16 @PROID varchar(100) 17 AS 18 BEGIN 19 20 --检查临时表 21 If object_id('tempdb..##BOMINFO') is not null Drop Table ##BOMINFO 22 23 --创建临时表 24 Create table ##BOMINFO 25 ( 26 BOM_NO varchar(100) not null, --BOM号 27 PRD_NO varchar(100) PRIMARY KEY, --品号 28 PRDNAME varchar(100), --品名 29 MIN_PURCHASE INT default(0), --最小采购量 30 PRICE [numeric](18,5) default(0), --单价 31 PRICESubtotal [numeric](18,5) default(0), --单个部品金额 采购量*单价 32 SUP_COUNT INT default(0), --供应商/家 33 ID_NO varchar(100), --子件ID 34 LAYER INT, 35 ) 36 37 --递归读取BOM表（With前面有语句需要用分好隔开） 38 ;With TBOM as 39 ( 40 Select BOM_NO,PRD_NO,[NAME],ID_NO,QTY from DB_TD02.DB_TD02.dbo.TF_BOM WHERE BOM_NO '" target="_blank">=@PROID+'->' 41 UNION ALL 42 Select B.BOM_NO,B.PRD_NO,B.NAME,B.ID_NO,B.QTY from TBOM 43 inner join DB_TD02.DB_TD02.dbo.TF_BOM B on TBOM.ID_NO=B.BOM_NO 44 ) 45 46 --Select * from TBOM (测试数据) 47 --根据型号查询BOM记录复制到临时表(层级) 48 Insert into ##BOMINFO(BOM_NO,PRD_NO,PRDNAME,ID_NO) Select B.BOM_NO,B.PRD_NO,B.NAME,B.ID_NO from TBOM B 49 50 51 ----------------------------------------------------------------------------------------------------- 52 -------------------------------游标执行对递归后TBOM的其他计算处理------------------------------------- 53 ----------------------------------------------------------------------------------------------------- 54 --申明变量(用户游标复制操作) 55 declare @PRD_NO varchar(100) 56 declare @PRDNAME varchar(100) 57 declare @BomCusCount int --货品对应供应商总数 58 declare @PRICE numeric(18,8) --部品单价 59 declare @MIN_PURCHASE int --最小采购量 60 declare @Layer int --层级 61 declare @KND varchar(1) --大类代号 62 declare @IDX1 varchar(10) --中类代号(包装类、喷油件、电镀件...) 63 declare @SUP1 varchar(12) --主供应商 64 declare @MAXDAYS numeric(18,8) --最长前置期 65 declare @MAXDAYSVALUE numeric(18,8) --存放最终前置值 66 Select @MAXDAYSVALUE=0 --初始化最长前置期 67 Select @MAXDAYS=0 --初始化最长前置期 68 69 --声明一个游标cur_BOMINFO，select语句中参数的个数必须要和从游标取出的变量名相同 70 declare cur_BOMINFO cursor for select PRD_NO from ##BOMINFO 71 --打开游标 72 Open cur_BOMINFO 73 --读取游标 一条记录插入变量 74 Fetch next from cur_BOMINFO into @PRD_NO 75 While(@@fetch_status = 0) 76 Begin 77 --根据品号查找对应供应商个数 78 Select @BomCusCount=COUNT(C.CUS_NO) from DB_TD02.DB_TD02.dbo.PRDT_CUS C WHERE C.PRD_NO =@PRD_NO 79 80 --修改前Select top 1 @KND=KND,@IDX1=IDX1,@SUP1=SUP1 from DB_TD02.DB_TD02.dbo.PRDT P WHERE P.PRD_NO =@PRD_NO 81 82 --根据品号供应商信息更新 83 Update ##BOMINFO set SUP_COUNT=SUP_COUNT+@BomCusCount where PRD_NO=@PRD_NO 84 85 --查找货品查找单价 86 Select top 1 @PRICE=isnull(UP_DEF.UP,0.0000) from DB_TD02.DB_TD02.dbo.UP_DEF UP_DEF WHERE PRD_NO=@PRD_NO 87 --根据品号查找单价信息更新 88 Update ##BOMINFO set PRICE=@PRICE where PRD_NO=@PRD_NO 89 90 --根据品号查找最小采购量 91 Select @MIN_PURCHASE=isnull(QTY_MIN,0),@MAXDAYS=NEED_DAYS from DB_TD02.DB_TD02.dbo.PRDT WHERE PRD_NO=@PRD_NO 92 --根据品号查找最先采购量信息更新 93 Update ##BOMINFO set MIN_PURCHASE=@MIN_PURCHASE where PRD_NO=@PRD_NO 94 95 --判断最长前置期 96 IF @MAXDAYS>@MAXDAYSVALUE 97 begin 98 Set @MAXDAYSVALUE=@MAXDAYSVALUE 99 end 100 101 --继续.... 102 Fetch next from cur_BOMINFO into @PRD_NO 103 End 104 --关闭游标 105 Close cur_BOMINFO 106 --删除游标 107 Deallocate cur_BOMINFO 108 109 --查询结果 110 --print @MAXDAYSVALUE 111 --Select * from ##BOMINFO 112 113 END
这里用到的游标貌似不怎么好~~~需要改善
