DECLARE CONDITION

Declares a condition name for an exception condition value.

 

Usage

Procedural.

Description

A condition declaration defines an identifier that can be used as a name for exceptions and/or SQLSTATE values. The identifier can be used in SIGNAL and RESIGNAL statements as well as in handler declarations. This results in a code that is easier to read and understand compared with using explicit SQLSTATE values.

A condition identifier can optionally be associated with an SQLSTATE value. Using a condition identifier with an associated SQLSTATE value in a SIGNAL or RESIGNAL statement means that the associated SQLSTATE value is signaled as well. If the condition identifier does not have an associated SQLSTATE value, the SQLSTATE value 45000 is signaled.

Restrictions

A condition name which represents an SQLSTATE value may only be declared to represent a specific SQLSTATE value, i.e. it is not possible to declare a condition name to represent an exception class group. For a description of exception class groups, see DECLARE HANDLER.

The scope of a condition name covers all the procedural SQL statements in the compound statement declaring it, including any other compound statements nested within it.

The general naming rules for a condition name are the same as those for other database objects.

If a condition name is declared to represent a particular SQLSTATE value, another condition name cannot exist for that same SQLSTATE value which has exactly the same scope.

A condition name cannot be declared for an SQLSTATE value with class ‘successful completion’, this covers all SQLSTATE values starting with 00.

Notes

The SQLSTATE value string is five characters long and contains only alphanumeric characters.

In Mimer SQL any SQLSTATE value that falls outside the range of standard SQLSTATE values is treated as an implementation-defined value.

Standard SQLSTATE values begin with the characters A-I, S, 0-4 and 7, while implementation-defined SQLSTATE values begin with the characters J-R, T-Z, 5-6 and 8-9.

Example

DECLARE invalid_parameter CONDITION;

 

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

   DECLARE condition_name VARCHAR(128);

   GET DIAGNOSTICS EXCEPTION 1 condition_name = CONDITION_IDENTIFIER;

    IF condition_name = 'invalid_parameter' THEN

      ...

   END IF;

END;

 

SIGNAL invalid_parameter;

 

For more information, see Return Status and Conditions or Mimer SQL Programmer's Manual, Declaring Condition Names.

For information about the SIGNAL statement, see SIGNAL. For information about the RESIGNAL statement, see RESIGNAL.

Standard Compliance