Mysql definition Exception and exception handling detailed _mysql

In MySQL, specific exceptions require specific processing. These exceptions can be linked to errors, as well as general process control in subroutines. Defining an exception is a prior definition of the problem encountered during the execution of a program, which defines how it should be handled when a problem is encountered, and that the stored procedure or function can continue to execute when an error is encountered or a warning.

1 Exception definition

1.1 Grammar

DECLARE condition_name condition for [condition_type];

1.2 Description

The Condition_name parameter represents the name of the exception;
The Condition_type parameter represents the type of the condition, Condition_type consists of the SQLSTATE [VALUE] Sqlstate_value|mysql_error_code:

Sqlstate_value and Mysql_error_code All can represent MySQL's error;
Sqlstate_value is a string type error code of length 5;
Mysql_error_code is a numeric type error code;

1.3 Example
"Error 1148 (42000)" is defined with the name command_not_allowed. There are two ways to do this:

Method One: Use Sqlstate_value 
DECLARE command_not_allowed CONDITION for SQLSTATE ' 42000′; 
Method Two: Use Mysql_error_code 
DECLARE command_not_allowed CONDITION for 1148; 

2 Custom Exception Handling

2.1 Exception Handling Syntax

DECLARE Handler_type handler for Condition_value [,...] Sp_statement

2.2 Parameter Description

Handler_type: continue| Exit| UNDO

Handler_type is an error handling method, and the parameter is one of 3 values;
Continue indicates that an error is not handled and continues to execute;
Exit means to exit immediately upon encountering an error;
Undo indicates the operation before the recall of the error, MySQL does not support rollback operation;

Condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name| Sqlwarning| Not found| Sqlexception|mysql_error_code

Condition_value indicates the type of error;
SQLSTATE [value] Sqlstate_value is a string error value containing 5 characters;
Condition_name represents the error condition name defined by the declare condition;
SQLWarning matches all SQLState error codes that start with 01;
The not found matches all SQLState error codes that start with 02;
SqlException matches all SQLState error codes that are not captured by sqlwarning or not found;
Mysql_error_code Match numeric type error codes;

2.3 Exception Capture Method

Method One: Catch Sqlstate_value exception 
//This method is to capture the Sqlstate_value value. If you encounter a Sqlstate_value value of "42s02″, perform a CONTINUE operation and output" no_such_table "information DECLARE CONTINUE HANDLER for 
SQLSTATE ' 42s02′set @ info= ' no_such_table '; 


Method Two: Catch Mysql_error_code exception 
//This method is to capture the Mysql_error_code value. If you encounter a Mysql_error_code value of 1146, perform a CONTINUE operation and output "no_such_table" information; 
DECLARE CONTINUE HANDLER for 1146 SET @info = ' No_ Such_table '; 


Method Three: First define the condition, then catch the exception 
DECLARE no_such_table CONDITION for 1146; 
DECLARE CONTINUE HANDLER for no_such_table SET @info = ' no_such_table ';  


Method Four: Use SQLWarning to catch exception 
DECLARE EXIT HANDLER for sqlwarning SET @info = ' ERROR ';  


Method Five: Use not FOUND to catch exceptions 
DECLARE EXIT HANDLER for not FOUND SET @info = ' no_such_table ';


Method VI: Use SQLEXCEPTION to catch exceptions 
DECLARE EXIT HANDLER for SQLEXCEPTION SET @info = ' ERROR ';

3 Integrated Examples

Create a table, set the primary key for the table, and see where the execution is if you do not define exception handling and define exception handling.

show databases;
Use WMS;
CREATE TABLE Location
(
location_id int primary KEY,
location_name varchar)
; 

Example 1: Do not define exceptions

 DELIMITER//
CREATE PROCEDURE handlerinsertnoexception ()
BEGIN
 /*declare CONTINUE HANDLER for SQLSTATE ' 23000 ' Set @x2 =1;*/
 set @x=1;
 INSERT into Location VALUES (1, ' Beijing ');
 SET @x=2;
 INSERT into Location VALUES (1, ' Wuxi ');
 SET @x=3;
End;
DELIMITER; 

Calling stored procedures and results:

 Mysql> call Handlerinsertnoexception ();
ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' PRIMARY ' mysql>
select @x;
+------+
| @x  |
+------+
|  2 |
+------+
1 row in Set (0.00 sec)

mysql> select * from location;
+-------------+---------------+
| location_id | location_name
| +-------------+---------------+
|      1 | Beijing    |
+-------------+---------------+
1 row in Set (0.00 sec) 

Note: To clear the data in the table before operation Example 2, and exit the login again to avoid the @x effect of the client variable, see the 1th of the conclusion in detail.

 mysql> truncate TABLE location;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from location;
Empty Set (0.00 sec)
mysql> exit;
Bye

david@louis:~$ mysql-u root-p
Enter Password: 
Welcome to the MySQL monitor. Commands End With; or \g.
Your MySQL Connection ID is the
Server version:5.5.38-0ubuntu0.14.04.1 (Ubuntu)

mysql> use WMS;
Reading table information for completion of table and column names you can turn out this feature to get
a quicker sta Rtup with-a

Database changed
mysql> select * from location;
Empty Set (0.00 sec)

mysql> select @x;
+------+
| @x  |
+------+
| NULL |
+------+
1 row in Set (0.00 sec)

Example 2: Define Exception Handling:

 DELIMITER//
CREATE PROCEDURE handlerinsertwithexception ()
BEGIN
 DECLARE CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @x2 =1;
 SET @x=1;
 INSERT into Location VALUES (1, ' Beijing ');
 SET @x=2;
 INSERT into Location VALUES (1, ' Wuxi ');
 SET @x=3;
End;
DELIMITER; 

Calling stored procedures and results:

 Mysql> call Handlerinsertwithexception ();
Query OK, 0 rows affected (0.09 sec)

mysql> select @x;
+------+
| @x  |
+------+
|  3 |
+------+
1 row in Set (0.00 sec) 

description and conclusion:

In MySQL, @var_name represents a user variable that is assigned a value using the SET statement, a user variable connected to a connection, and a client-defined variable that cannot be seen or used by another client. When the client exits, all the variables for that client connection are automatically freed.

Second, in Example 1, because the declaration of an exception is annotated "", the same primary key is inserted into the table, the exception is triggered, and the default (exit) path is taken, and the view at this time @x returns 2, indicating that the following insert statement exits without execution.

Third, the exception handling is defined, at which time the error will continue to follow the exception definition, but only the first data is inserted into the table, at which time the user variable @x=3 description has been executed to the 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.