SIGNAL | Teradata Vantage - SIGNAL - Advanced SQL Engine - Teradata Database

Purpose

SIGNAL explicitly raises an exception, completion condition, not including Success, or user-defined condition in the Diagnostics Area.

Invocation

Executable.

Stored procedures only.

Syntax

SIGNAL {
  condition_name |
  SQLSTATE [VALUE] SQLSTATE_code
} [ SET condition_information_item = value ] ;

where:

condition_name

The name of a variable declared to identify a condition within an SQL stored procedure.

If condition_name specifies a condition that corresponds to an SQLSTATE value, the value of that SQLSTATE is assigned to RETURNED_SQLSTATE in the Condition Area.

SQLSTATE [VALUE] SQLSTATE_code

The value for an SQLSTATE to be assigned to RETURNED_SQLSTATE in the Condition Area.

value

A text or numeric value to be assigned to the specified condition information name.

condition_information_item

One of the following field names from the Condition Area of the Diagnostics Area as shown in the following table.

CLASS_ORIGIN

The identification of the naming authority that defined the class value of RETURNED_SQLSTATE.

The value must be ISO 9075 if the class value is defined in the ANSI/ISO SQL:2011 standard or Teradata if the class value is a Teradata class extension to the SQL:2011 standard.

Data type: VARCHAR(128) CHARACTER SET UNICODE

Default: null

CONDITION_IDENTIFIER

The condition name specified in a SIGNAL or RESIGNAL statement.

Data type: VARCHAR(128) CHARACTER SET UNICODE

Default: null

CONDITION_NUMBER

Takes values from 1 to 16, where 16 is the maximum number of conditions that can be stored in the diagnostics area.

Data type: INTEGER

Default: 0

MESSAGE_LENGTH

The length of MESSAGE_TEXT in characters.

Data type: INTEGER

Default: 0

MESSAGE_TEXT

The text of the Error or Warning message returned by the previous SQL statement execution or a message specified in a SIGNAL or RESIGNAL statement as signal information.

Data type: VARCHAR(128) CHARACTER SET UNICODE

Default: null

RETURNED_SQLSTATE

The SQLSTATE value returned by the previous SQL statement, the SQLSTATE value specified in a SIGNAL or RESIGNAL statement, or the SQLSTATE value associated with the condition name if a condition name is specified in a SIGNAL or RESIGNAL statement.

Data type: CHARACTER(5) CHARACTER SET LATIN

Default: null

SUBCLASS_ORIGIN

The identification of the naming authority that defined the subclass value of RETURNED_SQLSTATE.

The value must be ISO 9075 if the class value is defined in the ANSI/ISO SQL:2011 standard or Teradata if the class value is a Teradata subclass extension to the SQL:2011 standard.

Data type: VARCHAR(128) CHARACTER SET UNICODE

Default: null

Usage Notes

When a SIGNAL statement is executed, the Diagnostics Area is emptied and the Statement Area is filled in with the details of the SIGNAL statement and a Condition Area with condition number 1 is added in the Diagnostics Area corresponding to the SQLSTATE value or condition name specified in the SIGNAL statement. If signal information is specified in the SIGNAL statement, this added condition area is modified with the details given in the signal information.

Rules

Contents of the Statement Area

The following table specifies the contents of the Statement Area after the execution of a SIGNAL statement:

Contents of Condition Area 1

The following table specifies the contents of Condition Area 1 after the execution of a SIGNAL statement:

Example: Using a Condition Name In a SIGNAL Statement and a Handler Declaration

The following example illustrates the usage of a condition name in a SIGNAL statement and a handler declaration defined for the SQLSTATE value associated with the condition name.

During stored procedure execution via Req1, the value of InParam2 is zero and the SIGNAL statement is executed.

The SIGNAL statement invokes the EXIT handler defined to handle SQLSTATE '22012'. Note that condition name divide_by_zero is associated with SQLSTATE '22012'.

Though the SIGNAL statement uses divide_by_zero and the handler is defined to handle the SQLSTATE value associated with divide_by_zero, the handler for SQLSTATE '22012' is invoked.

After successfully executing the handler action statement, control exits compound statement cs1 and the stored procedure terminates.

CREATE PROCEDURE signalsp3 (IN  InParam1 INTEGER,
                            IN  InParam2 INTEGER,
                            OUT OParam3  INTEGER)
 cs1:BEGIN
     DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
     DECLARE EXIT HANDLER FOR SQLSTATE '22012'
         SET OParam3 = 0;
     IF (InParam2 = 0) THEN
         SIGNAL divide_by_zero;
     ELSE
         SET OParam3 = InParam1 + InParam2;
         ...
     END IF;
     ...
 END cs1;
BTEQ> CALL signalsp3(10, 0, OParam3);

Example: Using a Condition Name in a SIGNAL Statement

The following example illustrates the usage of a condition name in a SIGNAL statement.

During stored procedure execution via Req1, the value of InParam2 is zero and the SIGNAL statement is executed.

The SIGNAL statement looks for a handler defined for divide_by_zero or its associated SQLSTATE '22012'.

Because there is an EXIT handler defined to handle divide_by_zero, it is invoked.

