MySQL MySQL存储过程返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26760414/
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 return value
提问by Juanma
I have to create an SP that returns a value if it's valid or not. But it doesn't return anything and I don't know, why?
我必须创建一个 SP，如果它有效或无效则返回一个值。但它没有返回任何东西，我不知道，为什么？
CREATE DEFINER=`root`@`localhost` PROCEDURE `validar_egreso`(
IN codigo_producto VARCHAR(100),
IN cantidad INT,
OUT valido INT(11)
)
BEGIN
DECLARE resta INT(11);
SET resta = 0;
SELECT (s.stock - cantidad) INTO resta
FROM stock AS s
WHERE codigo_producto = s.codigo;
IF (resta > s.stock_minimo) THEN
SET valido = 1;
ELSE
SET valido = -1;
END IF;
SELECT valido;
END
回答by mfredy
You have done the stored procedure correctly but I think you have not referenced the validovariable properly. I was looking at some examples and they have put an @ symbol before the parameter like this @Valido
您已正确完成存储过程，但我认为您没有valido正确引用该变量。我在看一些例子，他们在这样的参数前放了一个@符号@Valido
This statement SELECT valido;should be like this SELECT @valido;
这个语句SELECT valido;应该是这样的SELECT @valido;
Look at this link mysql stored-procedure: out parameter. Notice the solution with 7 upvotes. He has reference the parameter with an @ sign, hence I suggested you add an @ sign before your parameter valido
看这个链接mysql存储过程：输出参数。请注意具有 7 个赞成票的解决方案。他用@符号引用了参数，因此我建议您在参数valido之前添加@符号
I hope that works for you. if it does vote up and mark it as the answer. If not, tell me.
我希望这对你有用。如果它确实投票并将其标记为答案。如果没有，请告诉我。
回答by biniam
Add:
添加：
DELIMITERat the beginning and end of the SP.- DROP PROCEDURE IF EXISTS
validar_egreso; at the beginning - When calling the SP, use
@variableName.
DELIMITER在 SP 的开头和结尾。- 删除程序（如果存在）
validar_egreso；一开始 - 调用 SP 时，请使用
@variableName.
This works for me. (I modified some part of your script so ANYONE can run it with out having your tables).
这对我有用。（我修改了您脚本的某些部分，因此任何人都可以在没有您的表的情况下运行它）。
DROP PROCEDURE IF EXISTS `validar_egreso`;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE `validar_egreso` (
IN codigo_producto VARCHAR(100),
IN cantidad INT,
OUT valido INT(11)
)
BEGIN
DECLARE resta INT;
SET resta = 0;
SELECT (codigo_producto - cantidad) INTO resta;
IF(resta > 1) THEN
SET valido = 1;
ELSE
SET valido = -1;
END IF;
SELECT valido;
END $$
DELIMITER ;
-- execute the stored procedure
CALL validar_egreso(4, 1, @val);
-- display the result
select @val;
回答by Rishi Vedpathak
Update your SP and handle exception in it using declare handlerwith get diagnosticsso that you will know if there is an exception. e.g.
使用带有get 诊断的声明处理程序更新您的 SP 并处理其中的异常，以便您知道是否存在异常。例如
CREATE DEFINER=`root`@`localhost` PROCEDURE `validar_egreso`(
IN codigo_producto VARCHAR(100),
IN cantidad INT,
OUT valido INT(11)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1, @p2;
END
DECLARE resta INT(11);
SET resta = 0;
SELECT (s.stock - cantidad) INTO resta
FROM stock AS s
WHERE codigo_producto = s.codigo;
IF (resta > s.stock_minimo) THEN
SET valido = 1;
ELSE
SET valido = -1;
END IF;
SELECT valido;
END
