MySQL 存储过程的问题
2022-11-12 09:16:51
内容摘要
这篇文章主要为大家详细介绍了MySQL 存储过程的问题，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！一开始用phpMyAdmin来执行，后来出现一
文章正文
这篇文章主要为大家详细介绍了MySQL 存储过程的问题，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
一开始用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)BEGINPREPARE 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.thumbnailFROM product p INNER JOIN product_category pc ON p.product_id = pc.product_idWHERE pc.category_id = ?ORDER BY p.display DESCLIMIT ?, ?";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.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$$ERROR 1314 (0A000): PREPARE is not allowed in stored procedures上面有两个存储过程，一个不用预处理语句，一个用了预处理语句，之后，向作者发过邮件，没有答复，又给mysql官方发过邮件，同样没答复。现今只能求助诸位高人。
注：关于MySQL 存储过程的问题的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
