Analysis of exception handling in Mysql stored procedure _mysql

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  

There are a few things to note here:

A, Condition_value [, Condition_value], This statement can include a variety of situations (square brackets indicate optional), that is, a handler can be defined for a variety of situations to do the appropriate action In addition, Condition_value can include the following 6 kinds of values listed above:

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

2, SQLSTATE [VALUE] Sqlstate_value, which is similar to the error code to form a one by one correspondence, it is a 5-character string, the key point is that it from the ANSI SQL and ODBC standards referenced, so more standardized, Unlike the error_code above, which is completely mysql-defined for itself, this and the first analogy can also refer to MySQL database error codes and information.

3, Condtion_name, this is the condition name, it uses declare ... Condition statement, we'll explain how to define our own condition_name later in this article.

4, sqlwarning, which means that the string in the sqltate starts with ' 01 ', such as error:1311 sqlstate:01000 (Er_sp_uninit_var)

5, not FOUND, which means that the string in the sqltate that starts with ' 02 ', such as error:1329 sqlstate:02000 (er_sp_fetch_no_data)

6, SQLEXCEPTION, which means that the strings in SQLState are not those that start with ' 00 ', ' 01 ', ' 02 ', where the ' 00 ' starting SQLState actually represents a successful execution rather than an error, and two of the above 4 and 5.

The above 6 kinds of cases can be divided into two categories:

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

The other is the treatment of the corresponding type of error, that is, the treatment of a group of errors, including the 4, 5, 6 of the 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 have no way to define how to handle the error-handler):

1. The processing of sqlwarning and not found is to ignore the error, so in the case of the cursor if we do not do a no_more_products=1 handler to the value of the repeat condition, Then the cycle will go on.

2, for SqlException, its default treatment is in the wrong place on the end of the drop.

B, statement, this relatively simple is when some kind of condition/error, we want to execute the statement, can be simple such as SET var = value Simple statement, can also be complex multi-line statements, many lines can use the BEGIN ... End here to include the statement in the inside (this is like the situation in Delphi, notice that our stored procedures are also many lines, so also want to begin ....) End).

C, handler_action, This means that when the above statement, want to perform what action, this includes continue, exit, Undo, said Continue, exit, undo (temporarily not supported). Here 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 SqlException default processing method.

Other:

Condition_name: Naming criteria
the readability of MySQL error code or SQLSTATE code is too poor, 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  (a), 
     In_ Address2  VARCHAR, 
     zipcode    VARCHAR (a), out 
     out_status VARCHAR (a)) 
  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 the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.