MySQL definition exceptions and exception handling methods

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 the program, and exception handling defines how it should be handled when a problem is encountered, and ensures that the stored procedure or function can continue to execute when it encounters an error or a warning.

1 Exception Definition 1.1 syntax

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 condition, Condition_type consists of SQLState [VALUE] Sqlstate_value|mysql_error_code:

Both Sqlstate_value and Mysql_error_code can represent MySQL errors; Sqlstate_value is the error code for the string type 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 the error handling method, the parameter is one of 3 values; Continue indicates that an error is not handled and continues execution; exit indicates that an error is encountered and quits immediately after an error is encountered, and MySQL does not support rollback operation;

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

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

2.3 Exception Capture Method

Method One: Catch Sqlstate_value exceptions

This method captures 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: Capturing Mysql_error_code anomalies

This method captures the Mysql_error_code value. If you encounter a Mysql_error_code value of 1146, perform a continue operation and output the "no_such_table" message;

DECLARE CONTINUE HANDLER for 1146 set@info= ' no_such_table ';

Method Three: Define the condition first and 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 exceptions

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 Six: Use SqlException to catch exceptions

DECLARE EXIT HANDLER for SQLEXCEPTION SET @info = ' ERROR ';

3 Synthesis Examples

Create a table, set the primary key for the table, and see which step to take if you do not define exception handling and define exception handling.

1 2 3 4 5 6 7

show databases; use wms; createtablelocation ( location_id intprimarykey, location_name varchar(50) );

Example 1: Do not define exception cases

1 2 3 4 5 6 /p> 7 8 9 One

DELIMITER// create< /code> PROCEDURE handlerinsertnoexception () BEGIN     /*declare CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @x2 =1;*/   & Nbsp; set @x=1;     insert to location VALUES (1, ' Beijing ');     set @x=2;     insert to location VALUES (1, ' Wuxi ');     set @x=3; END; // DELIMITER;

Call the stored procedure with the result:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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 the operation Example 2, and exit the login again, in order to avoid the client variable @x effect, see the 1th in the conclusion in detail.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28

mysql> truncatetablelocation; Query OK, 0 rowsaffected (0.04 sec) mysql> select* fromlocation; Empty set(0.00 sec) mysql> exit; Bye [email protected]:~$ mysql -u root -p Enter password: Welcome tothe MySQL monitor.  Commands end with ; or\g. Your MySQL connectionid is53 Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu) mysql> use wms; Reading tableinformation for completion of table and columnnames You can turn offthis feature to get a quicker startup with-A Databasechanged mysql> select* fromlocation; Empty set(0.00 sec) mysql> select@x; +------+ | @x   | +------+ | NULL| +------+ 1 row inset(0.00 sec)

Example 2: Define Exception Handling cases:

1 2 3 4 5 6 7 8 9 12

DELIMITER// CREATE PROCEDURE Handlerinsertwithexception () BEGIN     declare CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @x2 =1;     set @x=1;     insert to location VALUES (1, ' Beijing ');     set @x=2;     insert to location VALUES (1, ' Wuxi ');     set @x=3; END; // DELIMITER;

Call the stored procedure with the result:

1 2 3 4 5 6 7 8 9 10

mysql> CALL handlerInsertWithException(); Query OK, 0 rowsaffected (0.09 sec) mysql> select@x; +------+ | @x   | +------+ |    3 | +------+ 1 row inset(0.00 sec)

Mysql> Select @x, @x2//
+--+--+
| @x | @x2 |
+--+--+
| 3 | 1 |
+--+--+
1 row in Set (0.00 sec)
The result shown above is @x=3, @x2 = 1, the same as the above analysis, indicating that the program is running correctly.

Description and conclusion:

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

Second, in Example 1, due to the comment of the exception declaration "", when 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 does not execute and exits.

Third, the definition of exception handling, at this time encountered an error will continue to follow the definition of the exception, but only the first data is inserted into the table, the user variable @x=3 description has been executed to the end;

DECLARE CONDITION Environment Declaration
    in error handling, You can use SQLState or specify an error code, in fact, you can define a name for them, and then use the defined name when processing. For example, see the following example:
Mysql> CREATE PROCEDURE pro18 ()
   , begin
    DECLARE ' constraint violation ' condition for SQLState ' 23000′; #注意constraint violation on both sides is "'", that is, tab top, 1 keys to the left of the key, not single quotes, Write single quotation marks will error
    declare exit handler for ' constraint violation ' rollback; #注意constraint On both sides of the violation is "'"
    start transaction;
   -INSERT INTO T2 (S1) values (1);
    INSERT INTO T2 (S1) values (1);
    commit;
    end;//
Query OK, 0 rows Affected (0.00 sec)

First, the SQLState ' 23000′ is defined as a name ' Constraintviolation ', which can be used directly in the operation. The T2 table is a InnoDB table, so the insert operation on this table is rollback rolled back, in this case, the rollback event occurs because the primary key inserts two identical values resulting in a sqlstate 23000 error. SQLState 23000 is a constraint error.
Call this stored procedure below and view the results of the operation:
Mysql> call Pro18 ()//
Query OK, 0 rows affected (0.04 sec)

