SqlServer的数据库Tsql还是很强大，以此来纪念下表值函数的语法吧。
1 -- ============================================= 2 -- Author: <jonney> 3 -- Create date: <2018.07.01> 4 -- Description: <根据产能、单机用料比例、班次、配送频率、提前期、最小包装数、欠品数、余料，等因素按时间点把料配送到产线> 5 -- ============================================= 6 ALTER FUNCTION [dbo].[KittingItemCycleFn] 7 ( 8 -- Add the parameters for the function here 9 @pWorkshop varchar(50) = '',-- 车间 10 @pDateStart varchar(50) = '',-- 计划开始日，开始 11 @pDateEnd varchar(50) = '',-- 计划开始日，结束 12 @pParentItem varchar(50) = '',-- 制品号 13 @pJon varchar(50) = '',-- Jon 14 @pWorkOrderNo varchar(50) = ''-- 工单号 15 ) 16 RETURNS 17 @result TABLE 18 ( 19 [Id] [int] NULL, 20 [WorkOrderNo] [varchar](50) NULL, 21 [ParentItem] [varchar](50) NULL, 22 [Jon] [varchar](50) NULL, 23 [Workshop] [varchar](50) NULL, 24 [StartDate] [varchar](50) NULL, 25 [CycleTime] [int] NULL, 26 [ItemNo] [varchar](50) NULL, 27 [Qty] [decimal](10, 2) NULL, 28 [QtyQp] [decimal](10, 2) NULL, 29 [ExtrNo] [varchar](50) NULL, 30 [Whs] [varchar](50) NULL, 31 [Keeper] [varchar](50) NULL 32 ) 33 AS 34 BEGIN 35 -- Fill the table variable with the rows for your result set 36 37 -- 若计划开始日期，开始为空，设置为今天 38 if (@pDateStart = '') 39 begin 40 set @pDateStart = DATEADD(DD, -1, GETDATE()); 41 end; 42 43 -- 若计划开始日期，结束为空，设置为明天 44 if (@pDateEnd = '') 45 begin 46 set @pDateEnd = DATEADD(DD, 1, GETDATE()); 47 end; 48 49 -- 声明返回的结果集 50 --declare @result dbo.KittingItem, @id int = 0; 51 declare @id int = 0; 52 53 -- 声明一个游标，获取配料出库=6的出库单明细 54 /* 55 出库单据 = [SHLL]表 56 DSRD = 0 = 未作废 57 TYPE = 901 = 正常出库 58 [DESC] = 6 = JON配料出库 59 */ 60 declare kitItemRow cursor for 61 select b.ID, a.NO WorkOrderNo, a.ParentItem, a.Jon, a.Workshop, a.ExpectDate WorkStartDate, c.CODE ItemNo, b.ORDQ Qty, f.START_TIME, f.REQ_TYPE, f.QTY, f.XFER_NO 62 , g.CODE Whs, (select top 1 w.LBL from IUW z inner join [USER] w on z.USR=w.ID where z.DSRD=0 and z.WHS=a.WHS and z.ITM=b.ITM) Keeper 63 from [dbo].[SHPL] a inner join [dbo].[SHLL] b on a.ID = b.SHLO 64 inner join ITEM c on b.ITM = c.ID 65 inner join EREF d on b.ID = d.SHPL 66 inner join EXTR f on d.EXTR = f.XFER_NO 67 left join WRHS g on a.WHS = g.ID 68 where a.DSRD = 0 and a.TYPE=901 and a.[DESC]=6 and a.Workshop = @pWorkshop and a.ExpectDate between @pDateStart and @pDateEnd 69 and (@pParentItem = '' or a.ParentItem=@pParentItem) 70 and (@pJon = '' or charindex(@pJon, a.Jon) > 0) 71 and (@pWorkOrderNo = '' or a.NO = @pWorkOrderNo) 72 order by f.REQ_TYPE; 73 74 -- 打开游标 75 open kitItemRow; 76 77 -- 声明行变量，用于处理业务逻辑 78 declare @rId bigint, @rWorkOrderNo varchar(50), @rParentItem varchar(50), @rJon varchar(50), @rWorkshop varchar(50) 79 , @rWorkStartDate datetime, @rItemNo varchar(50), @rQty decimal(10, 2), @rWhs varchar(50), @rKeeper varchar(50); 80 81 -- Imaps 数据信息 82 declare @shiftStart int, @reqType varchar(10), @qty decimal(10, 2), @extrNo varchar(50); 83 84 -- 游标移动到下一行 85 fetch next from kitItemRow into @rId, @rWorkOrderNo, @rParentItem, @rJon, @rWorkshop, @rWorkStartDate, @rItemNo, @rQty, @shiftStart, @reqType, @qty , @extrNo, @rWhs, @rKeeper; 86 87 -- 若移动游标后有数据，一直循环读取 88 WHILE (@@FETCH_STATUS =0) 89 begin 90 -- 班次特殊处理 91 set @shiftStart = @shiftStart / 100 -1; 92 93 -- 对欠品的特殊处理 94 if @reqType = '95' or @reqType = '99' 95 begin 96 set @rQty = @qty; -- 将总的分配数量设置为欠品 97 end 98 99 -- 获取部品的Cycle策略，若发货频率不等于0，则按照时间别(TYPE = 1602)配料 100 declare @moq int=0, @freq int=0, @preDeli int=0;-- 最小包装单位 101 select @moq=a.PARM1, @freq=a.PARM2, @preDeli=a.PARM3 from STRG a inner join ITEM b on a.ITM = b.ID where a.DSRD=0 and a.TYPE = 1602 and b.CODE = @rItemNo; 102 if @preDeli is not null -- 如果没有设置提前时间，则不考虑 103 begin 104 set @shiftStart -= @preDeli; -- 减掉提前发货小时 105 end; 106 107 -- 声明 制品的满班产量、部品的单机用量 108 declare @cap int=0, @usage int=0; 109 select @usage=PARM1 from JITM where DSRD=0 and TYPE=2202 and MCHC = @rParentItem and ITMC= @rItemNo;-- 获取部品的单机用量TYPE=2202 110 select @cap=PARM1 from JITM where DSRD=0 and TYPE=2201 and MCHC = @rParentItem;-- 获取制品的满班产量TYPE=2201 111 112 declare @capItem int = @rQty;-- 当前部品的应发数量 113 declare @sendTimes int = 1; -- 发货次数 114 declare @itemSendQty int = @capItem / @sendTimes; -- 当前部品每次发货XX件 115 116 -- 发货频率、满班产量、单机用量都设置过，才考虑满班产量 117 if (@cap > 0 and @usage > 0 and @freq > 0) 118 begin 119 set @capItem = @cap * @usage;-- 当前部品的满班用量 120 set @sendTimes = 8 / @freq; -- 重新设置发货频率 121 set @itemSendQty = @capItem / @sendTimes; -- 当前部品每次发货XX件 122 end 123 else if (@freq > 0) -- 如果只设置了发货频率，那就只按照8除以发货频率来计算 124 begin 125 set @capItem = @rQty; 126 set @sendTimes = 8 / @freq; -- 重新设置发货频率 127 set @itemSendQty = @capItem / @sendTimes; -- 当前部品每次发货XX件 128 end 129 else -- 否则就按照集约类型，一次发完 130 begin 131 set @sendTimes = 1; -- 重新设置发货频率 132 set @itemSendQty = @rQty; -- 一次都发完 133 end 134 135 -- 设置最迟配送时间 136 declare @lastTime int = @shiftStart + (@sendTimes - 1)*@freq; 137 -- 记录标准配送数量，有可能一次配送量超过标准的两次、多次 138 declare @standQty int = @itemSendQty; 139 -- 记录第几次配送 140 declare @curTime int=0; 141 142 -- 判断最小包装数@moq 143 declare @modQty int=0; 144 if (@moq > 0) 145 begin 146 set @modQty = @itemSendQty % @moq; 147 if(@modQty > 0) 148 begin 149 set @itemSendQty = (@itemSendQty / @moq + 1) * @moq; 150 end 151 end 152 153 -- 计算余料，放在第一次配送 154 declare @yuLiao int = 0; 155 set @yuLiao = @rQty - @itemSendQty * @sendTimes; 156 157 -- 该行是否已经读取过欠品数据 158 declare @yijing int = 0; 159 declare @mpqQty int = @itemSendQty; -- 考虑过最小包装后的一次配送数 160 161 -- 以下处理配料情况，即Qty > 0 162 while @rQty > 0 163 begin 164 set @id += 1;--结果行号自增 165 set @curTime += 1; 166 set @rQty -= @itemSendQty; 167 168 -- 若是第一次配料，且存在余料，就放在第一次配送 169 if(@yuLiao >0 and @curTime=1) set @rQty -= @yuLiao; 170 171 -- 修正本次要配料数量 172 if @rQty < 0 173 begin 174 set @itemSendQty = @rQty + @itemSendQty; 175 end 176 177 -- 判断是合并行，还是新建行 178 declare @existId int = 0; 179 select @existId = Id from @result where [WorkOrderNo]=@rWorkOrderNo and [ParentItem]=@rParentItem and [Jon]=@rJon and [ItemNo]=@rItemNo and [CycleTime]=@shiftStart 180 181 if @reqType = '95' or @reqType = '99' 182 begin 183 -- 插入欠品数据 184 --if @existId > 0 185 --begin 186 -- update @result set QtyQp = QtyQp + @itemSendQty where Id=@existId; 187 --end 188 --else 189 if (@existId < 1) 190 begin 191 insert into @result(Id,[WorkOrderNo],[ParentItem],[Jon],[Workshop],[StartDate],[CycleTime],[ItemNo], Qty, [QtyQp], [ExtrNo], [Whs], [Keeper]) 192 values(@Id, @rWorkOrderNo , @rParentItem , @rJon , @rWorkshop , convert(varchar(10),@rWorkStartDate,120), @shiftStart , @rItemNo , 0, case when (@yuLiao>0 and @curTime=1) then @itemSendQty + @yuLiao else @itemSendQty end, @extrNo, @rWhs, @rKeeper); 193 end 194 end 195 else if (@reqType = '31') 196 begin 197 -- 插入配料信息 198 if @existId > 0 199 begin 200 update @result set Qty = Qty + @itemSendQty where Id=@existId; 201 end 202 else 203 begin 204 insert into @result(Id,[WorkOrderNo],[ParentItem],[Jon],[Workshop],[StartDate],[CycleTime],[ItemNo], Qty, [QtyQp], [ExtrNo], [Whs], [Keeper]) 205 values(@Id, @rWorkOrderNo , @rParentItem , @rJon , @rWorkshop , convert(varchar(10),@rWorkStartDate,120), @shiftStart , @rItemNo , case when (@yuLiao>0 and @curTime=1) then @itemSendQty + @yuLiao else @itemSendQty end, 0, @extrNo, @rWhs, @rKeeper); 206 end 207 208 -- 如果行剩余数<=0，再次从出库池获取欠品数据 209 if(@rQty <= 0) 210 begin 211 declare @temQp decimal = 0;-- 总欠品数 212 select @temQp = sum(QTY) from EXTR where ITEM_NO=@rItemNo and JON=@rJon and PARENT_ITEM_NO=@rParentItem and WS_CD=@rWorkshop and (REQ_TYPE=99 or REQ_TYPE=95) 213 if(@temQp > 0 and @yijing = 0) 214 begin 215 set @yijing = 1; -- 已经读取过一次的欠品不再读取 216 declare @temRqp int = 0; -- 本次操作的欠品数 217 set @temRqp = @mpqQty - @itemSendQty; -- 考虑MPQ后的配送数 - 当前行已配送数 = 当前行仍需欠品数 218 set @temQp -= @temRqp; 219 if(@temQp <= 0) set @temRqp += @temQp; 220 221 -- 若已经到本班次最后一次配送，则全部配送完 222 if(@curTime >= @sendTimes) 223 begin 224 set @temRqp = @temQp; 225 set @temQp = 0; 226 end 227 update @result set QtyQp = @temRqp where Id = @id; -- 更新当前行的欠品数 228 set @shiftStart += (@temRqp + @itemSendQty) / @standQty * @freq; -- 修改下次配送时点 229 230 while(@temQp > 0) -- 持续把欠品数分配完 231 begin 232 set @temRqp = @mpqQty; 233 if(@shiftStart >= @lastTime) -- 若到最后一次配送时点，则把剩余欠品全部计算 234 begin 235 set @shiftStart = @lastTime; 236 set @temRqp = @temQp; 237 set @temQp = 0; 238 end 239 else set @temQp -= @temRqp; 240 if(@temQp <= 0) set @temRqp += @temQp; 241 set @id += 1; 242 243 -- 插入下一个配送时点的欠品数据 244 insert into @result(Id,[WorkOrderNo],[ParentItem],[Jon],[Workshop],[StartDate],[CycleTime],[ItemNo], Qty, [QtyQp], [ExtrNo], [Whs], [Keeper]) 245 values(@Id, @rWorkOrderNo , @rParentItem , @rJon , @rWorkshop , convert(varchar(10),@rWorkStartDate,120), @shiftStart , @rItemNo , 0, @temRqp, @extrNo, @rWhs, @rKeeper); 246 set @shiftStart += @temRqp / @standQty * @freq; -- 更新配送时点 247 end 248 end 249 end 250 end 251 252 -- 发货时间点增加 253 set @shiftStart += @itemSendQty / @standQty * @freq; 254 if @shiftStart >= 24 255 begin 256 set @shiftStart %= 24; 257 set @rWorkStartDate = DATEADD(DAY, 1, @rWorkStartDate) 258 end 259 end 260 261 -- 获取下一行 262 fetch next from kitItemRow into @rId, @rWorkOrderNo, @rParentItem, @rJon, @rWorkshop, @rWorkStartDate, @rItemNo, @rQty, @shiftStart, @reqType, @qty , @extrNo, @rWhs, @rKeeper; 263 end 264 265 -- 关闭游标 266 close kitItemRow; 267 -- 释放游标 268 DEALLOCATE kitItemRow; 269 270 -- 返回结果 271 --SELECT [WorkOrderNo], [ParentItem], [Jon], [Workshop], [StartDate], [CycleTime], [ItemNo], [Qty], [QtyQp], [Keeper], [Whs] from @result 272 --where qty>0 and qtyqp > 0 273 --group by [ParentItem],[Jon],[Workshop],[StartDate],[CycleTime],[ItemNo] 274 --order by [StartDate],[CycleTime], WorkOrderNo, [ItemNo]; 275 RETURN 276 END