Mysql -如何退出/退出存储过程。

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;

5 个解决方案

#1

146

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;

#2

There isn't a way: The geniuses at MySQL didn't think it was required - they prefer massively long if blocks to the elegant "exit early" coding pattern.

没有办法:MySQL的天才们不认为这是必需的——他们更喜欢长时间的块，而不是优雅的“提前退出”编码模式。

However, to handle this situation (which I have used to good effect in the past) is to break the procedure up into logic parts, like this:

但是，要处理这种情况(我过去曾对这种情况有过良好的效果)，就需要将过程分解成如下逻辑部分:

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;

#3

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:

How to raise an error within a MySQL function

如何在MySQL函数中引发错误

http://www.databasejournal.com/features/mysql/mysql-error-handling-using-the-signal-and-resignal-statements.html

#4

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;

#5

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;

#1

146

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;

#2