DECLARE ... CONDITION

The DECLARE ... CONDITION statement is used to declare an exception condition and assign a name to it, and associate the name with the handler specified by the exception condition.

The declaration of an exception condition must appear before the declaration of a cursor or handler. The syntax of the DECLARE ... CONDITION statement is as follows:

DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

The condition_name can be referenced in a subsequent DECLARE ... HANDLER statement.

The condition_value specifies the condition or condition class associated with the exception condition name. It can take the following forms:

• mysql_error_code: an integer literal that represents an error code. Note that code 0 indicates success, not an error, so do not use 0 to represent an error code.

• SQLSTATE [VALUE] sqlstate_value: a 5-character string literal that represents an SQLSTATE value. Do not use SQLSTATE values that start with '00', as these indicate success, not an error.

For more information about error codes and SQLSTATE values, see Error codes (MySQL mode) in the OceanBase Database System Reference.

The name of an exception condition used in a SIGNAL statement or with the RESIGNAL statement must be associated with an SQLSTATE value, not an error code.

Using condition names can make stored program code clearer. Here is an example of an exception condition based on mysql_error_code:

DECLARE unknown_column CONDITION FOR 5217;
DECLARE CONTINUE HANDLER FOR unknown_column
  BEGIN
    -- Handler body
  END;

Here is an example of an exception condition based on an SQLSTATE value:

DECLARE unknown_column CONDITION FOR SQLSTATE '42S22';
DECLARE CONTINUE HANDLER FOR unknown_column
  BEGIN
    -- Handler body
  END;