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

Specifies the value to be returned to the UAP.

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.

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

  1. If the SQL procedure statement that called the handler is not found before the RESIGNAL statement is executed, a runtime error may occur.
  2. If signal-value is not specified, the code 'R0000' indicating an error (abnormal termination without an implicit rollback) is set in the SQLSTATE.
  3. 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.
  4. 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.
  5. 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

  1. 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