MySQL Error Handling in Stored Procedures, handlingstored

MySQL Error Handling in Stored Procedures, handlingstored

Http://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/

Exception Handling During mysql Storage

Define the exception capture type and handling method:

    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  

Pay attention to the following points:

A. condition_value [, condition_value], which indicates that there are multiple situations (square arc indicates optional), that is, a handler can be defined as performing corresponding operations for multiple situations; in addition, condition_value can include six types of values listed above:

1. mysql_error_code, which indicates the mysql error code. The error code is a number defined by mysql. For details about this value, refer to the mysql database error code and information.

2. SQLSTATE [VALUE] sqlstate_value, which is similar to the error code and forms a one-to-one correspondence relationship. It is a five-character string, the key point is that it is referenced from ansi SQL and ODBC standards, so it is more standardized, unlike the above error_code, which is completely defined by mysql for your own use, this is similar to the first mysql database error code and information.

3. condtion_name. This is the CONDITION name, which is defined using the DECLARE... CONDITION statement. This section describes how to define your own condition_name.

4. SQLWARNING indicates the errors starting with '01' of the string in SQLTATE, such as Error:1311SQLSTATE:01000(ER_SP_UNINIT_VAR)

5. not found indicates the errors starting with '02' for the string in SQLTATE, such as Error:1329SQLSTATE:02000(ER_SP_FETCH_NO_DATA)

6. SQLEXCEPTION, indicating that the strings in SQLSTATE are not errors starting with '00', '01', or '02, here, the SQLSTATE starting from '00' actually indicates successful execution rather than an error. The other two are the above two situations: 4 and 5.

The above six situations can be divided into two categories:

The first type is clear processing, that is, processing the specified error, including the methods 1, 2, and 3;

The other is to handle corresponding types of errors, that is, to handle a group of errors, including 4, 5, and 6. This section introduces condition_value. In addition, it should be noted that MySQL uses its own error handling mechanism by default (that is, we have not defined the method for handling errors-handler: 1. The SQLWARNING and not found methods are to ignore errors and continue execution, so in the cursor example, if we do not make a handler of no_more_products = 1 for the value judged by the repeat condition, the loop will continue. 2. For SQLEXCEPTION, the default solution is to terminate it when an error occurs.

B. statement. This is a simple statement to be executed when a certain condition or error occurs, such as SET var = value, it can also be a complex multi-row statement. If there are multiple rows, you can use BEGIN ..... the END statement is included here (this is like the situation in delphi. It is noted that our stored procedure is also multi-row, so we need to BEGIN in .... END ).

C. handler_action, which indicates the actions you want to perform after the preceding statement is executed. The actions include "CONTINUE", "EXIT", and "UNDO", which indicate "CONTINUE", "EXIT", and "UNDO" (not supported currently ). Here there are two actions. In fact, these two actions are also mentioned above. CONTINUE is the default Processing Method of SQLWARNING and not found, while EXIT is the default Processing Method of SQLEXCEPTION.

 

In addition:

Condition_name: naming Condition

MySQL error code or SQLSTATE code is too readable, so the naming conditions are introduced:

Syntax:

    DECLARE condition_name CONDITION FOR condition_value            condition_value:          SQLSTATE [VALUE] sqlstate_value        | mysql_error_code  

Usage:

    # 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(30),           in_address1    VARCHAR(30),           in_address2    VARCHAR(30),           zipcode        VARCHAR(10),           OUT out_status VARCHAR(30))      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;