RESIGNAL statement (Resignal error)
Function
Generates and signals an error, and adds diagnostic information to the diagnostic area.
Format
RESIGNAL [signal-value]
signal-value::={SQLSTATE-value|condition-name}
SQLSTATE-value::=SQLSTATE [VALUE] character-string-literal |
Operands
- signal-value::={SQLSTATE-value|condition-name}
Specifies the value to be returned to the UAP.
- SQLSTATE-value::=SQLSTATE [VALUE] character-string-literal
Specifies a value (combination of upper case characters A to Z, and numeric characters 0 to 9) that is valid as an SQLSTATE value, in 5 characters. Specify a value according to the following rules for SQLSTATE in HiRDB.
- The values 00, 01, 02, and R2 cannot be specified as an SQLSTATE class (the first two characters). These values are not error-indicating classes.
- SQLSTATE classes beginning with the character 0 to 5, A to I, or R cannot be specified. These values are reserved by HiRDB.
If the SQLSTATE class fails to comply with these rules, a definition-time error may occur. For SQLSTATE values, see SQLSTATE variable.
Specifies the condition name that was declared in the condition declaration.
If condition-name is specified, the code 'R0000' indicating an error (abnormal termination without an implicit rollback) is set in the SQLSTATE.
If an SQLCODE value associated with condition-name is defined, an error may occur.
Common rules
- If the SQL procedure statement that called the handler is not found before the RESIGNAL statement is executed, a runtime error may occur.
- If signal-value is not specified, the code 'R0000' indicating an error (abnormal termination without an implicit rollback) is set in the SQLSTATE.
- Execution of the RESIGNAL statement causes the assignment of the following values in the SQLCODE:
- With signal-value not specified:
The SQLCODE is not changed. The value that was set when the SQL procedure statement called the handler is retained.
- With signal-value specified:
The value -1400 is assigned.
- Execution of the RESIGNAL statement does not cause an implicit rollback. However, if the statement is executed in the trigger, an implicit rollback ensues, by excluding the tables that are defined by specifying WITHOUT ROLLBACK. Tables that are defined by specifying WITHOUT ROLLBACK are not rolled back after completion of row updating (including additions and deletions) even when the RESIGNAL statement is executed in the trigger.
- Execution of the RESIGNAL statement causes the following types of information to be assigned to the diagnostic area:
- With signal-value not specified:
Diagnostic information is not updated.
- With signal-value specified:
In the statement information item NUMBER of the diagnostic area, the value i + 1 (the value before execution of the RESIGNAL statement is i), and the value N is assigned to MORE. The information that was assigned to the i-the condition information item in the diagnostic area (condition number i), is re-assigned to the i + 1st item (condition number i + 1). If the maximum number of condition information items is exceeded, the value Y is set in the statement information item MORE. In the first (condition number 1) ERROR_SQL condition information item, the value 'REGIONAL' is assigned. If a condition name is specified in signal-value, a condition name is assigned to CONDITION_IDENTIFIER. If SQLSTATE-value is specified, 'SQLSTATE:xxxxx' (where xxxxx is the specified SQLSTATE-value) is assigned to CONDITION_IDENTIFIER.
Note
- The RESIGNAL statement can be specified in SQL procedures and triggers.
Example
For the inventory table (STOCK), specify an SQL procedure (STOCK_UPDATE2) that updates the quantity of a specified product code. If the specified quantity is less than 0, a matching product is not found, or the update process fails, the RESIGNAL statement generates an error, and respective SQLSTATE values are set.
CREATE PROCEDURE STOCK_UPDATE2(IN UPCODE INT, IN UQUANTITY INT)
BEGIN
DECLARE illegal_value CONDITION ;
DECLARE EXIT HANDLER FOR illegal_value
RESIGNAL SQLSTATE VALUE '66001';
DECLARE EXIT HANDLER FOR NOT FOUND
SIGNAL SQLSTATE VALUE '66002';
DECLARE EXIT HANDLER FOR SQLERROR
RESIGNAL SQLSTATE VALUE '66003';
IF UQUANTITY<0 THEN
SIGNAL illegal_value;
ELSE
UPDATE STOCK SET SQUANTITY=UQUANTITY WHERE SPCODE=UPCODE;
END IF;
END