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 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 comprised 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.
Examples
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