DECLARE ... CONDITION

You can use the DECLARE ... CONDITION statement to declare and name exception conditions and associate the name with the handler specified in the exception condition.

The exception condition declaration must be prior to the cursor or exception handler declaration. 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
}

condition_name can be referenced in the subsequent DECLARE ... HANDLER statement.

condition_value specifies the condition or condition class associated with the exception condition name. It can be in any of the following forms:

• mysql_error_code: an integer literal of the error code. Note that code 0 indicates success rather than an error. Therefore, do not use 0 as an error code.

• SQLSTATE [VALUE] sqlstate_value: a five-character string literal that represents the SQLSTATE value. Do not use a SQLSTATE value that starts with “00”, because the value “00” indicates success rather than an error.

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

The name of the exception condition in the SIGNAL or RESIGNAL statement must be associated with the value of SQLSTATE rather than the error code.

The use of condition names can make the code of the stored program clearer. The following example shows an exception condition based on mysql_error_code:

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

The following example shows the exception condition based on the SQLSTATE value:

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