Create
Procedure
sp_ComputeStock
@StoreroomId INT = NULL , -- 库房编号
@EquipmentIds VARCHAR ( 5000 ) = NULL , -- 设备编号,各设备编号间用逗号隔开,例如:1,2,3,4
@EquipmentMount VARCHAR ( 5000 ) = NULL , -- 设备数量,各设备数量间用逗号隔开,例如:11,15,20,5
@Out_equipIdNotExists VARCHAR ( 5000 ) OUTPUT, -- 不存在的设备ID,各设备用逗号隔开
@Out_equipNameNotExists VARCHAR ( 5000 ) OUTPUT, -- 不存在的设备名称,各设备用逗号隔开
@Out_equipIdNotEnough VARCHAR ( 5000 ) OUTPUT, -- 库存不够的设备ID,各设备用逗号隔开
@Out_equipNameNotEnough VARCHAR ( 5000 ) OUTPUT, -- 库存不够的设备名称,各设备用逗号隔开
@Out_Stockpile VARCHAR ( 5000 ) OUTPUT -- 设备ID的库存量,各设备用逗号隔开
AS
SET NOCOUNT ON
SET CURSOR_CLOSE_ON_COMMIT OFF -- 设置手动关闭游标,使事务不会影响到游标的打开和关闭
DECLARE
@Empty CHAR ( 1 ), -- 空串
@Split CHAR ( 1 ), -- 分隔符
@equipName VARCHAR ( 100 )
SET @Empty = ''
SET @Split = ' , '
SET @Out_equipIdNotExists = @Empty -- 初始化为空串
SET @Out_equipIdNotEnough = @Empty
SET @Out_equipNameNotExists = @Empty
SET @Out_equipNameNotEnough = @Empty
SET @Out_Stockpile = @Empty
DECLARE
@tb_EquipmentIds TABLE ( [ Id ] INT IDENTITY ( 1 , 1 ),EquipmentId INT ) -- 表变量,设备编号表
DECLARE
@tb_EquipmentMount TABLE ( [ Id ] INT IDENTITY ( 1 , 1 ),EquipmentMount INT ) -- 表变量,设备数量表
INSERT INTO @tb_EquipmentIds SELECT * FROM dbo.f_splitSTR( @EquipmentIds , @Split )
-- 拆分设备编号,并将结果插入到设备编号表(表变量)中
INSERT INTO @tb_EquipmentMount SELECT * FROM dbo.f_splitSTR( @EquipmentMount , @Split )
-- 拆分设备数量,并将结果插入到设备数量表(表变量)中
DECLARE @au_equipmentId VARCHAR ( 10 ), -- 设备编号
@au_equipmentMount INT , -- 设备数量
@var_Mount INT -- 库存数量
-- 将设备编号表和设备数量表关联,按设备编号分组统计出各设备的出库数量
-- 注意遍历了两次该游标,第一次用来检查设备,第二次用来更新设备
-- (若第一次遍历设备通过后,才进行第二次,否则不进行)
DECLARE stockBill_cursor CURSOR LOCAL FAST_FORWARD FOR -- 声明并定义快速只进的本地游标
SELECT
EquipmentId,
SUM (EquipmentMount) AS Mount
FROM
@tb_EquipmentIds AS a
INNER JOIN
@tb_EquipmentMount AS b
ON
a. [ ID ] = b. [ ID ]
GROUP BY EquipmentId
OPEN stockBill_cursor
-- 第一次遍历:检查设备是否在库房中存在,检查设备的库存是否不足。若存在且库存够,
-- 才进行第二次遍历(更新库存)
FETCH NEXT FROM stockBill_cursor
INTO @au_equipmentId , @au_equipmentMount
-- 将当前设备编号保存到@au_equipmentId中,将当前设备数量保存到@au_equipmentMount中
WHILE @@FETCH_STATUS = 0
BEGIN
SET @var_Mount = NULL
-- 根据库房号和设备编号从库存表中找出该设备的存库数量,并保存到@var_Mount中
SELECT
@var_Mount = Mount
FROM
tb_Stock
WHERE
F_StoreroomId = @StoreroomId
AND
F_EquipmentId = @au_equipmentId
SELECT @equipName = EquipmentName FROM dbo.tb_Equipment WHERE P_EquipmentId = @au_equipmentId
IF ( @var_Mount IS NULL ) -- 若@var_Mount=NULL表明不存在该设备
BEGIN
SET @Out_equipIdNotExists = @Out_equipIdNotExists + @au_equipmentId + @Split
SET @Out_equipNameNotExists = @Out_equipNameNotExists + @equipName + @Split
END
ELSE
BEGIN -- 否则存在该设备
-- 所取数量与库存数量比较,若大于库存数量, 将该设备编号记录下来。
IF ( @au_equipmentMount > @var_Mount )
BEGIN
SET @Out_equipIdNotEnough = @Out_equipIdNotEnough + @au_equipmentId + @Split -
- 记录库存不够的设备,用逗号隔开
SET @Out_equipNameNotEnough = @Out_equipNameNotEnough + @equipName + @Split
SET @Out_Stockpile = @Out_Stockpile + LTRIM ( STR ( @var_Mount )) + @Split
END
END -- IF(@var_Mount IS NULL)
FETCH NEXT FROM stockBill_cursor -- 取下一条
INTO @au_equipmentId , @au_equipmentMount
END -- WHILE
-- 若@Out_equipmentNotExists和@Out_equipmentNotEnough都是空串,表明
-- 即不存在库存不够的设备, 也没有库房中不存在的设备
IF ( @Out_equipIdNotExists = @Empty AND @Out_equipIdNotEnough = @Empty )
BEGIN
CLOSE stockBill_cursor -- 关闭游标
OPEN stockBill_cursor -- 再次打开游标
FETCH NEXT FROM stockBill_cursor -- 第二次遍历,更新库存.
INTO @au_equipmentId , @au_equipmentMount
BEGIN TRANSACTION
WHILE @@FETCH_STATUS = 0
BEGIN
SET @var_Mount = NULL
-- 根据库房号和设备编号从库存表中找出该设备的存库数量,并保存到@var_Mount中
SELECT
@var_Mount = Mount
FROM
tb_Stock
WHERE
F_StoreroomId = @StoreroomId
AND
F_EquipmentId = @au_equipmentId
UPDATE
tb_Stock
SET
Mount = Mount - @au_equipmentMount
WHERE
F_StoreroomId = @StoreroomId
AND
F_EquipmentId = @au_equipmentId
IF @@ERROR <> 0 -- 未知错误
BEGIN
ROLLBACK TRANSACTION -- 回滚
CLOSE stockBill_cursor -- 关闭游标
DEALLOCATE stockBill_cursor
RETURN @@ERROR -- 返回错误代码
END
FETCH NEXT FROM stockBill_cursor -- 取下一条
INTO @au_equipmentId , @au_equipmentMount
END -- WHILE
COMMIT TRANSACTION -- 提交事务
END -- IF(@Out_equipmentNotExists = @Empty AND @Out_equipmentNotEnough = @Empty)
CLOSE stockBill_cursor -- 关闭游标
DEALLOCATE stockBill_cursor
RETURN 0 -- 成功返回(执行该过程没有系统出错错误)
@StoreroomId INT = NULL , -- 库房编号
@EquipmentIds VARCHAR ( 5000 ) = NULL , -- 设备编号,各设备编号间用逗号隔开,例如:1,2,3,4
@EquipmentMount VARCHAR ( 5000 ) = NULL , -- 设备数量,各设备数量间用逗号隔开,例如:11,15,20,5
@Out_equipIdNotExists VARCHAR ( 5000 ) OUTPUT, -- 不存在的设备ID,各设备用逗号隔开
@Out_equipNameNotExists VARCHAR ( 5000 ) OUTPUT, -- 不存在的设备名称,各设备用逗号隔开
@Out_equipIdNotEnough VARCHAR ( 5000 ) OUTPUT, -- 库存不够的设备ID,各设备用逗号隔开
@Out_equipNameNotEnough VARCHAR ( 5000 ) OUTPUT, -- 库存不够的设备名称,各设备用逗号隔开
@Out_Stockpile VARCHAR ( 5000 ) OUTPUT -- 设备ID的库存量,各设备用逗号隔开
AS
SET NOCOUNT ON
SET CURSOR_CLOSE_ON_COMMIT OFF -- 设置手动关闭游标,使事务不会影响到游标的打开和关闭
DECLARE
@Empty CHAR ( 1 ), -- 空串
@Split CHAR ( 1 ), -- 分隔符
@equipName VARCHAR ( 100 )
SET @Empty = ''
SET @Split = ' , '
SET @Out_equipIdNotExists = @Empty -- 初始化为空串
SET @Out_equipIdNotEnough = @Empty
SET @Out_equipNameNotExists = @Empty
SET @Out_equipNameNotEnough = @Empty
SET @Out_Stockpile = @Empty
DECLARE
@tb_EquipmentIds TABLE ( [ Id ] INT IDENTITY ( 1 , 1 ),EquipmentId INT ) -- 表变量,设备编号表
DECLARE
@tb_EquipmentMount TABLE ( [ Id ] INT IDENTITY ( 1 , 1 ),EquipmentMount INT ) -- 表变量,设备数量表
INSERT INTO @tb_EquipmentIds SELECT * FROM dbo.f_splitSTR( @EquipmentIds , @Split )
-- 拆分设备编号,并将结果插入到设备编号表(表变量)中
INSERT INTO @tb_EquipmentMount SELECT * FROM dbo.f_splitSTR( @EquipmentMount , @Split )
-- 拆分设备数量,并将结果插入到设备数量表(表变量)中
DECLARE @au_equipmentId VARCHAR ( 10 ), -- 设备编号
@au_equipmentMount INT , -- 设备数量
@var_Mount INT -- 库存数量
-- 将设备编号表和设备数量表关联,按设备编号分组统计出各设备的出库数量
-- 注意遍历了两次该游标,第一次用来检查设备,第二次用来更新设备
-- (若第一次遍历设备通过后,才进行第二次,否则不进行)
DECLARE stockBill_cursor CURSOR LOCAL FAST_FORWARD FOR -- 声明并定义快速只进的本地游标
SELECT
EquipmentId,
SUM (EquipmentMount) AS Mount
FROM
@tb_EquipmentIds AS a
INNER JOIN
@tb_EquipmentMount AS b
ON
a. [ ID ] = b. [ ID ]
GROUP BY EquipmentId
OPEN stockBill_cursor
-- 第一次遍历:检查设备是否在库房中存在,检查设备的库存是否不足。若存在且库存够,
-- 才进行第二次遍历(更新库存)
FETCH NEXT FROM stockBill_cursor
INTO @au_equipmentId , @au_equipmentMount
-- 将当前设备编号保存到@au_equipmentId中,将当前设备数量保存到@au_equipmentMount中
WHILE @@FETCH_STATUS = 0
BEGIN
SET @var_Mount = NULL
-- 根据库房号和设备编号从库存表中找出该设备的存库数量,并保存到@var_Mount中
SELECT
@var_Mount = Mount
FROM
tb_Stock
WHERE
F_StoreroomId = @StoreroomId
AND
F_EquipmentId = @au_equipmentId
SELECT @equipName = EquipmentName FROM dbo.tb_Equipment WHERE P_EquipmentId = @au_equipmentId
IF ( @var_Mount IS NULL ) -- 若@var_Mount=NULL表明不存在该设备
BEGIN
SET @Out_equipIdNotExists = @Out_equipIdNotExists + @au_equipmentId + @Split
SET @Out_equipNameNotExists = @Out_equipNameNotExists + @equipName + @Split
END
ELSE
BEGIN -- 否则存在该设备
-- 所取数量与库存数量比较,若大于库存数量, 将该设备编号记录下来。
IF ( @au_equipmentMount > @var_Mount )
BEGIN
SET @Out_equipIdNotEnough = @Out_equipIdNotEnough + @au_equipmentId + @Split -
- 记录库存不够的设备,用逗号隔开
SET @Out_equipNameNotEnough = @Out_equipNameNotEnough + @equipName + @Split
SET @Out_Stockpile = @Out_Stockpile + LTRIM ( STR ( @var_Mount )) + @Split
END
END -- IF(@var_Mount IS NULL)
FETCH NEXT FROM stockBill_cursor -- 取下一条
INTO @au_equipmentId , @au_equipmentMount
END -- WHILE
-- 若@Out_equipmentNotExists和@Out_equipmentNotEnough都是空串,表明
-- 即不存在库存不够的设备, 也没有库房中不存在的设备
IF ( @Out_equipIdNotExists = @Empty AND @Out_equipIdNotEnough = @Empty )
BEGIN
CLOSE stockBill_cursor -- 关闭游标
OPEN stockBill_cursor -- 再次打开游标
FETCH NEXT FROM stockBill_cursor -- 第二次遍历,更新库存.
INTO @au_equipmentId , @au_equipmentMount
BEGIN TRANSACTION
WHILE @@FETCH_STATUS = 0
BEGIN
SET @var_Mount = NULL
-- 根据库房号和设备编号从库存表中找出该设备的存库数量,并保存到@var_Mount中
SELECT
@var_Mount = Mount
FROM
tb_Stock
WHERE
F_StoreroomId = @StoreroomId
AND
F_EquipmentId = @au_equipmentId
UPDATE
tb_Stock
SET
Mount = Mount - @au_equipmentMount
WHERE
F_StoreroomId = @StoreroomId
AND
F_EquipmentId = @au_equipmentId
IF @@ERROR <> 0 -- 未知错误
BEGIN
ROLLBACK TRANSACTION -- 回滚
CLOSE stockBill_cursor -- 关闭游标
DEALLOCATE stockBill_cursor
RETURN @@ERROR -- 返回错误代码
END
FETCH NEXT FROM stockBill_cursor -- 取下一条
INTO @au_equipmentId , @au_equipmentMount
END -- WHILE
COMMIT TRANSACTION -- 提交事务
END -- IF(@Out_equipmentNotExists = @Empty AND @Out_equipmentNotEnough = @Empty)
CLOSE stockBill_cursor -- 关闭游标
DEALLOCATE stockBill_cursor
RETURN 0 -- 成功返回(执行该过程没有系统出错错误)
本算法是1.0版本的改进版。1.0版中对于多个设备库存不够或不存在的情况,执行该算法一次只能提示一个,不能将所有的库存不够或者不存在的设备都一一显示出来(设备名称)。给用户的操作带来不便,为此改进了该算法。物理表,函数详见《更新库存SQL算法1.0版》.