mysql 存储过程：输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1113579/
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
mysql stored-procedure: out parameter
提问by domagojk
I have a mysql stored procedure from this (google book), and one example is this:
我有一个来自这个（谷歌书）的 mysql 存储过程，一个例子是这样的：
DELIMITER $$
DROP PROCEDURE IF EXISTS my_sqrt$$
CREATE PROCEDURE my_sqrt(input_number INT, OUT out_number FLOAT)
BEGIN
SET out_number=SQRT(input_number);
END$$
DELIMITER ;
The procedure compiles fine. (I am using MySQL Query Browser in ubuntu).
该程序编译得很好。（我在 ubuntu 中使用 MySQL 查询浏览器）。
However when I call the procedure:
但是，当我调用该程序时：
CALL my_sqrt(4,@out_value);
(also in query browser)
（也在查询浏览器中）
It returns an error:
它返回一个错误：
(1064) check the manual that correspond to the...
Why isn't this example working?
为什么这个例子不起作用？
回答by chaos
Unable to replicate. It worked fine for me:
无法复制。它对我来说很好：
mysql> CALL my_sqrt(4, @out_value);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @out_value;
+------------+
| @out_value |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
Perhaps you should paste the entire error message instead of summarizing it.
也许您应该粘贴整个错误消息而不是总结它。
回答by domagojk
You must have use correct signature for input parameter *IN is missing in the below code.
您必须为输入参数使用正确的签名 *IN 在下面的代码中丢失。
CREATE PROCEDURE my_sqrt(IN input_number INT, OUT out_number FLOAT)
回答by domagojk
I just tried to call a function in terminal rather then MySQL Query Browser and it works. So, it looks like I'm doing something wrong in that program...
我只是尝试在终端而不是 MySQL 查询浏览器中调用一个函数，它可以工作。所以，看起来我在那个程序中做错了什么......
I don't know what since I called some procedures before successfully (but there where no out parameters)...
我不知道是什么，因为我之前成功调用了一些程序（但是那里没有输出参数）...
For this one I had entered
对于这个我已经进入
CALL my_sqrt(4,@out_value);
SELECT @out_value;
And it results with an error:
它导致一个错误：
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 'SELECT @out_value' at line 2
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 2 行的“SELECT @out_value”附近使用的正确语法
Strangely, if I write just:
奇怪的是，如果我只写：
CALL my_sqrt(4,@out_value);
The result message is: "Query canceled"
结果消息是：“查询已取消”
I guess, for now I will use only terminal...
我想，现在我只会使用终端......
回答by SomeOne_1
I know this is an old thread, but if anyone is looking for an answer of why their procedures doesn't work in the workbench and think the only result is "Query canceled" or anything like that without clues:
我知道这是一个旧线程，但是如果有人正在寻找为什么他们的程序在工作台中不起作用的答案，并认为唯一的结果是“查询已取消”或类似的任何没有线索的东西：
the output with errors or problems is hiddenl. I do not know why, I do understand it's annoying, but it is there. just move your cursor above the line above the message, it will turn in an double arrow (up and down) you can then click and drag that line up, then you will see a console with the message you missed!
有错误或问题的输出是隐藏的。我不知道为什么，我明白这很烦人，但它就在那里。只需将光标移到消息上方的行上方，它就会变成一个双箭头（向上和向下），然后您可以单击并向上拖动该行，然后您将看到一个控制台，其中包含您错过的消息！
回答by Noby Nirmal
SET out_number=SQRT(input_number);
SET out_number=SQRT(input_number);
Instead of this write:
而不是这样写：
select SQRT(input_number);
Please don't write SET out_numberand your input parameter should be:
请不要写SET out_number，你的输入参数应该是：
PROCEDURE `test`.`my_sqrt`(IN input_number INT, OUT out_number FLOAT)
回答by ship shuk - www.shipshuk.com
If you are calling from within Stored Procedure don't use @. In my case it returns 0
如果您从存储过程中调用，请不要使用 @。在我的情况下它返回 0
CALL SP_NAME(L_OUTPUT_PARAM)
CALL SP_NAME(L_OUTPUT_PARAM)
回答by Maurice
try changing OUTto INOUTfor your out_numberparameter definition.
请尝试更改OUT到INOUT您的out_number参数定义。
CREATE PROCEDURE my_sqrt(input_number INT, INOUT out_number FLOAT)
CREATE PROCEDURE my_sqrt(input_number INT, INOUT out_number FLOAT)
INOUTmeans that the input variable for out_number(@out_valuein your case.) will also serve as the output variable from which you can select the value from.
INOUT意味着out_number（@out_value在您的情况下。）的输入变量也将作为您可以从中选择值的输出变量。
