在 MySQL 中，如何创建带有多个参数的存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9181612/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In MySQL how do I create a stored procedure that takes multiple parameters?
提问by stackoverflow
Example of function: call getThings(amount, place, limit, marginError)
函数示例： 调用 getThings(amount, place, limit, marginError)
SYNOPSIS: CALL getThings(4, PA, 3, 1.2);
概要： CALL getThings(4, PA, 3, 1.2);
example of goal:
目标示例：
CREATE PROCEDURE getThings(IN amount X, place VARCHAR(30), lim INT, marginError double)
SELECT place, limit, amount
FROM AREA, PRODUCT
WHERE AREA.place=PRODUCT.place
AND PRODUCT.AREA=place
ORDER BY ABS(AMOUNT-marginError)
LIMIT lim;
END
Desired goal is to retrieve the closest 3 products from a stored procedure (using MySQL) but I keep getting sytax errors in trying to create the procedure.
期望的目标是从存储过程（使用 MySQL）中检索最接近的 3 个产品，但我在尝试创建该过程时不断收到语法错误。
回答by Bernhard Kircher
since you didn't post the exact error/message,
由于您没有发布确切的错误/消息，
EDIT: I assume you are missing the IN/OUT for the 2.and 3. parameter. - Not true, see comments.
编辑：我假设您缺少 2. 和 3. 参数的输入/输出。- 不正确，请参阅评论。
e.g.
例如
DELIMITER$$
CREATE PROCEDURE getThings(IN amount X, IN place VARCHAR(30), IN lim INT)
SELECT place, `limit`, amount
FROM AREA, PRODUCT
WHERE AREA.place=PRODUCT.place
AND PRODUCT.AREA=place
ORDER BY ABS(AMOUNT-5)
LIMIT lim;
END$$
DELIMITER;
回答by Mchl
LIMITis MySQL's reserved word. If you really need to use it as column name, put in in backticks (``). Also, your paramteres have same names as columns in your table, which adds to confusion.
LIMIT是 MySQL 的保留字。如果您确实需要将其用作列名，请使用反引号 (``)。此外，您的参数与表中的列具有相同的名称，这增加了混淆。
