The DECLARE ... CONDITION statement is used to declare an exception condition, give it a name, and associate the name with the handler specified for that condition.
Exception condition declarations must appear before cursor or handler declarations. The syntax for the DECLARE ... CONDITION statement is as follows:
DECLARE condition_name CONDITION FOR condition_value
condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
}
condition_name can be referenced in the subsequent DECLARE ... HANDLER statement.
condition_value specifies the condition or condition category associated with the exception condition name. It can take the following forms:
mysql_error_code: An integer literal representing the error code. Note that code 0 indicates success, not an error, so do not use it to represent an error code.SQLSTATE [VALUE] sqlstate_value: A five-character string literal representing theSQLSTATEvalue. Do not useSQLSTATEvalues that start with '00', as these indicate success rather than an error.
For more information about error codes and SQLSTATE values, see Error codes in OceanBase AI Database System Reference.
The exception condition name in the SIGNAL or RESIGNAL statement must be associated with an SQLSTATE value, not with an error code.
Using condition names can make the stored program code clearer. The following example shows exception conditions based on mysql_error_code:
DECLARE unknown_column CONDITION FOR 5217;
DECLARE CONTINUE HANDLER FOR unknown_column
BEGIN
-- Main Body of Handler
END;
Examples of exception conditions based on SQLSTATE values are as follows:
DECLARE unknown_column CONDITION FOR SQLSTATE '42S22';
DECLARE CONTINUE HANDLER FOR unknown_column
BEGIN
-- Main Body of Handler
END;