环境:MYSQL数据库本身是UTF8+客户端是UTF8连接的
如果在这种情况下,存储过程依然是乱码的话,那么原因如下:
1、定义了变量。变量必须也要以UTF8定义。
2、用了中文字符串作比较,此时要将中文转化成UTF8编码,否则乱码。
具体方法:
CREATE PROCEDURE proc_dealwith_incomingrecords(IN p_in date)
BEGIN
/*1.FIRST DELETE temp*/
DELETE from temp;
/*2.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
group by province,businessName,incomingTime2;
/*3.row to COLUMN*/
INSERT into t_cs_incoming_report(institution,report,compensation,challenge,inquiry,other,incomingTime)
SELECT province as 'institution',
sum(CASE WHEN businessName=convert(_utf8'报案' using utf8) THEN count END) as 'report',
sum(CASE WHEN businessName=convert(_utf8'承保理赔' using utf8) THEN count END) as 'compensation',
sum(CASE WHEN businessName=convert(_utf8'投诉' using utf8) THEN count END) as 'complaint',
sum(CASE WHEN businessName=convert(_utf8'咨询' using utf8) THEN count END) as 'inquiry',
sum(CASE WHEN businessName=convert(_utf8'其他服务' using utf8) THEN count END) as 'other',
incomingTime
from temp
GROUP BY 'institution';
end;
refurl:http://magicma-007.iteye.com/blog/1687598
http://blog.csdn.net/dy357978912/article/details/8906963
-------------------------------------------
ps:在Navicat for mysql的软件版本其实蛮好的,调用存储过程如下:
1、随便打开一个查询窗口
2、输入如下语句:
set @p_out=1;
call proc2(@p_out);
然后会在下文出现结果1,结果2等。