Mysql> SELECT * from t2//
Empty Set (0.01 sec)
You can see that no records are inserted in the T2, and all transactions are rolled back.

Here are a few more statement conditions, first look at the example:
Mysql> CREATE PROCEDURE Pro19 ()
Begin
-Declare exit handler for not found begin end;
-Declare exit handler for SqlException begin end;
-Declare exit handler for sqlwarning begin end;
end;//
Query OK, 0 rows Affected (0.00 sec)
This example shows three pre-conditional declarations: Not fount is not found, SqlException is an error, sqlwarning is a warning or comment, and these three conditional declarations are pre-declared, so you do not need to declare the conditions to use. But if you use: Declare sqlexception condition .... This format, you will get an error.

Error code

1011 HY000 Error on delete of '%s ' (Errn%d)
1021 HY000 Disk Full (%s); Waiting for someone to free some space ...
1022 23000 Can ' t write; Duplicate key in table '%s '
1027 HY000 '%s ' is locked against change
1036 HY000 Table '%s ' is read only
1048 23000 Column '%s ' cannot be null
1062 23000 Duplicate entry '%s ' for key%d
1099 HY000 Table '%s ' is locked with a READ lock and can ' t is updated
1100 HY000 Table '%s ' is not locked with LOCK TABLES
1104 42000 The SELECT would examine more than max_join_size rows; Check yourwhere and use Set Sql_big_selects=1 or set sql_max_join_size=# if the SELECT Isokay
1106 42000 Incorrect parameters to procedure '%s '
1114 HY000 The table '%s ' is full
1150 HY000 Delayed Insert thread couldn ' t get requested lock for table%s
1165 HY000 INSERT DELAYED can ' t be used with table '%s ' because it is lockedwith LOCK TABLES
1242 21000 subquery returns more than 1 row
1263 22004 Column set to default value; Null supplied to not NULL column "'%s ' at row%ld
1264 22003 out of range value adjusted to column '%s ' at row%ld
1265 Data truncated for column '%s ' at row%ld
1312 0a000 SELECT in a stored program must has into
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION:%s
1325 24000 Cursor is already open
1326 24000 Cursor is not open
1328 HY000 incorrect number of FETCH variables
1329 No data to FETCH
1336 42000 use isn't allowed in a stored program
1337 42000 Variable or condition declaration after cursor or handlerdeclaration
1338 42000 Cursor declaration after handler declaration
1339 20000 case no found for Case statement
1348 HY000 Column '%s ' is not updatable
1357 HY000 Can ' t drop a%s from within another stored routine
1358 HY000 GOTO is isn't allowed in a stored program handler
1362 HY000 Updating of%s row is not allowed in%s trigger
1363 HY000 There is no%s row in%s trigger

Stored procedures are a major feature of MySQL advanced programming, and of course the stored procedures include a lot of knowledge, including error handling, predefined, and so on, let's look at the error handling section first.

Definition error:

To define a name for the error, the syntax is:

DECLARE error_name CONDITION for Condition_value;

Declare define a variable

Error_name: The name of the custom error

Condition_value can be in two situations:

First: Write the error number directly, such as 1305;

Second: Write SQLState error Number: if

SQLSTATE ' 42000 ';

Error handling

The syntax is:

DECLARE Handler_type handler for Condition_value

Begin

...

End

Handler_type: Process of processing.

CONTINUE continues to execute the unfinished stored procedure until the end. (Common, default)

| EXIT When an error occurs, automatically jumps out of the begin no longer executes the subsequent statement.

Condition_value: Trigger conditions for processing

SQLSTATE [VALUE] Sqlstate_value Needless to say, this is the second method mentioned above, and the most common error definition, check the error list yourself.

| Condition_name the name that we just defined, ErrorName is used here.

| SQLWarning represents all error codes that begin with 01

| The Not FOUND table all error codes starting with 02, and of course can also represent a cursor at the end of the dataset.

| SQLEXCEPTION represents all error codes except for sqlwarning and not FOUND.

| Mysql_error_code error number, the first method above, but also can be found in the error list, I am more commonly used.

Example:

CREATE PROCEDURE Error_test ()

Begin

#定义错误, 1305 is the stored procedure that called the error

Declare errname condition for 1305;

Declare continue handler for Errname

Begin

Select ' No that procedure ' as error;

End

Call AAA (); end;

Note:

If you need to see more error lists, you can go directly to the MySQL installation path.

Like my/usr/local/mysql/share/mysql/errmsg.txt.

Description: SQLSTATE [VALUE] Sqlstate_value This format is specifically for ANSI SQL and ODBC as well as other standards.

Not all MySQL ERROR CODE is mapped to SQLState.

MySQL defines exceptions and exception handling methods