更新库存SQL算法版
-
页面布局
请选择仓库(下拉选择) | 填写设备 | 填写出库数量 | | 填写设备 | 填写出库数量 | | 填写设备 | 填写出库数量 | | 填写设备 | 填写出库数量 | | 需求描述:首先选择仓库,然后依客户需要,填写要出库的设备并填写出库数量。如右图所示。 - 功能描述:批量出库时,对于库存不够的设备要提示该设备库存不够,并撤消本次出库操作,对于库存中没有的设备也要提示该设备中不存在,否则提交。注意客户可以选择一样的设备。
- 技术难点:在库存不够时给出提示并撤消;对于多个相同的设备怎么样快速汇总。
- 关键词:存储过程,函数,游标,事务。
- 数据库设计
- 库房表
库房表tb_Storeroom
P_StoreroomId(库房ID) | StoreroomName(库房名称) | 1 | 库房1 | 2 | 库房2 | - 设备表
设备表tb_Equipment
P_EquipmentId(设备id) | EquipmentName(设备名称) | 1 | 飞毛腿导弹 | 2 | 爱国者导弹 | - 库存表
库存表tb_Stock
P_StockId(库存ID) | F_EquipmentId(设备ID) | Mount(库存数量) | F_StoreroomId(所属库房) | 1 | 1 | 10 | 1 | 2 | 1 | 15 | 1 | 存储过程实现(内有详细注释,不再另外说明了)
Create
Procedure
sp_ComputeStock
@StoreroomId
INT
=
NULL
,
--
库房编号
@EquipmentIds
VARCHAR
(
1000
),
--
设备编号,各设备编号间用逗号隔开,例如:1,2,3,4
@EquipmentMount
VARCHAR
(
1000
)
--
设备数量,各设备数量间用逗号隔开,例如:11,15,20,5
AS
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SET
NOCOUNT
ON
SET
CURSOR_CLOSE_ON_COMMIT
OFF
--
设置手动关闭游标,使事务不会影响到游标的打开和关闭
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
,
'
,
'
)
--
拆分设备编号,并将结果插入到设备编号表(表变量)中
INSERT
INTO
@tb_EquipmentMount
SELECT
*
FROM
dbo.f_splitSTR(
@EquipmentMount
,
'
,
'
)
--
拆分设备数量,并将结果插入到设备数量表(表变量)中
DECLARE
@au_equipmentId
INT
,
--
设备编号
@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
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
FETCH
NEXT
FROM
stockBill_cursor
INTO
@au_equipmentId
,
@au_equipmentMount
--
将当前设备编号保存到@au_equipmentId中,将当前设备数量保存到@au_equipmentMount中
BEGIN
TRANSACTION
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
WHILE
@@FETCH_STATUS
=
0
BEGIN
SET
@var_Mount
=
NULL
SELECT
@var_Mount
=
Mount
--
根据库房号和设备编号从库存表中找出该设备的存库数量,并保存到@var_Mount中
FROM
tb_Stock
WHERE
F_StoreroomId
=
@StoreroomId
AND
F_EquipmentId
=
@au_equipmentId
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
IF
(
@au_equipmentMount
<=
@var_Mount
)
AND
(
@var_Mount
IS
NOT
NULL
)
--
所取数量与库存数量比较,若小于等于库存数量,更新库存表。
UPDATE
tb_Stock
SET
Mount
=
Mount
-
@au_equipmentMount
WHERE
F_StoreroomId
=
@StoreroomId
AND
F_EquipmentId
=
@au_equipmentId
ELSE
BEGIN
--
否则,不更新库存表,并回滚,关闭游标,返回该设备编号(库存不够)
ROLLBACK
TRANSACTION
CLOSE
stockBill_cursor
DEALLOCATE
stockBill_cursor
IF
@var_Mount
IS
NULL
BEGIN
PRINT
'
库房
'
+
LTRIM
(
STR
(
@StoreroomId
))
+
'
中不存在设备编号为
'
+
LTRIM
(
STR
(
@au_equipmentId
))
+
'
设备
'
SET
@au_equipmentId
=
-
1
--
'返回-1表明有不存在的设备'
END
ELSE
PRINT
(
'
库房编号为
'
+
LTRIM
(
STR
(
@StoreroomId
))
+
'
设备编号为
'
+
LTRIM
(
STR
(
@au_equipmentId
))
+
'
的设备库存不够
'
)
RETURN
@au_equipmentId
END
--
FETCH
NEXT
FROM
stockBill_cursor
INTO
@au_equipmentId
,
@au_equipmentMount
END
COMMIT
TRANSACTION
--
提交事务
CLOSE
stockBill_cursor
--
关闭游标
DEALLOCATE
stockBill_cursor
RETURN
0
--
成功返回
函数
Create
FUNCTION
f_splitSTR
(
@s
varchar
(
8000
),
--
待分拆的字符串
@split
varchar
(
10
))
--
数据分隔符
RETURNS
@re
TABLE
(col
varchar
(
100
))
AS
BEGIN
Declare
@splitlen
int
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SET
@splitlen
=
LEN
(
@split
+
'
a
'
)
-
2
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
WHILE
CHARINDEX
(
@split
,
@s
)
>
0
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
BEGIN
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Insert
@re
VALUES
(
LEFT
(
@s
,
CHARINDEX
(
@split
,
@s
)
-
1
))
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SET
@s
=
STUFF
(
@s
,
1
,
CHARINDEX
(
@split
,
@s
)
+
@splitlen
,
''
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
END
INSERT
@re
Values
(
@s
)
RETURN
END
库存视图
Create
VIEW
V_Stock
AS
SELECT
P_EquipmentID
AS
EquipmentID,
EquipmentName,
Mount,
F_StoreroomId
AS
StoreroomId,
StoreroomName
from
tb_Stock
INNER
JOIN
tb_Equipment
ON
tb_Stock.F_EquipmentId
=
tb_Equipment.P_EquipmentId
INNER
JOIN
tb_Storeroom
ON
tb_Stock.F_StoreroomId
=
tb_Storeroom.P_StoreroomId
|