存储过程模板

DROP PROCEDURE IF EXISTS getComparedWithLastMonth;


CREATE PROCEDURE getComparedWithLastMonth(IN currentMonthlyTime DATE,IN productName varchar)
BEGIN


--当前月主原料小计
DECLARE currentMonthTotalCost DOUBLE;


--当前月主原料板坯单耗
DECLARE currentMonthUnitConsume DOUBLE;


--当前月主原料板坯单价
DECLARE currentMonthUnitPrice DOUBLE;


--上月主原料小计
DECLARE lasttMonthTotalCost DOUBLE;


--上月主原料板坯单价
DECLARE lastMonthUnitPrice DOUBLE;


--当前月主原料价格差总和
DECLARE TotalPriceDiffer DOUBLE;


/*
IF i=1 THEN 


SELECT i FROM DUAL;
END IF;
*/


-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 当天班组游标
DECLARE cur CURSOR FOR SELECT DISTINCT crewID  FROM realcrewschedule;


-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


-- 得到总耗量
OPEN cur;


-- 开始循环
read_loop1 :LOOP
FETCH cur INTO _crewID;


-- 声明结束的时候
IF done THEN   
          LEAVE read_loop1;
END IF;

-- 结束循环
END LOOP;

-- 关闭游标
CLOSE cur;
SET done = FALSE;

END

例子：

DROP PROCEDURE IF EXISTS getComparedWithLastMonth;




CREATE PROCEDURE getComparedWithLastMonth(IN _currentMonthlyTime varchar(255),IN _productName varchar(255))
BEGIN


-- 存储过程( select dd  from dual )


DECLARE mainLastMonthAmount DOUBLE default 0;


DECLARE mainCurrentMonthAmount DOUBLE default 0;


-- 主原料损失（价格影响）
DECLARE mainPriceImpact DOUBLE default 0;


-- 主原料损失（消耗影响）
DECLARE mainConsumeImpact DOUBLE default 0;


-- 主原料损失（成本差异）
DECLARE mainCostDifference DOUBLE default 0;


--  得到J基础数据
 CAll ComparedWithThePreviousMonth(_currentMonthlyTime);


-- 当月实际


select round((a.TotalPreUnitCost - b.preUnitPrice),2) INTO  mainCurrentMonthAmount from 


