2020-02-15 简单的记录一下
2020-04-24 零点整:之前写的sql中大量使用了函数,并且每一行物料判断完成就要写入一次MTO的表并且调用一次校对库存的存储过程。当一张单据数据量超过100行的时候,占用太多资源,审核时间达5-6分种之久。经过这几天晚上的研究,重新调整思路,使用表连接,然后通过临时表上的计算,一次算出结果,然后一次插入MTO表,使得程序运行速度大大加快。今天出来了初步的结果,明天争取完善。
2020-04-25 今天把功能进行了完善,在系统中进行测试:采购申请单上面有多达180行物料,点击审核后仅用了3秒即完成了库存的判断,并生成了MTO调整单。而如果用原来的计算方法则很大概率无法审核完成,而且日志文件瞬间爆发至2G以上。
这里说一下,为什么开始设计的那个触发器会出现速度慢的问题。
究其主要原因,第一次设计的是把采购申请单的记录拿出来逐行去判断库存,每判断完一行记录则插入MTO调整单,而插入一条MTO调整单的记录,则需要进行一次库存的校对,所以导致占用资源高,耗时长。
新的方法使用了表联结,一次计算出结果。
⚙
一、触发器的一些基础
触发器的概念
当在表
中插入、更新、删除记录
时,触发
一个或一系列的SQL语句
触发器类型
1.After触发器
当数据表执行插入、更新、删除操作之后,After触发器才被触发。After触发器只能用于数据表,不可用于视图。
分为:
- after insert触发器
- after update触发器
- after delete触发器
- for insert触发器
- for update触发器
- for delete触发器
2.Instead Of 触发器
当数据表执行插入、更新、删除操作,Instead Of触发器并不执行插入、更新、删除操作,而是替换成执行触发器本身的T-SQL逻辑。
分为:
- instead of insert触发器
- instead of update触发器
- instead of delete触发器
Instead Of触发器也可用于视图,用来扩展是多表连接的视图设计可以进行更新操作。视图设计是多表连接,是无法更新的。这时候,可以用Instead Of触发器,把更新操作替换为向每个基本表进行更新。
触发器的2个中间表
触发器触发的时候有两个中间表:插入表(instered表)和删除表(deleted表)。这两张表的字段,和触发器应用主表的字段相同。中间表存储的数据是执行插入、更新、删除操作的数据,当触发器执行完成后,这两张表就会被删除。Inserted表的数据是待插入的数据,或是触发器修改后的数据。deleted表的数据是插入前的原始数据。如果是删除操作的触发器,deleted表是表示删除的数据。
[AFTER]的情况下 | Inserted | Delete | |
---|---|---|---|
insert | 存放新增的记录 | 不存储记录 | |
update | 存放用来更新的新记录 | 存放更新前的记录 | |
delete | 不存储记录 | 存放被删除的记录 |
触发器的语法结构
CREATE TRIGGER trigger_name
ON {Table_name|View_name}
{FOR|AFTER|INSTEAD OF} [INSERT,UPDATE,DELETE]
AS
声明部分
BEGIN
执行部分
END
FOR
和AFTER
意义一样; 它们不能作用于视图- 修改触发器把
CREATE
改成ALTER
- 触发器命名规则:
tr_表名_说明
删除触发器
DROP TRIGGER trigger_name
查询数据库中已有的触发器
SELECT * FROM sysobjects where xtype = 'TR'
查看某个触发器的具体内容
EXEC sp_helptext 'trigger_name'
触发器的启用和停用
alter table tablename disable trigger trigname
alter table tablename enable trigger trigname
sql profiler如何捕捉触发器
sqlserver profiler 的event里选择这两个:
Stored procedures: - SP:StmtStarting - SP:StmtCompleted
二、初步测试
初步在
采购申请单
上创建一个触发器, 在单据审核
时修改
单据的编码
create trigger tr_POrequest_MTOchange
on POrequest
after update
as
begin
declare @billno nvarchar(100)
if UPDATE(FCheckerID) begin
select @billno = fbillno from deleted
update PORequest set FBillNo = 'rp001'
where FBillNo = @billno
end
end
-- 这是一个字段级的触发器, 为了能识别单据是保存时执行了update还是审核时执行了update,
-- 所以使用了if update(字段); 当FCheckerID字段改变后, 才会触发后面的SQL语句
三、SQL相关知识
SQL表循环
需要在采购申请明细表中遍历所有行的物料, 通过循环的方法实现. (通过游标应该也可以)
参考: https://www.cnblogs.com/yssss/p/10731626.html
set nocount on
--创建一个表变量, 用来存储要遍历的内容
declare @t_MTOEntry table (FDetailID int)
insert into @t_MTOEntry from select ... --插入表内容, 省略(这里放的是采购申请的FDetailID)
select ROW_NUMBER() over(order by FDetailID) as i,* into #t_id from @t_MTOEntry --创建临时表,加上序号
declare @count int
declare @i int = 1
select @count = COUNT(1) from #t_id --获取临时表的记录数,语法:/SQL/64.html
while (@i<=@count)
begin
declare @FDetailID int
select @FDetailID=FDetailID from #t_id where i = @i
print str(@FDetailID)
set @i = @i+1
end
drop table #t_id
SQL函数
SQL自定义函数 : https://www.cnblogs.com/happyyangyanghappy/p/10870495.html
SQL自定义函数主要分为2种 :
- 标量函数:
返回单个数据 - 表格值函数:
一种是函数中只有一条查询语句;
一种是函数中可以有多条SQL语句;
标量函数
返回单个数据
语法格式
--创建一个函数
create function 函数名(参数 类型[,参数2 类型2, ...])
returns 返回值类型
as
begin
declare @变量 变量类型
set @变量 = 参数
return @变量
end
--调用方法
dbo.函数名(参数)
例如:
select dbo.函数名(参数) as "列名"
示例
--创建一个函数用来获取计量单位对应的换算率
create function Coeff(@FUnitID int)
returns decimal(28,10)
as
begin
declare @Coeff decimal(28,10)
select @Coeff = FCoefficient from t_MeasureUnit where FMeasureUnitID = @FUnitID
return @Coeff
end
--调用方法
declare @n decimal(28,10)
select @n = 5 * dbo.coeff(290)
print @n
表格值函数
单条语句, 返回值为一个table
语法格式
create function 函数名(参数 类型[,参数2 类型2, ...])
returns table
as
return (SQL查询语句)
示例
create function func_ReqMto(@FDetailID int, @FItemID int)
returns table
as
return (select t1.FInterID ,
t1.FItemID ,
t1.FQty ,
t1.FUnitID ,
t1.FDetailID ,
t1.FSecQty ,
t1.FMTONo as "ToMTONo",
t2.FBatchNo ,
t2.FStockID ,
t2.FStockPlaceID ,
t2.FQty as "FQtyInv",
t2.FKFPeriod ,
t2.FKFDate ,
t2.FSecQty as "SecQtyInv",
t2.FMTONo as "FromMTONo"
from PORequestEntry t1 left join ICInventory t2 on t1.FItemID = t2.FItemID
where t1.FItemID = @FItemID
and t1.FDetailID = @FDetailID
and t2.FMTONo in ('','999999')
)
--调用方法
select * from func_ReqMto(参数1,参数2)
多条语句, 返回值为一个table
语法格式
create[alter] function 函数名(参数 类型[,参数2 类型2,...])
returens @表变量 (字段名 类型, [字段名2 类型2, 字段名3 类型3, ...])
as
begin
SQL过程
insert into @表变量 select 字段名 类型, [字段名2 类型2, 字段名3 类型3, ...] from [条件]
end
示例
create function func_Inv( @FDetailID int, @FItemID int)
returns @Inv table (FInterID int,
FItemID int,
FQty_Req decimal(28,10),
FUntiID int,
FDetailID int,
FSecQty_Req decimal(28,10),
FMTONo_Req nvarchar(50),
FBatchNo varchar(200),
FStockID int,
FStockPlaceID int,
FQty decimal(28,10),
FKFPeriod int,
FKFDate varchar(20),
FSecQty decimal(28,10),
FMTONoFrom nvarchar(50))
as
begin
insert into @inv
select t1.FInterID ,
t1.FItemID ,
t1.FQty ,
t1.FUnitID ,
t1.FDetailID ,
t1.FSecQty ,
t1.FMTONo as "ToMTONo",
t2.FBatchNo ,
t2.FStockID ,
t2.FStockPlaceID ,
t2.FQty as "FQtyInv",
t2.FKFPeriod ,
t2.FKFDate ,
t2.FSecQty as "SecQtyInv",
t2.FMTONo as "FromMTONo"
from PORequestEntry t1 left join ICInventory t2 on t1.FItemID = t2.FItemID
where t1.FItemID = 293
and t1.FDetailID = 176
and t2.FMTONo in ('','999999')
order by t2.FMTONo , t2.FQty desc
return
end
SQL存储过程
语法格式
create proc 过程名称
@变量1 类型, [可以有默认值]
@变量2 类型, [可以有默认值]
...
as
begin
SQL语句
end
--调用方法
exec 过程名称 @变量1 = 参数, @变量2 = 参数
示例
create proc proc_mtoChange
@FDetailID int ,
@isAll int ,
@maxnum int ,
@reqQty decimal(28,10)
as
begin
select identity(int,1,1) as "id", * ,null as "chaQty" , null as "MTOChange", null as "fno"
into #inv
from func_reqmto(@FDetailID)
order by frommtono, fqtyinv desc
declare @m int = 1
declare @count int = (select count(id) from #inv)
declare @chaQty decimal(28,10)
select @chaQty = fqty from PORequestEntry where FDetailID = @FDetailID
while @m <= @count begin
set @chaQty = @chaQty - (select fqtyinv from #inv where id = @m)
update #inv set chaQty = @chaQty where id = @m
if @chaQty > 0 begin
update #inv set MTOChange = FQtyInv where id = @m
end
else if @chaQty <= 0 begin
update #inv set MTOChange = FQtyInv + chaQty where id = @m
end
set @m = @m +1
end
update #inv set fno = id where chaQty <=0
declare @top int
declare @sumMTOchange decimal(28,10)
if @isall = 0 begin
select @top = MIN(fno) from #inv
/* [FEntrySelfP0133] MTO调整数量;[FEntrySelfP0134] MTO关联标志 */
update PORequestEntry set FEntrySelfP0133 = @reqQty / dbo.coeff(@FDetailID),FMRPClosed = 1 ,FEntrySelfP0134 = 1
where FDetailID = @FDetailID
drop table #sumMTOchange
end
else if @isall = 1 begin
select @top = count(id) from #inv
/* [FEntrySelfP0133] MTO调整数量;[FEntrySelfP0134] MTO关联标志 */
update PORequestEntry set FAuxQty = (@reqQty-dbo.SumQty ( @FDetailID ))/dbo.coeff(@FDetailID), FEntrySelfP0133 = dbo.SumQty ( @FDetailID ) /dbo.coeff(@FDetailID), FEntrySelfP0134 = 1
where FDetailID = @FDetailID
end
select top(@top)
@maxnum AS "FID",
'' AS "FIndex",
FItemID, --物料内码
0 AS "FAuxPropID",
FBatchNo, --批号
FStockID, --仓库
FStockPlaceID, --仓位
FQtyInv, --即时库存基本数量
null AS "FSecUnitID", --浮动计量单位
FUnitID, --采购申请单计量单位
( FQtyInv / dbo.coeff ( @FDetailID ) ) AS "FQty", --即时库存常用计量单位数量
FSecCoefficient, --辅助计量单位换算率
SecQtyInv, --即时库存辅助计量单位数量
0 AS "FChangeQty_Base",
( MTOChange / dbo.coeff ( @FDetailID ) ) AS "FChangeQty",
case when FSecCoefficient = 0 then 0 else ( MTOChange / FSecCoefficient ) end AS "FChangeSecQty",
case when (fkfdate = '') then null else FKFDate end AS "FKFDate",
FKFPeriod,
case when (fkfdate = '') then null else FKFDate + fkfperiod end AS "FPeriodDate",
FromMTONo,
ToMTONo,
MTOChange,
0 AS "FSelectedProcID",
0 AS "FEntrySupply",
0 AS "FStockTypeID",
'' AS "FMrpNo"
into #t_mtoChangeEntry
from #inv
insert into t_MTOChangeEntry select * from #t_mtoChangeEntry
drop table #t_mtoChangeEntry
drop table #inv
end
四、一个示例
问题提出
1.客户使用了MTO物料管理 :
在不具备进行MRP/MTO计算的情况下, 无法使用系统本身的MTO自动批量调整功能 ;
2.库存中经常存在公用料 :
公用料有2种情况 : 1是入库时无法确认MTO计划跟踪号,全部指定成某个计划跟踪号; 2是期初的物料无法录入计划跟踪号 ;
3.问题(需求):
客户在下达采购申请单时, 如何快速判断库存中是否存在可用的公共料, 如果存在则自动调整MTO计划跟踪号, 及时关闭采购申请 ; 避免重复采购, 造成库存积压 .
大体思路
在单据审核的时候,
首先判断单据上是否存在可以调整的记录,
如果单据上至少有一行可以进行调整的记录,
则:
按顺序把每行物料拿出来,
到库存表里面去找MTO计划跟踪号为'999999'和''的记录,
将调整数量生成MTO调整单,
并更新采购申请单的关联数量,
以及行业务关闭标志和整单关闭标志
1.上下游单据之间的关联关系
- 生成下游单据之后, 采购申请单的执行情况, 行业务关闭标志状态, 单据头审核状态、关闭状态
- 下游单据删除之后, 如何恢复采购申请单的前置状态
2.多计量单位, 辅助计量单位
3.库存记录中存在多个批次, 多个仓库, 仓位 , 生产日期/保质期的情况
五、细节备忘
PORequest-采购申请单
采购申请单字段说明
--关于单据上数量字段的说明
select FInterID,
FEntryID,
FItemID,
FQty, -- (Qty)基本单位申请数量, ( = FAuxQty * FUnitID.FCoefficient ) 受数据库强制更新的约束, update时只能更新FAuxQty
FCommitQty, -- (Qty)基本单位`关联`数量, ( = 原值 + 下游单据`存时`关联的基本数量 ) []
FUnitID , -- (Qty)单据上`录入`的计量单位 ( 可以是基本计量单位, 也可以是其他常用计量单位 )
FAuxCommitQty, -- (Qty)常用单位关联数量, ( = FCommitQty / FUnitID.FCoefficient )
FAuxQty , -- (Qty)单据上`录入`的数量 ( 单位是FUnitID )
FSecCoefficient , -- (Qty)单据上`录入`的辅助计量单位换算率 ( tips : 物料的辅助计量单位只能有一个, 用过后就不能修改了, 所以单据上显示的辅助计量单位是自动显示物料的基础信息, 且不能修改)
FSecQty , -- (Qty)单据上`录入`的辅助计量单位对应的数量 ( tips : 辅助数量FSecQty和辅助换算率FSecCoefficient , 俩者之间会自动进行倒算)
FSecCommitQty , -- (Qty)辅助单位`关联`数量, ( = 原值 + 下游单据`保存`时关联的辅助数量 )
FOrderQty , -- (Qty)基本单位`执行`数量 ( = 原值 + 下游单据`审核`时关联的基本数量 )
FEntrySelfP0133, -- (Qty)自定义的字段2, ( = MTO调整单.FChangeQty ; 用来记录MTO调整的常用数量 )
FEntrySelfP0134 -- 自定义的字段3, (用来记录当前行是否进行了MTO调整, 1为已经调整)
from PORequestEntry
where FDetailID = @FDtailID
单据关闭标志说明
- Entry 单据体标志
Entry | 未关闭 | 手工关闭 | 自动关闭 |
---|---|---|---|
MRPClosed | 0 | 1 | 1 |
MRPAutoClosed | 1 | 0 | 1 |
- Head 整单标志
FClosed: 0 无下推或者部分下推 ; 1 全部下推 ;
FStatus: 0 未审核; 1 审核但是无关联或无关闭; 2 部分关闭 ; 3 关联或全部关闭
FChildren: 0 无关联单据 ; 1 有关联单据
ICInventory-存货表
存货余额表字段说明
select FItemID ,
FBatchNo , -- 批号
FStockID , -- 仓库
FQty , -- 基本单位数量 (存货表中的数量只统计基本单位数量和浮动计量单位数量)
FBal ,
FStockPlaceID , -- 仓位
FKFPeriod , -- 有效期
FKFDate , -- 生产日期
FQtyLock ,
FAuxPropID ,
FSecQty , -- 浮动计量单位数量
FMTONo , -- MTO计划跟踪号
FSupplyID
from ICInventory
T_MTOChange-MTO调整单
MTO调整单字段说明
select FID , -- 单据内码
FIndex ,
FItemID ,
FAuxPropID ,
FBatchNo , -- 要调整的批次
FStockID , -- 要调整的仓库
FSPID , -- 要调整的仓位
FBaseQty , -- (Qty)即时库存的基本单位数量 ( 来源于库存表, 数量精确定位到物料.批号.MTO计划跟踪号.库房.仓位 )
FSecUnitID , -- 'NULL' ; ( 好像没什么用 )
FUnitID , -- (Qty)单据上`录入`计量单位 ( = 采购申请单.FUnitID )
FQty , -- (Qty)根据FUnitID计算出来的即时数量, ( = FBaseQty * FUnitID.FCefficient )
FSecCoefficient , -- (Qty)辅助单位换算率 ( = FBaseQty / FSecQty )
FSecQty , -- (Qty)即时库存的辅助单位数量 ( 来源于库存表 )
FChangeQty_Base , -- 0.00不清楚有什么用
FChangeQty , -- (Qty)单据上`录入`调整数量 ( 单位是 FUnitID ) [ 本案取 = FChangeBaseQty / FUnitID.FCefficient ]
FChangeSecQty , -- (Qty)辅助单位调整数量 ( = FChangeBaseQty / FSecCoefficient )
FKFDate , -- 库存中物料的生产日期 ( case when 库存.KFFDate = '' then null else 库存.KFFDate end )
FKFPeriod , -- 库存中物料的有效期
FPeriodDate , -- 单据上物料的有效期至 ( case when 库存.fkfdate = '' then null else 库存.FKFDate + 库存.fkfperiod end )
FFromMTONo , -- 库存中物料的MTONo
FToMTONo , -- 单据上录入的MTONo
FChangeBaseQty -- (Qty)基本单位调整数量, ( = FChangeQty * FUnitID.FCefficient ) [ 本案取( 采购申请.FQty - 库存.FQty ) 比较之后的数量]
from t_MTOChangeEntry
六、成果展示
订单BOM如下
展示 - mto订单.png
测试账套下载
下载提取码:回复可见
Windows 10Chrome 81.0.4044.17
念念不忘,必有回响。
Windows 7Chrome 53.0.2785.116
写得很好,小白都可以上手
Windows 7Chrome 53.0.2785.116
11
Android 10Chrome 78.0.3904.62
谢谢了共享
Windows 10Chrome 85.0.4183.121
nice
Windows 10Chrome 62.0.3202.94
学习学习,想看看成果