最近把系统从mssql转到mysql上,原来有做统计的存储过程也要一并移过来,原来的存储过程是用行列转换,动态生成sql语句然后用exec执行的,换到mysql后改用EXECUTE执行,在mysql命令行中调用存储过程可以得到列表
+-------+--------------+-----------------+--------------+-----------+--------+
| se_no | max(se_code) | head | 娼滃湪瀹㈡埛 | 鏈€夋嫨 | 鍚
堣 |
+-------+--------------+-----------------+--------------+-----------+--------+
| 1 | NULL | 瓒呯骇绠$悊鍛? | 1 | 1 | 2
|
| NULL | NULL | 瓒呯骇绠$悊鍛? | 1 | 1 | 2
|
+-------+--------------+-----------------+--------------+-----------+--------+
(因为数据库是utf8的,所以列表是乱码),但用jdbc调用存储过程后好像没有执行execute语句,而是直接返回了动态生成的sql语句,就和在命令行中不执行最后两句
[code="sql"]PREPARE stmt2 FROM @psql;
EXECUTE stmt2;[/code]
的效果一样,哪位对mysql比较熟悉的大哥能帮个忙啊
存储过程代码
[code="sql"]CREATE PROCEDURE empCusState( arg_sqlAppend text )
BEGIN
SET @psql = 'select se_no,max(se_code),max(se_name) as head';
SELECT @psql:=CONCAT(@psql,
CASE WHEN typ_name IS NOT NULL
THEN (CONCAT(',sum(case typ_name when ''',typ_name,
''' then 1 else 0 end) as ',typ_name,' '))
ELSE (', sum(case when typ_name is null then 1 else 0 end) as 未选择')
END)
FROM
(select typ_name from cus_cor_cus
left join type_list on cor_typ_id=typ_id
where cor_isdelete='1' group by typ_name order by typ_name desc)
AS a;
SET @psql:=CONCAT(@psql,
',count(cor_code)as 合计 from cus_cor_cus
inner join sal_emp on cor_se_no=se_no
left join type_list on cor_typ_id=typ_id
where cor_isdelete=''1'' group by se_no with rollup');
PREPARE stmt2 FROM @psql;
EXECUTE stmt2;
END[/code]