After successfully executing the handler action statement, control exits compound statement cs1 and the stored procedure terminates.

CREATE PROCEDURE signalsp4 (IN  InParam1 INTEGER,
                            IN  InParam2 INTEGER,
                            OUT OParam3  INTEGER)
cs1:BEGIN
    DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    DECLARE EXIT HANDLER FOR divide_by_zero
        SET OParam3 = 0;
    IF (InParam2 = 0) THEN
        SIGNAL divide_by_zero;
    ELSE
        SET OParam3 = InParam1 + InParam2;
        ...
    END IF;
    ...
END cs1;
BTEQ> CALL signalsp4(10, 0, OParam3);

Example: The CONTINUE handler and Exceptions Raised by the SIGNAL Statement

In the following example, the CONTINUE handler defined in the containing outer compound statement handles the exception raised by the SIGNAL statement.

During the stored procedure execution, the SIGNAL statement signals divide_by_zero.

Because there is no handler defined to handle divide_by_zero in cs2, the SQLSTATE value associated with divide_by_zero is propagated to outer compound statement cs1 and is handled by the CONTINUE handler in cs1.

After the successful completion of the handler action statement, control returns to the line following SIGNAL divide_by_zero.

CREATE PROCEDURE signalsp5 (IN  InParam1 INTEGER,
                            IN  InParam2 INTEGER,
                            OUT OParam3  INTEGER)
cs1: BEGIN
     DECLARE CONTINUE HANDLER FOR SQLSTATE '22012'
         SET OParam3=1;
     DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'
     cs2: BEGIN
         IF (InParam2 = 0) THEN
             SIGNAL divide_by_zero;
             …
         ELSE
             SET OParam3 = InParam1 + InParam2;
             …
         END IF;
         …
     END cs2;
     …
END cs1;
BTEQ> CALL signalsp5(10, 0, OParam3);

Example: Using Signal Information In a SIGNAL Statement

The following example illustrates the use of signal information in a SIGNAL statement.

During stored procedure execution, the SIGNAL statement sets MESSAGE_TEXT as 'balance is too low' and CLASS_ORIGIN as 'Stored Procedure'.

MESSAGE_LENGTH is implicitly set to 19.

The EXIT handler handles the condition.

The GET DIAGNOSTICS statement retrieves the MESSAGE_TEXT and CLASS_ORIGIN from the first condition area and assigns them to the output parameters, Message and Class.

After successful execution of the EXIT handler action statements, control exits compound statement cs1 and the stored procedure terminates.

CREATE PROCEDURE setsignalsp1 (INOUT acno    INTEGER,
                               INOUT amt     FLOAT,
                               OUT   Message VARCHAR(50),
                               OUT   Class   VARCHAR(50))
cs1: BEGIN
     DECLARE balance_too_low CONDITION;
     DECLARE count INTEGER DEFAULT 0;
     DECLARE bal_amt, balance FLOAT;
     DECLARE EXIT HANDLER FOR balance_too_low
     BEGIN
         GET DIAGNOSTICS EXCEPTION 1
            Message = MESSAGE_TEXT, Class = CLASS_ORIGIN;
         SET count = count + 1;
         INSERT INTO errortbl VALUES (acno, count, User,
                     current_timestamp, 'Balance too low for the
                                         account');
     END;
     SELECT balamt INTO balance
     FROM Deposit
     WHERE accountno = acno;
     SET bal_amt = balance - amt;
     IF  (bal_amt < 1000) THEN
          SIGNAL balance_too_low
                 SET MESSAGE_TEXT = 'Balance is too low',
                     CLASS_ORIGIN = 'Stored Procedure';
     ELSE
         UPDATE Deposit
         SET balance = bal_amt
         WHERE accountno = acno;
     END IF;
END cs1;

Example: The Condition Declaration and the SIGNAL Statement

In the following example, the condition declaration that is more local to the scope of the compound statement containing the SIGNAL statement gets used.

During stored procedure execution, the value of InParam2 is zero and the SIGNAL statement in compound statement cs2 raises a user-defined condition divide_by_zero.

Because there is a handler for this condition in compound statement cs2, it is invoked. Even though compound statement cs1 has a handler defined to handle divide_by_zero, it is not invoked because the condition declaration is more local to the scope of the compound statement containing the SIGNAL statement.

Stored procedure execution continues after executing the handler action statement.

CREATE PROCEDURE signalsp7 (IN  InParam1 INTEGER,
                            IN  InParam2 INTEGER,
                            OUT OParam3  INTEGER)
cs1: BEGIN
     DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
     DECLARE EXIT HANDLER FOR divide_by_zero
         SET OParam3 = 0;
     cs2: BEGIN
         DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
         DECLARE EXIT HANDLER FOR divide_by_zero
             SET OParam3 = 10;
         IF (InParam2 = 0) THEN
             SIGNAL divide_by_zero;
         ELSE
             SET OParam3 = InParam1 + InParam2;
             …
         END IF;
         …
     END cs2;
     …
END cs1;
BTEQ> CALL signalsp7 (10, 0, OParam3);

Related Topics