实战计算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

这里用到的游标貌似不怎么好~~~需要改善

 

转载于:https://www.cnblogs.com/iplaycode/p/3404857.html