Exception handling analysis in mysql stored procedure_MySQL

Define exception capture types and processing methods:

  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  

A few things to note here:

a, condition_value [,condition_value], This means that it can include multiple situations (square brackets indicate optional), that is, a handler can be defined to perform corresponding operations for multiple situations; in addition, condition_value can include There are 6 values ​​listed above:

1. mysql_error_code, this represents the error code of mysql. The error code is a number, which is defined by mysql itself. This value can refer to the mysql database error code and information.

2. SQLSTATE [VALUE] sqlstate_value, this has a one-to-one correspondence similar to the error code. It is a string of 5 characters. The key point is that it is quoted from standards such as ANSI SQL and ODBC, so More standardized, unlike the error_code above which is completely defined by mysql for its own use. This is similar to the first one and you can also refer to the mysql database error code and information.

3. condition_name, this is the condition name, which is defined using the DECLARE...CONDITION statement. We will introduce how to define your own condition_name later.

4. SQLWARNING, indicating those errors where the string in SQLTATE starts with ‘01’, such as Error: 1311 SQLSTATE: 01000 (ER_SP_UNINIT_VAR)

5. NOT FOUND, indicating those errors where the string in SQLTATE starts with ‘02’, such as Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

6. SQLEXCEPTION, which indicates errors in which the string in SQLSTATE does not start with '00', '01', or '02'. The SQLSTATE starting with '00' here actually indicates successful execution rather than an error. The other two The first is the two situations 4 and 5 above.

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

The first category is relatively clear processing, which is to handle the specified error situation, including three methods 1, 2, and 3;

The other category is the processing of corresponding types of errors, which is the processing of a certain group of errors, including three methods 4, 5, and 6. This introduces condition_value. Another thing to note is MySQL's own error handling mechanism by default (that is, we have not defined a method to handle errors - handler):

1. The way to deal with SQL WARNING and NOT FOUND is to ignore the error and continue execution. So in the cursor example, if we do not make a no_more_products=1 handler for the value judged by the repeat condition, then the loop will continue. .

2. For SQLEXCEPTION, the default processing method is to terminate where an error occurs.

b, statement, This is relatively simple, it is the statement we want to execute when a certain condition/error occurs. It can be a simple statement such as SET var = value, or it can be a complex multi-line statement. statement, if there are multiple lines, you can use BEGIN ..... END to include the statement here (this is like the situation in Delphi. Note that our stored procedure also has multiple lines, so we also need BEGIN .... END) .

c, handler_action, This indicates what action you want to perform after executing the above statement. This includes CONTINUE, EXIT, and UNDO, which means continue, exit, and cancel (not supported for the time being). Here are two actions. In fact, these two actions have been mentioned above. CONTINUE is the default processing method of SQLWARNING and NOT FOUND, and EXIT is the default processing method of SQLEXCEPTION.

Another:

condition_name: Naming condition
The readability of MySQL error code or SQLSTATE code is too poor, so naming conditions are 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(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 the entire content of this article. I hope it will be helpful to everyone’s study and I hope you will support me a lot.