Define the type of exception capture and how to handle it:
DECLARE handler_action Handler for Condition_value [, Condition_value] ... Statement handler_action: CONTINUE | EXIT | UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWarning | Not FOUND | SQLEXCEPTION
Here are a few things to note:
A, Condition_value [, Condition_value], this statement can include a variety of situations (square brackets to indicate optional), that is, a handler can be defined as the corresponding operation for a variety of situations; Condition_ Value can include 6 kinds of values listed above:
1, Mysql_error_code, this represents the MySQL error code, the error code is a number, the completion is defined by MySQL itself, this value can refer to the MySQL Database error code and information.
2, SQLSTATE [VALUE] Sqlstate_value, this is similar to the error code to form a one by one corresponding relationship, it is a 5-character string, the key point is that it is referenced from the ANSI SQL and ODBC standards, and therefore more standardized, Unlike the above error_code is completely mysql itself defined for their own use, this and the first similar can also refer to the MySQL Database error code and information.
3, Condtion_name, this is the condition name, it uses declare ... Condition statement, which we'll show you how to define your own condition_name.
4. SQLWarning, which indicates that the string in sqltate starts with ' 01 ' errors, such as error: 1311
SQLSTATE: 01000
( ER_SP_UNINIT_VAR
)
5, not FOUND, which indicates that the strings in Sqltate start with ' 02 ' errors, such as error: 1329
SQLSTATE: 02000
( ER_SP_FETCH_NO_DATA
)
6, SQLEXCEPTION, indicates that sqlstate in the string is not the ' 00 ', ' 01 ', ' 02 ' The beginning of those errors, where ' 00 ' beginning of the SQLSTATE actually represents a successful execution rather than error, the other two is the above 4 and 5 of the two cases.
The above 6 cases can actually be divided into two categories:
One is a more explicit treatment, which is to deal with the specified error conditions, including 1, 2, 3 of these three ways;
The other is the processing of the corresponding type of error, that is, the processing of a group of errors, including 4, 5, 6 of these three ways. This is the introduction of Condition_value. Another thing to note is that MySQL has its own error handling mechanism by default (that is, we do not define a method for handling errors-handler): 1, for sqlwarning and not found, the process is to ignore the error continue to execute, So in the case of the cursor, if we don't do a no_more_products=1 handler to the value of repeat's condition, then the loop will go on. 2, for SqlException, its default treatment method is in the wrong place on the termination.
b, statement, this is relatively simple is when a certain condition/error, we have to execute the statement, can be simple as SET var = value such as simple statement, can also be a complex multiline statement, multiple lines can use the begin .... End here to include the statement inside (this is like the case in Delphi, notice that our stored procedures are also many lines, so also begin ...) END).
C, handler_action, this means that when the above statement is executed, want to perform what kind of action, here include continue, exit, Undo, indicating continuation, exit, revocation (temporarily not supported). This is two kinds of action, in fact, these two actions in the above also said, continue is a sqlwarning and not found default processing method, and exit is the default processing method of SqlException.
Other:
Condition_name: Naming criteria
MySQL error code or SQLSTATE code is poorly readable, so a naming condition is introduced:
Grammar:
DECLARE condition_name condition for condition_value condition_value: SQLSTATE [value] Sqlstate_value | Mysql_error_code
Use:
# original DECLARE CONTINUE HANDLER for 1216 mysql_statements; # changed DECLARE foreign_key_error CONDITION for 1216; DECLARE CONTINUE HANDLER for Foreign_key_error mysql_statements;
Example:
CREATE PROCEDURE sp_add_location (in_location varchar), in_address1 varchar (+), In_ Address2 varchar (+), zipcode varchar (ten), out out_status varchar ()) BEGIN DECLARE CONTINUE HANDLER for 1062 SET out_status= ' Duplicate Entry '; SET out_status= ' OK '; INSERT into Locations (location,address1,address2,zipcode) VALUES (in_location,in_address1,in_ Address2,zipcode); END;
MySQL Error Handling in Stored procedures