The
RESIGNAL statement is used within a condition handler to resignal
the condition that activated the handler, or to raise an alternate
condition so that it can be processed at a higher level. It causes
an exception, warning, or not found condition to be returned, along
with optional message text.
Invocation
This statement can only be embedded
in a condition handler within a compound SQL (compiled) statement.
The compound SQL (compiled) statement can be embedded in an SQL procedure
definition, SQL function definition, or SQL trigger definition.
Authorization
If a module condition is
referenced, the privileges held by the authorization ID of the statement
must include EXECUTE privilege on the module.
Syntax
>>-RESIGNAL----------------------------------------------------->
>--+-----------------------------------------------------------------------------------+-><
| .-VALUE-. |
'-+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+--+------------------------+-'
| '-sqlstate-string-variable-' | '-| signal-information |-'
'-condition-name------------------------------------'
signal-information
|--SET--MESSAGE_TEXT-- = --+-SQL-variable-name----------+-------|
+-SQL-parameter-name---------+
'-diagnostic-string-constant-'
Description
- SQLSTATE VALUE sqlstate-string-constant
- The specified string constant represents an SQLSTATE. It must
be a character string constant with exactly 5 characters that follow
the rules for SQLSTATEs:
- Each character must be from the set of digits ('0' through '9')
or non-accented upper case letters ('A' through 'Z')
- The SQLSTATE class (first two characters) cannot be '00', since
this represents successful completion.
If the SQLSTATE does not conform to these rules, an error is
raised (SQLSTATE 428B3).
- SQLSTATE VALUE
- Specifies the SQLSTATE that will be returned. Any valid SQLSTATE
value can be used. The specified value must follow the rules for SQLSTATEs:
- Each character must be from the set of digits ('0' through
'9') or upper case letters ('A' through 'Z') without
diacritical marks
- The SQLSTATE class (first two characters) cannot be '00',
since this represents successful completion.
If the SQLSTATE does not conform to these rules, an error is
returned.- sqlstate-string-constant
- The sqlstate-string-constant must be
a character string constant with exactly 5 characters.
- sqlstate-string-variable
- The specified SQL variable or SQL parameter must be of data type
CHAR(5) and must not be the null value.
- condition-name
- Specifies
the name of a condition that will be returned. The condition-name must
be declared within the compound-statement or identify a condition
that exists at the current server.
- SET MESSAGE_TEXT =
- Specifies a string that describes the error or warning. The string
is returned in the sqlerrmc field of the SQLCA.
If the actual string is longer than 70 bytes, it is truncated without
warning.
- SQL-variable-name
- Identifies an SQL variable, declared within the compound statement,
that contains the message text.
- SQL-parameter-name
- Identifies an SQL parameter, defined for the routine, that contains
the message text. The SQL parameter must be defined as a CHAR or VARCHAR
data type.
- diagnostic-string-constant
- Specifies a character string constant that contains the message
text.
Notes
- If a RESIGNAL statement is issued without specifying an SQLSTATE
clause or a condition-name, the identical
condition that invoked the handler is returned. The SQLSTATE, SQLCODE
and the SQLCA associated with the condition are unchanged.
- If a RESIGNAL statement is issued using a condition-name that
has no associated SQLSTATE value and the condition is not handled,
SQLSTATE 45000 is returned and the SQLCODE is set to -438. Note that
such a condition will not be handled by a condition handler for SQLSTATE
45000 that is within the scope of the routine issuing the RESIGNAL
statement.
- If a RESIGNAL statement is issued using an SQLSTATE value or a condition-name with
an associated SQLSTATE value, the SQLCODE returned is based on the
SQLSTATE value as follows:
- If the specified SQLSTATE class is either '01' or '02', a warning
or not found condition is returned and the SQLCODE is set to +438.
- Otherwise, an exception condition is returned and the SQLCODE
is set to -438.
- A RESIGNAL statement has the indicated fields of the SQLCA set
as follows:
- sqlerrd fields are set to zero
- sqlwarn fields are set to blank
- sqlerrmc is set to the first 70 bytes of
MESSAGE_TEXT
- sqlerrml is set to the length of sqlerrmc,
or to zero if no SET MESSAGE_TEXT clause is specified
- sqlerrp is set to ROUTINE
- Refer to the "Notes"
section under "SIGNAL statement" for further information about SQLSTATE
values.
Example
This example detects a division
by zero error. The IF statement uses a SIGNAL statement to invoke
the overflow condition handler. The condition
handler uses a RESIGNAL statement to return a different SQLSTATE value
to the client application.
CREATE PROCEDURE divide ( IN numerator INTEGER,
IN denominator INTEGER,
OUT result INTEGER)
LANGUAGE SQL
BEGIN
DECLARE overflow CONDITION FOR SQLSTATE '22003';
DECLARE CONTINUE HANDLER FOR overflow
RESIGNAL SQLSTATE '22375';
IF denominator = 0 THEN
SIGNAL overflow;
ELSE
SET result = numerator / denominator;
END IF;
END