Troubleshooting of exceptions in mysql stored procedures, mysql stored procedures

Troubleshooting of exceptions in mysql stored procedures, mysql stored procedures

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],This description can include multiple cases (square brackets indicate optional), that is, a handler can be defined as performing corresponding operations for multiple cases; 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' for the string in SQLTATE, such as Error: 1311 SQLSTATE: 01000 (ER_SP_UNINIT_VAR)

5. not found indicates the errors starting with '02' for the string in SQLTATE, such as Error: 1329 SQLSTATE: 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 condition or error occurs. It can be a simple statement 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,This indicates the action you want to perform after the above statement is executed. The actions include CONTINUE, EXIT, and UNDO, indicating to 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; 

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.