(select sum(preUnitCost) as TotalPreUnitCost,elmtGroupName from  report_fiveFactorAnalysis where  productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%') and elmtGroupName = "主原料" ) as a 


LEFT JOIN (select preUnitPrice,elmtGroupName from report_fiveFactorAnalysis where productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%')and elmtName = "板坯")as b  on a.elmtGroupName = b.elmtGroupName;






-- 上月实际 


select  round((a.TotalComUnitCost - b.comUnitPrice),2) INTO mainLastMonthAmount from 


(select sum(comUnitCost) as TotalComUnitCost,elmtGroupName from report_fiveFactorAnalysis where productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%') and elmtGroupName = "主原料" ) as a 


LEFT JOIN  (select comUnitPrice,elmtGroupName from report_fiveFactorAnalysis where productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%') and elmtName = "板坯") as b  on a.elmtGroupName = b.elmtGroupName ;






-- 当前月主原料损失（价格影响）




select round((f.preUnitPrice *(d.preUnitconsume - 1) + f.toDiffer),2) INTO mainPriceImpact from (


(select toDiffer,preUnitPrice,elmtGroupName from 


(select round((a.totalPriceDiffer - b.priceDiffer ),2)as toDiffer, b.elmtGroupName from 


(select sum(priceDiffer ) as totalPriceDiffer, elmtGroupName from report_fiveFactorAnalysis where  productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%') and elmtGroupName = "主原料" ) as a


LEFT JOIN (select priceDiffer,elmtGroupName from report_fiveFactorAnalysis where productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%') and elmtName = "板坯") as b on a.elmtGroupName = b.elmtGroupName ) as e


LEFT JOIN (select preUnitPrice,elmtGroupName as elmtGroupName_1 from report_fiveFactorAnalysis where productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%')and elmtName = "板坯") as c on c.elmtGroupName_1 =  e.elmtGroupName) as f


LEFT JOIN (select preUnitconsume,elmtGroupName  from report_fiveFactorAnalysis where productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%') and elmtName = "板坯") as d on d.elmtGroupName = f.elmtGroupName);






--  主原料损失（成本差异）
select round((mainCurrentMonthAmount - mainLastMonthAmount),2) as mainCostDifference from dual;




--  主原料损失（消耗影响）


select round((mainCostDifference - mainPriceImpact),2) as mainConsumeImpact from dual;






--  除主原料板坯其他的主原料




--   先清空表report_otherInfo的所有数据,保证表中只有当前插入的数据


delete  from report_otherInfo;


insert into report_otherInfo (report_otherInfoID,elmtGroupName,LastMonthAmount,currentMonthAmount,costDifference,priceImpact,consumeImpact,scopeImpact,costImpact,elmtGroupType)
select uuid() as report_otherInfoID,elmtGroupName,LastMonthAmount,currentMonthAmount,costDifference,priceImpact,consumeImpact,scopeImpact,costImpact,elmtGroupType from(
select elmtGroupName,
round(sum(comUnitCost) ,2) as LastMonthAmount,
round(sum(preUnitCost) ,2) as currentMonthAmount,
round((sum(preUnitCost) -  sum(comUnitCost)) ,2) as costDifference, 
round(sum(priceDiffer),2) as priceImpact,
round(sum(consumeDiffer),2) as consumeImpact,
round(sum(scopeDiffer),2)as scopeImpact,
round(sum(costDiffer),2)as costImpact,
elmtGroupType 
from report_fiveFactorAnalysis where productName = _productName and preTime like CONCAT('%',_currentMonthlyTime,'%') group by elmtGroupID) as a;


--    从表report_otherInfo删除主原料


delete  from report_otherInfo where elmtGroupName like "%主原料%"  and elmtGroupType = 0;


--   主原料损失


insert into report_otherInfo (report_otherInfoID,elmtGroupName,LastMonthAmount,currentMonthAmount,costDifference,priceImpact,consumeImpact,scopeImpact,costImpact,elmtGroupType)
select uuid() as report_otherInfoID, 
"主原料损失" as elmtGroupName,
mainLastMonthAmount,
mainCurrentMonthAmount,
mainCostDifference,
mainPriceImpact,
mainConsumeImpact,
0 as scopeImpact,
0 as costImpact,
0 as elmtGroupType from dual ;


--   变动成本小计


insert into report_otherInfo (report_otherInfoID,elmtGroupName,LastMonthAmount,currentMonthAmount,costDifference,priceImpact,consumeImpact,scopeImpact,costImpact,elmtGroupType)
select uuid() as report_otherInfoID,
"变动成本小计" as elmtGroupName,
round(sum(LastMonthAmount),2) as LastMonthAmount,
round(sum(currentMonthAmount),2) as currentMonthAmount,
round(sum(costDifference),2) as costDifference,
round(sum(priceImpact),2) as priceImpact,
round(sum(consumeImpact),2) as consumeImpact,
round(sum(scopeImpact),2) as scopeImpact,
round(sum(costImpact),2) as costImpact,
0.5 as elmtGroupType 
from report_otherInfo where elmtGroupType = 0;




--  固定成本小计




insert into report_otherInfo (report_otherInfoID,elmtGroupName,LastMonthAmount,currentMonthAmount,costDifference,priceImpact,consumeImpact,scopeImpact,costImpact,elmtGroupType)
select uuid() as report_otherInfoID,
"固定成本小计" as elmtGroupName,
round(sum(LastMonthAmount),2) as LastMonthAmount,
round(sum(currentMonthAmount),2) as currentMonthAmount,
round(sum(costDifference),2) as costDifference,
round(sum(priceImpact),2) as priceImpact,
round(sum(consumeImpact),2) as consumeImpact,
round(sum(scopeImpact),2) as scopeImpact,
round(sum(costImpact),2) as costImpact,
1.5 as elmtGroupType 
from report_otherInfo where elmtGroupType = 1;


--   合计


insert into report_otherInfo (report_otherInfoID,elmtGroupName,LastMonthAmount,currentMonthAmount,costDifference,priceImpact,consumeImpact,scopeImpact,costImpact,elmtGroupType)
select uuid() as report_otherInfoID,
"合计" as elmtGroupName,
round(sum(LastMonthAmount),2) as LastMonthAmount,
round(sum(currentMonthAmount),2) as currentMonthAmount,
round(sum(costDifference),2) as costDifference,
round(sum(priceImpact),2) as priceImpact,
round(sum(consumeImpact),2) as consumeImpact,
round(sum(scopeImpact),2) as scopeImpact,
round(sum(costImpact),2) as costImpact,
2 as elmtGroupType 
from report_otherInfo;




END