Mysql存储过程

一：带变量

delimiter $$

CREATE  PROCEDURE `echarts`()

begin
DECLARE member_count int DEFAULT 0;
SELECT COUNT(id) INTO member_count FROM common_party_member;
delete from common_echarts;
Insert into common_echarts
select case when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 18 AND 29 then '18-29岁'
when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 30 AND 39 then '30-39岁'
when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 40 AND 49 then '40-49岁'
when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 50 AND 59 then '50-59岁'
when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 60 AND 69 then '60-69岁'
when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 70 AND 79 then '70-79岁'
when TIMESTAMPDIFF(YEAR, birthday, CURDATE()) > 80 then '80岁以上' end as text,count(id) count,member_count total,ROUND(count(id)/member_count*100,1) percent,'age' type from common_party_member GROUP BY text
union
select education text,count(id) count,member_count total,ROUND(count(id)/member_count*100,1) percent,'education' type
from common_party_member GROUP BY text
end

delimiter;

二：带输入参数

delimiter $$

CREATE  PROCEDURE `group_cost_statistics`(in begin_date datetime,in end_date datetime,in groupid varchar(36))

begin 

select max(person_num) person_num,max(fate) fate,max(budget) budget,max(foodcost) foodcost,max(livingcost)livingcost,max( clothingcost)clothingcost ,a.group_id,m.case_name group_name,max(total) total
from (
select group_id,count(id)person_num, max(CASE
WHEN leave_date is null THEN
datediff(now(),checkin_date)
ELSE
datediff(leave_date,checkin_date)
END) fate,sum(CASE
WHEN leave_date is null THEN
datediff(now(),checkin_date)
ELSE
datediff(leave_date,checkin_date)
END*140) budget,0 foodcost,0 livingcost,0 clothingcost,0 total from rm_checkin
where datediff(now(),checkin_date)>0 and (begin_date is null or checkin_date >= begin_date) and (end_date is null or checkin_date <= end_date)
GROUP BY group_id
UNION
select group_id,0 person_num,0 fate,0 budget,
sum(case when consumption_type='食品申请' then consumption_price end)foodcost,
sum(case when consumption_type='物品申请' then consumption_price end)livingcost ,
sum(case when consumption_type='物品领用' then consumption_price end)clothingcost,
sum(consumption_price) total
from sp_group_bill where audit_state=1 and (begin_date is null or consumption_date >= begin_date) and (end_date is null or consumption_date <= end_date) GROUP BY group_id)a
inner join sp_group_main m on a.group_id=m.id
where (m.leave_date is null or m.leave_date='') and (groupid is null or m.id = groupid)
GROUP BY a.group_id ;

end

delimiter;