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' 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 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;

MySQL stored procedure common syntax

The stored procedure P_GET_CLASS_NAME is used to determine the class name based on the input class number.

The stored procedure P_INSERT_STUDENT is used to receive input student information and insert the information into the student table.

Drop procedure if exists 'p _ GET_CLASS_NAME ';
Create procedure P_GET_CLASS_NAME (in id int, out name varchar (50 ))
BEGIN
IF (ID = 1) THEN
Set name = 'class1 ';
End if;
IF (ID = 2) THEN
Set name = 'second class ';
End if;
END;
      
      
Drop procedure if exists 'p _ INSERT_STUDENT ';
Create procedure P_INSERT_STUDENT (in id int, in name varchar (10), in classno int, in birth datetime)
BEGIN
SET @ ID = ID;
SET @ NAME = NAME;
SET @ CLASSNO = CLASSNO;
SET @ BIRTH = BIRTH;
SET @ CLASSNAME = NULL;
CALL P_GET_CLASS_NAME (@ CLASSNO, @ CLASSNAME );
           
SET @ insertSql = CONCAT ('Insert INTO TBL_STUDENT VALUES (?,?,?,?) ');
PREPARE stmtinsert FROM @ insertSql;
EXECUTE stmtinsert USING @ ID, @ NAME, @ CLASSNAME, @ BIRTH;
Deallocate prepare stmtinsert;
END;
      
CALL P_INSERT_STUDENT (1, 'XY', 1, '2017-10-01 10:20:01 ');

 

In the second stored procedure

① Declared parameters using SET and called the first stored procedure

② The NAME parameter in the first stored procedure is the output parameter, so @ CLASSNAME is assigned a value after the first procedure is called.

③ Use CONCAT to splice SQL statements and input parameters to execute SQL statements.


CALL P_INSERT_STUDENT (1, 'XY', 1, '2017-10-01 10:20:01 '); CALL the stored procedure