mysql 存储过程

 

1、要想查看存储过程，用如下命令：

 show create procedure  proc_dealwith_cs_incomingrecords;

refurl:http://www.jb51.net/article/27067.htm

 

2、执行存储过程:

call proc_dealwith_cs_incomingrecords('2015-01-27');

利用call命令，然后加上参数即可。这个在调试时非常有用。

refurl:http://blog.csdn.net/xiaohang20103/article/details/8924520

 

3、删除存储过程:

drop PROCEDURE proc_dealwith_ec_incomingrecords

 

 

4、创建存储过程:

CREATE  PROCEDURE `proc_dealwith_cs_incomingrecords`(IN `p_in` DATE)
BEGIN
/*1.FIRST DELETE temp*/
DELETE from temp;
/*2.1 sum by province*/
INSERT into temp(province,businessName,count,incomingTime)
SELECT province,businessname,count(*) as 'cnt',DATE_FORMAT(incomingtime,'%Y/%m/%d') as incomingTime2
from t_incoming_records
where DATE_FORMAT(incomingtime,'%Y/%m/%d')=p_in and type=0
group by province,businessname,incomingTime2;
/*2.2 sum by city*/
INSERT into temp(province,businessName,count,incomingTime)
SELECT city,businessname,count(*) as 'cnt',DATE_FORMAT(incomingtime,'%Y/%m/%d') as incomingTime2
from t_incoming_records
where DATE_FORMAT(incomingtime,'%Y/%m/%d')=p_in and type=0 and city in (convert('青岛市' using utf8),convert('深圳市' using utf8),convert('苏州市' using utf8),convert('宁波市' using utf8))
group by city,businessname,incomingTime2;
/*3.row to COLUMN*/
INSERT into t_cs_incoming_report(institution,report,compensation,complaint,inquiry,other,yijianxian,incomingTime)
SELECT province as institution,
sum(CASE WHEN businessName=convert('1' using utf8) THEN count END) as 'report',
sum(CASE WHEN businessName=convert('3' using utf8) THEN count END) as 'compensation',
sum(CASE WHEN businessName=convert('4' using utf8) THEN count END) as   'complaint',
sum(CASE WHEN businessName=convert('2' using utf8) THEN count END) as  'inquiry',
sum(CASE WHEN businessName=convert('6' using utf8) THEN count END) as 'other',
sum(CASE WHEN businessName=convert('5' using utf8) THEN count END) as 'yijianxian',
incomingTime
from temp
GROUP BY institution;
end

 

i)一开始要加上关键字，create procedure procudureName( 入参名字 in 入参类型)

2)在首尾加上begin和end,中间就是普通的SQL代码块。

refurl:http://www.2cto.com/database/201407/321989.html

refurl:http://2301703.blog.51cto.com/2291703/1160297