MySQL Error Handling in Stored procedures

Define the type of exception capture and how to handle it:

    DECLARE handler_action Handler          for Condition_value [, Condition_value] ...          Statement            handler_action:          CONTINUE        | EXIT        | UNDO            condition_value:          mysql_error_code        | SQLSTATE [VALUE] sqlstate_value        | condition_name        | SQLWarning        | Not FOUND        | SQLEXCEPTION  

Here are a few things to note:

A, Condition_value [, Condition_value], this statement can include a variety of situations (square brackets to indicate optional), that is, a handler can be defined as the corresponding operation for a variety of situations; Condition_ Value can include 6 kinds of values listed above:

1, Mysql_error_code, this represents the MySQL error code, the error code is a number, the completion is defined by MySQL itself, this value can refer to the MySQL Database error code and information.

2, SQLSTATE [VALUE] Sqlstate_value, this is similar to the error code to form a one by one corresponding relationship, it is a 5-character string, the key point is that it is referenced from the ANSI SQL and ODBC standards, and therefore more standardized, Unlike the above error_code is completely mysql itself defined for their own use, this and the first similar can also refer to the MySQL Database error code and information.

3, Condtion_name, this is the condition name, it uses declare ... Condition statement, which we'll show you how to define your own condition_name.

4. SQLWarning, which indicates that the string in sqltate starts with ' 01 ' errors, such as error: 1311 SQLSTATE: 01000 ( ER_SP_UNINIT_VAR )

5, not FOUND, which indicates that the strings in Sqltate start with ' 02 ' errors, such as error: 1329 SQLSTATE: 02000 ( ER_SP_FETCH_NO_DATA )

6, SQLEXCEPTION, indicates that sqlstate in the string is not the ' 00 ', ' 01 ', ' 02 ' The beginning of those errors, where ' 00 ' beginning of the SQLSTATE actually represents a successful execution rather than error, the other two is the above 4 and 5 of the two cases.

The above 6 cases can actually be divided into two categories:

One is a more explicit treatment, which is to deal with the specified error conditions, including 1, 2, 3 of these three ways;

The other is the processing of the corresponding type of error, that is, the processing of a group of errors, including 4, 5, 6 of these three ways. This is the introduction of Condition_value. Another thing to note is that MySQL has its own error handling mechanism by default (that is, we do not define a method for handling errors-handler): 1, for sqlwarning and not found, the process is to ignore the error continue to execute, So in the case of the cursor, if we don't do a no_more_products=1 handler to the value of repeat's condition, then the loop will go on. 2, for SqlException, its default treatment method is in the wrong place on the termination.

b, statement, this is relatively simple is when a certain condition/error, we have to execute the statement, can be simple as SET var = value such as simple statement, can also be a complex multiline statement, multiple lines can use the begin .... End here to include the statement inside (this is like the case in Delphi, notice that our stored procedures are also many lines, so also begin ...) END).

C, handler_action, this means that when the above statement is executed, want to perform what kind of action, here include continue, exit, Undo, indicating continuation, exit, revocation (temporarily not supported). This is two kinds of action, in fact, these two actions in the above also said, continue is a sqlwarning and not found default processing method, and exit is the default processing method of SqlException.

Other:

Condition_name: Naming criteria

MySQL error code or SQLSTATE code is poorly readable, so a naming condition is introduced:

Grammar:

    DECLARE condition_name condition for condition_value            condition_value:          SQLSTATE [value] Sqlstate_value        | Mysql_error_code  

Use:

    # original      DECLARE CONTINUE HANDLER for 1216 mysql_statements;            # changed      DECLARE foreign_key_error CONDITION for 1216;      DECLARE CONTINUE HANDLER for Foreign_key_error mysql_statements;  

Example:

    CREATE PROCEDURE sp_add_location          (in_location    varchar),           in_address1    varchar (+),           In_ Address2    varchar (+),           zipcode        varchar (ten), out           out_status varchar ())      BEGIN          DECLARE CONTINUE HANDLER for              1062              SET out_status= ' Duplicate Entry ';                SET out_status= ' OK ';          INSERT into Locations              (location,address1,address2,zipcode)          VALUES              (in_location,in_address1,in_ Address2,zipcode);      END;  

MySQL Error Handling in Stored procedures