mysql 过程调用函数_mysql 存储过程调用函数

CREATEDEFINER=`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))LANGUAGESQL NOTDETERMINISTIC CONTAINSSQL SQLSECURITYDEFINER COMMENT'下架操作存储过程'BEGINDECLAREv_GCIDint;DECLAREv_skuIDint;DECLAREv_duIDint;DECLAREv_volumedecimal(12,2);DECLAREv_CBidint;DECLAREv_CBidHoldint;DECLAREv_useableBoxintdefault0;DECLAREv_countOnStackint;DECLAREv_oscountint;DECLAREv_oiIDint;DECLAREv_osIDint;DECLAREv_orderIDint;DECLAREv_isFlagintdefault0;DECLAREv_holdoprCtint;DECLAREv_cvsIDint;DECLAREv_usedSpacedecimal(12,7);DECLAREv_GCCountHoldint;DECLAREv_GCIDHoldint;DECLAREv_downsheldedint;DECLAREv_alldownsheldint;DECLAREv_usedoprCtOldint;DECLAREv_resultintdefault0;DECLAREv_isdownsheldListintdefault0;-- 根据sku查询该产品的ID，所属客户，体积等信息selectskuID,duID,volumeintov_skuID,v_duID,v_volumefromskuListwheresku=p_sku;-- 根据货箱条码查询货箱IDselectCBidintov_CBidfromStorBoxwhereCbarcode=p_Cbarcode;-- 根据货箱ID和产品ID确定容器selectGCIDintov_GCIDfromglobalContainerwhereCBid=v_CBidandskuID=v_skuID;-- 根据产品ID和容器ID查询是否在库selectcount(osID),osCount,osIDintov_countOnStack,v_oscount,v_osIDfromonStackwhereskuID=v_skuIDandGCID=v_GCID;-- 查询暂存货箱IDselectCBidintov_CBidHoldfromStorBoxwhereCbarcode=p_CbarcodeHold;-- 验证暂存货箱是否可用(0：不可用，1：直接插入容器表，2：更新容器表) 将函数返回值赋值给变量selectuseableBox(v_CBidHold,v_skuID,v_GCCountHold,v_GCIDHold)intov_useableBox;-- 根据下架清单编号和产品编号查询已经下架的数量selectsum(GCCount)intov_downsheldedfromglobalContainerwhererelationShipKey=p_dsID2andskuID=v_skuID;-- 根据下架清单编号查询总下架数量selectdsCountintov_alldownsheldfromdownSheldListwheredsID2=p_dsID2;-- 判断下架清单的所有数量是否全部下架完(1：已经下架和本次下架的数量小于等于下架清单下架数量)if(v_downshelded+p_dscount)<=v_alldownsheldorv_downsheldedisnullthen setv_isdownsheldList=1;else setv_isdownsheldList=0;endif;-- 可以本次下架ifv_isdownsheldList=1then-- 暂存货箱可用 并且 库中有此产品ifv_useableBox!=0ANDv_countOnStack>0then selectoiIDintov_oiIDfromdownSheldListwheredsID2=p_dsID2;selectorderIDintov_orderIDfromorderItemwhereoiID=v_oiID;-- 在库数量大于要下架数量ifv_oscount>p_dscountthen updateonStacksetosCount=v_oscount-p_dscountwhereskuID=v_skuIDandGCID=v_GCID;setv_isFlag=1;-- 在库数量等于要下架数量elseifv_oscount=p_dscountthen deletefromonStackwhereosID=v_osID;setv_isFlag=1;-- 不够下架else setv_isFlag=0;endif;-- 可以下架ifv_isFlag=1then insertintopackageList(dsID2,orderID,dsCount,skuID)values(p_dsID2,v_orderID,p_dscount,v_skuID);selectholdoprCt,cvsID,usedSpace,usedoprCtintov_holdoprCt,v_cvsID,v_usedSpace,v_usedoprCtOldfromcustomerValidSourcewhereduID=v_duID;updatecustomerValidSourcesetholdoprCt=v_holdoprCt-p_dscount,usedSpace=v_usedSpace-v_volume*p_dscount/1000000,usedoprCt=v_usedoprCtOld+p_dscountwhereduID=v_duID;insertintocustomerValidSourceDetail(operateType,cvsID,srcType,srcCt)values(1,v_cvsID,2,p_dscount);insertintocustomerValidSourceDetail(operateType,cvsID,srcType,srcCt)values(0,v_cvsID,0,v_volume*p_dscount/1000000);-- 容器表中没有这个容器，直接添加ifv_useableBox=1then insertintoglobalContainer(skuID,CBid,onState,GCCount,relationShipKey)values(v_skuID,v_CBidHold,4,p_dscount,p_dsID2);-- 容器表里有，并且可用，更新该容器里的数据elseifv_useableBox=2then updateglobalContainersetGCCount=(v_GCCountHold+p_dscount),relationShipKey=p_dsID2whereGCID=v_GCIDHold;endif;setv_result=1;else setv_result=0;endif;elseifv_useableBox=0then setv_result=4;else setv_result=2;endif;endif;else setv_result=5;endif;selectv_result;END