一开始用phpmyadmin来执行，后来出现一堆错误，后来去掉了begin,end之后可以正常执行，但要执行存储过程，在phpmyadmn中不行，而在mysql命令行文本框中就可以。
接下来又遇到更难的问题，在存储过程中加入预处理语句，更不行了，在mysql命令行文本框下执行同样，下面的运行记录，给大家参考，能否有高手来帮助。
mysql> create procedure catalog_get_products_in_category(
-> in incategoryid int, in inshortproductdescriptionlength int,
-> in inproductsperpage int, in instartitem int)
-> begin
-> select p.product_id, p.name,if(length(p.description) <= inshortproductd
escriptionlength, p.description,
-> concat(left(p.description, inshortproductdescriptionlength),'...')) as
description, p.price, p.discounted_price, p.thumbnail
-> from product p inner join product_category pc on p.product_id = pc.pro
duct_id
-> where pc.category_id = incategoryid
-> order by p.display desc
-> limit instartitem;inproductsperpage;
-> end$$
error 1064 (42000): you have an error in your sql syntax; check the manual that
corresponds to your mysql server version for the right syntax to use near 'insta
rtitem;inproductsperpage;
end' at line 10
//原本的存储过程语句
eate procedure catalog_get_products_in_category(
in incategoryid int, in inshortproductdescriptionlength int,
in inproductsperpage int, in instartitem int)
begin
prepare statement from
"select p.product_id, p.name,if(length(p.description) <= ?, p.description,
concat(left(p.description, ?),'...')) as description, p.price, p.discounted_price, p.thumbnail
from product p inner join product_category pc on p.product_id = pc.product_id
where pc.category_id = ?
order by p.display desc
limit ?, ?";
set @p1 = inshortproductdescriptionlength;
set @p2 = inshortproductdescriptionlength;
set @p3 = incategoryid;
set @p4 = instartitem;
set @p5 = inproductsperpage;
execute statement using @p1, @p2, @p3, @p4, @p5;
end$$
mysql> delimiter $$
mysql> create procedure catalog_get_products_in_category(
-> in incategoryid int, in inshortproductdescriptionlength int,
-> in inproductsperpage int, in instartitem int)
-> begin
-> prepare statement from
-> "select p.product_id, p.name,if(length(p.description) <= ?, p.descript
ion,
"> concat(left(p.description, ?),'...')) as description, p.price, p.disco
unted_price, p.thumbnail
"> from product p inner join product_category pc on p.product_id = pc.pro
duct_id
"> where pc.category_id = ?
"> order by p.display desc
"> limit ?, ?";
-> set @p1 = inshortproductdescriptionlength;
-> set @p2 = inshortproductdescriptionlength;
-> set @p3 = incategoryid;
-> set @p4 = instartitem;
-> set @p5 = inproductsperpage;
-> execute statement using @p1, @p2, @p3, @p4, @p5;
-> end$$
error 1314 (0a000): prepare is not allowed in stored procedures
上面有两个存储过程，一个不用预处理语句，一个用了预处理语句，
之后，向作者发过邮件，没有答复，又给mysql官方发过邮件，同样没答复。现今只能求助诸位高人。
如对本文有疑问，
点击进行留言回复！！
我要评论
网友评论