The
SIGNAL statement is used to signal an error or warning condition.
It causes an error or warning to be returned with the specified SQLSTATE,
along with optional message text.
Invocation
This statement
can be embedded in an:
- SQL procedure definition
- Compound SQL (compiled) statement
- Compound SQL (inlined) statement
The compound statements can be embedded in an SQL procedure definition,
SQL function definition, or SQL trigger definition. It is not an executable
statement and cannot be dynamically prepared.
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
>>-SIGNAL------------------------------------------------------->
.-VALUE-.
>--+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+-------->
| '-sqlstate-string-variable-' |
'-condition-name------------------------------------'
>--+------------------------+----------------------------------><
'-| signal-information |-'
signal-information
|--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression-+------|
'-(--diagnostic-string-expression--)------------------'
Description
- 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.
In the context of a compound SQL (inlined) statement, a
MERGE statement, or as the only statement in a trigger body, the following
rules must also be applied:
- The SQLSTATE class (first two characters) cannot be '01' or '02',
since these are not error classes.
- If the SQLSTATE class starts with the numbers '0' through '6'
or the letters 'A' through 'H', then the subclass (the last three
characters) must start with a letter in the range of 'I' through 'Z'.
- If the SQLSTATE class starts with the numbers '7', '8', '9', or
the letters 'I' through 'Z', then the subclass can be any of '0' through
'9' or 'A' through 'Z'.
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 (SQLSTATE 42373).
- 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.
- diagnostic-string-expression
- A literal string, or a local variable or parameter that describes
the error condition. If the string is longer than 70 bytes, it is
truncated.
- (diagnostic-string-expression)
- An expression of
type CHAR or VARCHAR that returns a character string of up to 70 bytes to describe the error
condition. If the string is longer than 70 bytes, it is truncated. This option is only provided within the scope of a CREATE TRIGGER statement for
compatibility with previous versions of DB2®. Regular use
of this option is not recommended.
Notes
- If a SIGNAL 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 SIGNAL statement.
- If a SIGNAL 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 SIGNAL 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
- SQLSTATE values are composed of a two-character class code value,
followed by a three-character subclass code value. Class code values
represent classes of successful and unsuccessful execution conditions.
Any valid SQLSTATE value can be used in the SIGNAL statement.
However, it is recommended that programmers define new SQLSTATEs based
on ranges reserved for applications. This prevents the unintentional
use of an SQLSTATE value that might be defined by the database manager
in a future release.
- SQLSTATE classes that begin with the characters '7' through '9',
or 'I' through 'Z' may be defined. Within these classes, any subclass
may be defined.
- SQLSTATE classes that begin with the characters '0' through '6',
or 'A' through 'H' are reserved for the database manager. Within these
classes, subclasses that begin with the characters '0' through 'H'
are reserved for the database manager. Subclasses that begin with
the characters 'I' through 'Z' may be defined.
Example
An
SQL procedure for an order system that signals an application error
when a customer number is not known to the application. The ORDERS
table includes a foreign key to the CUSTOMER table, requiring that
the CUSTNO exist before an order can be inserted.
CREATE PROCEDURE SUBMIT_ORDER
(IN ONUM INTEGER, IN CNUM INTEGER,
IN PNUM INTEGER, IN QNUM INTEGER)
SPECIFIC SUBMIT_ORDER
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = 'Customer number is not known';
INSERT INTO ORDERS (ORDERNO, CUSTNO, PARTNO, QUANTITY)
VALUES (ONUM, CNUM, PNUM, QNUM);
END