Mysql - 如何退出/退出存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6260157/
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 - How to quit/exit from stored procedure
提问by Joe Ijam
I have very simple question but i did't get any simple code to exit from SP using Mysql. Can anyone share with me how to do that?
我有一个非常简单的问题，但我没有得到任何使用 Mysql 退出 SP 的简单代码。谁能与我分享如何做到这一点？
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
BEGIN
IF tablename IS NULL THEN
#Exit this stored procedure here
END IF;
#proceed the code
END;
回答by piotrm
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
proc_label:BEGIN
IF tablename IS NULL THEN
LEAVE proc_label;
END IF;
#proceed the code
END;
回答by Bohemian
To handle this situation in a portable way (ie will work on all databases because it doesn't use MySQL label Kung fu), break the procedure up into logic parts, like this:
要以可移植的方式处理这种情况（即适用于所有数据库，因为它不使用 MySQL 标签 Kung fu），将过程分解为逻辑部分，如下所示：
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
BEGIN
IF tablename IS NOT NULL THEN
CALL SP_Reporting_2(tablename);
END IF;
END;
CREATE PROCEDURE SP_Reporting_2(IN tablename VARCHAR(20))
BEGIN
#proceed with code
END;
回答by BuvinJ
If you want an "early exit" for a situation in which there was no error, then use the accepted answer posted by @piotrm. Most typically, however, you will be bailing due to an error condition (especially in a SQL procedure).
如果您希望在没有错误的情况下“提前退出”，请使用@piotrm 发布的已接受答案。然而，最典型的情况是，您将因错误情况（尤其是在 SQL 过程中）而放弃。
As of MySQL v5.5 you can throw an exception. Negating exception handlers, etc. that will achieve the same result, but in a cleaner, more poignant manner.
从 MySQL v5.5 开始，您可以抛出异常。否定异常处理程序等将实现相同的结果，但以更清晰、更尖锐的方式。
Here's how:
就是这样：
DECLARE CUSTOM_EXCEPTION CONDITION FOR SQLSTATE '45000';
IF <Some Error Condition> THEN
SIGNAL CUSTOM_EXCEPTION
SET MESSAGE_TEXT = 'Your Custom Error Message';
END IF;
Note SQLSTATE '45000'equates to "Unhandled user-defined exception condition". By default, this will produce an error code of 1644(which has that same meaning). Note that you can throw other condition codes or error codes if you want (plus additional details for exception handling).
注意SQLSTATE '45000'等同于“未处理的用户定义异常条件”。默认情况下，这将产生一个错误代码1644（具有相同的含义）。请注意，您可以根据需要抛出其他条件代码或错误代码（以及有关异常处理的其他详细信息）。
For more on this subject, check out:
有关此主题的更多信息，请查看：
https://dev.mysql.com/doc/refman/5.5/en/signal.html
https://dev.mysql.com/doc/refman/5.5/en/signal.html
How to raise an error within a MySQL function
Addendum
附录
As I'm re-reading this post of mine, I realized I had something additional to add. Prior to MySQL v5.5, there was a way to emulate throwing an exception. It's not the same thing exactly, but this was the analogue: Create an error via calling a procedure which does not exist. Call the procedure by a name which is meaningful in order to get a useful means by which to determine what the problem was. When the error occurs, you'll get to see the line of failure (depending on your execution context).
当我重新阅读我的这篇文章时，我意识到我有一些额外的东西要添加。在 MySQL v5.5 之前，有一种模拟抛出异常的方法。这完全不是一回事，但这是类比：通过调用不存在的过程来创建错误。通过一个有意义的名称来调用该过程，以便获得一种确定问题所在的有用方法。发生错误时，您将看到失败的行（取决于您的执行上下文）。
For example:
例如：
CALL AttemptedToInsertSomethingInvalid;
CALL AttemptedToInsertSomethingInvalid;
Note that when you create a procedure, there is no validation performed on such things. So while in something like a compiled language, you could never call a function that wasn't there, in a script like this it will simply fail at runtime, which is exactly what is desired in this case!
请注意，当您创建过程时，不会对此类内容执行验证。因此，虽然在诸如编译语言之类的东西中，您永远无法调用不存在的函数，但在这样的脚本中，它只会在运行时失败，这正是这种情况下所需要的！
回答by Devendra Singraul
This works for me :
这对我有用：
CREATE DEFINER=`root`@`%` PROCEDURE `save_package_as_template`( IN package_id int ,
IN bus_fun_temp_id int , OUT o_message VARCHAR (50) ,
OUT o_number INT )
BEGIN
DECLARE v_pkg_name varchar(50) ;
DECLARE v_pkg_temp_id int(10) ;
DECLARE v_workflow_count INT(10);
-- checking if workflow created for package
select count(*) INTO v_workflow_count from workflow w where w.package_id =
package_id ;
this_proc:BEGIN -- this_proc block start here
IF v_workflow_count = 0 THEN
select 'no work flow ' as 'workflow_status' ;
SET o_message ='Work flow is not created for this package.';
SET o_number = -2 ;
LEAVE this_proc;
END IF;
select 'work flow created ' as 'workflow_status' ;
-- To send some message
SET o_message ='SUCCESSFUL';
SET o_number = 1 ;
END ;-- this_proc block end here
END
回答by Stephen
Why not this:
为什么不是这个：
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
BEGIN
IF tablename IS NOT NULL THEN
#proceed the code
END IF;
# Do nothing otherwise
END;
回答by sdfor
MainLabel:BEGIN
IF (<condition>) IS NOT NULL THEN
LEAVE MainLabel;
END IF;
....code
i.e.
IF (@skipMe) IS NOT NULL THEN /* @skipMe returns Null if never set or set to NULL */
LEAVE MainLabel;
END IF;
