mysql 存储过程调用函数

代码部分：

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 customerValidSourceDetail (operateType,cvsID,srcType,srcCt) values(1,v_cvsID,2,p_dscount);
insert into customerValidSourceDetail (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

截图效果：