(11) condition definition and processing in mysql, 11 mysql

(11) condition definition and processing in mysql, 11 mysql
Overview

In MySQL, specific exceptions must be handled. These exceptions can be associated with errors and general process control in subprograms.Definition exceptionIt is a problem encountered during the execution of the program. Exception Handling defines the handling method that should be taken in case of a problem, 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

Handler_type indicates 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 specified parameter condition_value indicates the error type. This parameter has six values.sqlstate_valueAndmysql_error_codeIt is the same as the condition definition.condition_nameIs the condition name defined by DECLARE.SQLWARNINGIt indicates all sqlstate_value values starting with 01.NOT FOUNDIt indicates all sqlstate_value values starting with 02.SQLEXCEPTIONIndicates all sqlstate_value values NOT captured by SQLWARNING or not found.
Optional values 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 way to capture   state_value is to capture the value of sqlstate_value. If sqlstate_value is 42S02, perform the CONTINUE operation and output "can not find" information.
The second method of zookeeper 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 of condition escalation is to first define the condition and then call the condition. Here, the can_not_find condition is defined first, and the CONTINUE operation will be executed in case of a 1146 error.
  Warning 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 of limit distinct 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 of zookeeper 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