(one) condition definition, handling in MySQL

Overview

In MySQL, specific exceptions require specific processing. These exceptions can be linked to errors, as well as general process control in subroutines. 定义异常is to define in advance the problems encountered during the execution of a program, which defines how the process should be handled when a problem is encountered, and that the stored procedure or function can continue to execute when it encounters an error or a warning. This will enhance the ability of the program to handle problems, and prevent the program from stopping abnormally.

definition of the condition

declare condition_name condition for condition_value;

Where the Condition_name parameter represents the name of the condition, the Condition_value parameter represents the type of condition, and the Sqlstate_value parameter and Mysql_error_code parameter can represent the MySQL error. For example, in error 1146 (42S02), the Sqlstate_value value is the 42s02,mysql_error_code value is 1146.

example of a condition definition
The following defines "error 1146 (42S02)", which is named Can_not_find. It can be defined in two different ways, with the following code:

#方法一：使用sqlstate_value DECLARE can_not_find  CONDITION FOR sqlstate ‘42S02‘; #方法二：使用mysql_error_code DECLARE can_not_find  CONDITION FOR 1146;

Processing of conditions

handler for condition_value[,...] sp_statementhandler_type: continue|exit|undocondition_name:|sqlwarning|not found|sqlexception| mysql_error_code

?? Where the Handler_type parameter indicates how the error is handled, and the parameter has 3 values. These 3 values are continue, exit, and Undo, respectively. Continue indicates that an error has not been processed, continues to execute downward, exits immediately after an error has been encountered, and Undo indicates that the operation was not supported until the error was encountered and is temporarily unsupported in MySQL.
?? The Condition_value parameter indicates the type of error, which has 6 values. sqlstate_valueand the mysql_error_code same meaning as in the condition definition. condition_nameis the condition name defined by the declare. SQLWARNINGrepresents all sqlstate_value values that begin with 01. NOT FOUNDrepresents all sqlstate_value values that begin with 02. SQLEXCEPTIONrepresents all sqlstate_value values that are not captured by sqlwarning or not found.
?? Sp_statement represents some execution statements for stored procedures or functions.

Example of defining a condition handler

#方法一: Capturing Sqlstate_value DECLARE CONTINUE HANDLER  for SQLSTATE ' 42s02 ' SET @info =' CAN not FIND ' ;#方法二: Capturing Mysql_error_code DECLARE CONTINUE HANDLER  for 1146 SET @info =' CAN not FIND ';#方法三: Define the condition first and then call DECLARE can_not_find CONDITION for 1146; DECLARE CONTINUE HANDLER  for can_not_find SET@info =' Can not find ';#方法四: Use sqlwarning DECLARE EXIT HANDLER  for sqlwarning SET @info =' ERROR ';#方法五: Use not FOUND DECLARE EXIT HANDLER   for not FOUND SET @info =' CAN not FIND ';#方法六: Use SqlException DECLARE EXIT HANDLER  for SQLEXCEPTION SET @info =' ERROR ';

?? The first method is to capture the Sqlstate_value value. If you encounter a Sqlstate_value value of 42S02, perform a continue operation and output "CAN not FIND" information.
?? The second method is to capture the Mysql_error_code value. If a mysql_error_code value of 1146 is encountered, the continue operation is performed and the "CAN not FIND" message is output.
?? The third method is to define the condition before calling the condition. The can_not_find condition is defined first, and the continue operation is performed with a 1146 error.
?? The fourth method is to use SQLWarning. SQLWarning captures all Sqlstate_value values starting with 01, then executes the exit operation and outputs the "ERROR" information.
?? The fifth method is to use not FOUND. Not found captures all sqlstate_value values starting with 02, then performs the exit operation and outputs "CAN not FIND" information.
?? The sixth method is to use SqlException. SqlException captures all sqlstate_value values that are not captured by the sqlwarning or not found, and then performs the exit operation and outputs the "ERROR" information.

About conditional processing use in stored procedures or functions see

(9) Stored procedures and custom functions in MySQL

(one) condition definition, handling in MySQL