代码部分:
CREATE DEFINER=`root`@`%` PROCEDURE `downSheldListPro`(IN `p_sku` VARCHAR(50), IN `p_Cbarcode` VARCHAR(50), IN `p_dsID2` INT, IN `p_dscount` INT, IN `p_CbarcodeHold` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '下架操作存储过程'
BEGIN
DECLARE v_GCID int;
DECLARE v_skuID int;
DECLARE v_duID int;
DECLARE v_volume decimal(12,2);
DECLARE v_CBid int;
DECLARE v_CBidHold int;
DECLARE v_useableBox int default 0;
DECLARE v_countOnStack int;
DECLARE v_oscount int;
DECLARE v_oiID int;
DECLARE v_osID int;
DECLARE v_orderID int;
DECLARE v_isFlag int default 0;
DECLARE v_holdoprCt int;
DECLARE v_cvsID int;
DECLARE v_usedSpace decimal(12,7);
DECLARE v_GCCountHold int;
DECLARE v_GCIDHold int;
DECLARE v_downshelded int;
DECLARE v_alldownsheld int;
DECLARE v_usedoprCtOld int;
DECLARE v_result int default 0;
DECLARE v_isdownsheldList int default 0;
-- 根据sku查询该产品的ID,所属客户,体积等信息
select skuID,duID,volume into v_skuID,v_duID,v_volume
from skuList where sku = p_sku;
-- 根据货箱条码查询货箱ID
select CBid into v_CBid from StorBox
where Cbarcode = p_Cbarcode;
-- 根据货箱ID和产品ID确定容器
select GCID into v_GCID from globalContainer
where CBid = v_CBid and skuID = v_skuID;
-- 根据产品ID和容器ID查询是否在库
select count(osID),osCount,osID into v_countOnStack,v_oscount,v_osID from onStack
where skuID = v_skuID and GCID = v_GCID;
-- 查询暂存货箱ID
select CBid into v_CBidHold from StorBox
where Cbarcode = p_CbarcodeHold;
-- 验证暂存货箱是否可用(0:不可用,1:直接插入容器表,2:更新容器表) 将函数返回值赋值给变量
select useableBox(v_CBidHold, v_skuID,v_GCCountHold,v_GCIDHold) into v_useableBox;
-- 根据下架清单编号和产品编号查询已经下架的数量
select sum(GCCount) into v_downshelded from globalContainer
where relationShipKey = p_dsID2 and skuID = v_skuID;
-- 根据下架清单编号查询总下架数量
select dsCount into v_alldownsheld from downSheldList
where dsID2 = p_dsID2;
-- 判断下架清单的所有数量是否全部下架完(1:已经下架和本次下架的数量小于等于下架清单下架数量)
if (v_downshelded+p_dscount) <= v_alldownsheld or v_downshelded is null then
set v_isdownsheldList = 1;
else
set v_isdownsheldList = 0;
end if;
-- 可以本次下架
if v_isdownsheldList = 1 then
-- 暂存货箱可用 并且 库中有此产品
if v_useableBox != 0 AND v_countOnStack >0 then
select oiID into v_oiID from downSheldList
where dsID2 = p_dsID2;
select orderID into v_orderID from orderItem
where oiID = v_oiID;
-- 在库数量大于要下架数量
if v_oscount > p_dscount then
update onStack set osCount = v_oscount-p_dscount
where skuID = v_skuID and GCID = v_GCID;
set v_isFlag = 1;
-- 在库数量等于要下架数量
elseif v_oscount = p_dscount then
delete from onStack where osID = v_osID;
set v_isFlag = 1;
-- 不够下架
else
set v_isFlag = 0;
end if;
-- 可以下架
if v_isFlag =1 then
insert into packageList (dsID2,orderID,dsCount,skuID) values(p_dsID2,v_orderID,p_dscount,v_skuID);
select holdoprCt,cvsID,usedSpace,usedoprCt into v_holdoprCt,v_cvsID,v_usedSpace,v_usedoprCtOld from customerValidSource where duID = v_duID;
update customerValidSource set holdoprCt = v_holdoprCt-p_dscount,usedSpace = v_usedSpace-v_volume*p_dscount/1000000,usedoprCt = v_usedoprCtOld+p_dscount
where duID = v_duID;
insert into customerValidSourceDetai l (operateType,cvsID,srcType,srcCt) values(1,v_cvsID,2,p_dscount);
insert into customerValidSourceDetai l (operateType,cvsID,srcType,srcCt) values(0,v_cvsID,0,v_volume*p_dscount/1000000);
-- 容器表中没有这个容器,直接添加
if v_useableBox = 1 then
insert into globalContainer (skuID,CBid,onState,GCCount,relationShipKey) values(v_skuID,v_CBidHold,4,p_dscount,p_dsID2);
-- 容器表里有,并且可用,更新该容器里的数据
elseif v_useableBox =2 then
update globalContainer set GCCount = (v_GCCountHold+p_dscount),relationShipKey=p_dsID2 where GCID = v_GCIDHold;
end if;
set v_result = 1;
else
set v_result = 0;
end if;
else
if v_useableBox = 0 then
set v_result = 4;
else
set v_result = 2;
end if;
end if;
else
set v_result = 5;
end if;
select v_result;
END
截图效果: