(11) condition definition and processing in mysql

(11) condition definition and processing in mysql
Overview

In MySQL, specific exceptions must be handled. These exceptions can be associated with errors and general process control in subprograms. Defining exceptions is a problem encountered during the execution of the program in advance. Exception Handling defines the handling methods to be taken when a problem occurs, it also ensures that stored procedures or functions can continue to be executed in case of errors or warnings. In this way, the program's ability to handle problems can be enhanced to avoid abnormal program stop.

Condition definition

declare condition_name condition for condition_value;

The condition_name parameter indicates the condition name, The condition_value parameter indicates the condition type, and the sqlstate_value parameter and mysql_error_code parameter both indicate MySQL errors. For example, in ERROR 1146 (42S02), sqlstate_value is 42S02, and mysql_error_code is 1146.

Condition definition example
The ERROR "ERROR 1146 (42S02)" is defined as can_not_find. You can define it in two different ways. The Code is as follows:

# Method 1: Use sqlstate_value DECLARE can_not_find condition for sqlstate '42s02 '; # Method 2: Use mysql_error_code DECLARE can_not_find condition for 1146;

Condition Processing

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

?? The handler_type parameter specifies the error handling method. This parameter has three values. The three values are CONTINUE, EXIT, and UNDO respectively. CONTINUE indicates that if an error is encountered, it is not processed. if the error is encountered, it is exited immediately. If the error is encountered, UNDO indicates that the operation before the error is recalled. This method is not supported currently in MySQL.
?? The condition_value parameter specifies the error type. This parameter has six values. Sqlstate_value and mysql_error_code share the same meaning with the condition definition. Condition_name is the condition name defined by DECLARE. SQLWARNING indicates all sqlstate_value values starting with 01. Not found indicates all sqlstate_value values starting with 02. SQLEXCEPTION indicates all sqlstate_value values that are NOT captured by SQLWARNING or not found.
?? Sp_statement indicates the execution statements of some stored procedures or functions.

Example of defining a condition Handler

# Method 1: capture sqlstate_value declare continue handler for sqlstate '42s02 'SET @ info = 'can not find'; # Method 2: capture mysql_error_code declare continue handler for 1146 SET @ info = 'can not find '; # method 3: first define the CONDITION and then call DECLARE can_not_find condition for 1146; declare continue handler for can_not_find SET @ info = 'can not find '; # Method 4: Use sqlwarning declare exit handler for sqlwarning set @ info = 'error'; # Method 5: use not found declare exit handler for not found set @ info = 'can not find '; # Method 6: Use sqlexception declare exit handler for sqlexception set @ info = 'error ';

?? The first method is to capture the sqlstate_value value. If sqlstate_value is 42S02, perform the CONTINUE operation and output "can not find" information.
?? The second method is to capture the value of mysql_error_code. If the value of mysql_error_code is 1146, perform the CONTINUE operation and output "can not find" information.
?? The third method is to define the conditions before calling the conditions. Here, the can_not_find condition is defined first, and the CONTINUE operation will be executed in case of 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 "ERROR" information.
?? The fifth method is not found. Not found captures all sqlstate_value values starting with 02, then executes the EXIT Operation and outputs "can not find" information.
?? The sixth method is to use SQLEXCEPTION. SQLEXCEPTION captures all sqlstate_value values NOT captured by SQLWARNING or not found, then executes the EXIT Operation and outputs "ERROR" information.


For how to use conditional processing in stored procedures or functions, see

(9) stored procedures and user-defined functions in mysql