Special Condition Handling for SIGNAL & RESIGNAL Statements | Teradata Vantage - Special Condition Handling for SIGNAL and RESIGNAL Statements - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
The condition names defined in a condition declaration can be specified in a condition handler declaration for two purposes:
  • To document the program by allowing you to associate a descriptive symbolic name for a specific SQLSTATE value.
  • To enable you to specify user-defined conditions and to enable the condition handlers to act on them when the procedure uses the condition name in a SIGNAL or RESIGNAL statement.
The following usage rules apply to specifying a condition name in a handler declaration.
  • A condition name specified in a handler declaration must be defined either within the containing compound statement or within a containing outer compound statement.

    The following example illustrates how to specify a condition name and its associated SQLSTATE value in a handler. In this example, the condition declaration at line 3 defines condition name divide_by_zero, associating it with SQLSTATE '22012'. The EXIT handler at line 4 is then defined to handle divide_by_zero. While the procedure executes, the divide by zero exception with SQLSTATE '22012' is raised at line 8, and is handled by the EXIT handler for divide_by_zero. After the successful completion of the EXIT handler statements, compound statement cs1 yields its control of the procedure, which then completes successfully.

    1.   CREATE PROCEDURE condsp1 (
           INOUT IOParam2 INTEGER,
           OUT   OParam3  INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; 
    4.       DECLARE EXIT HANDLER 
    5.           FOR divide_by_zero, SQLSTATE '42000' 
    6.               SET OParam3 = 0; 
    7.       SET IOParam2=0;
    8.       SET OParam3 = 20/IOParam2; /* raises exception 22012 */ 
    9.   END cs1;
  • If a condition value in a handler declaration specifies SQLEXCEPTION, SQLWARNING, or NOT FOUND, you cannot also specify a condition name in the same handler declaration. Otherwise, the procedure aborts and returns an error to the requestor during its compilation.

    The following example illustrates how to specify a condition name and a generic condition in different handlers. In this example, a handler is defined at line 4 for SQLEXCEPTION, and a separate handler is defined for condition name divide_by_zero at line 7.

    1.   CREATE PROCEDURE condsp1 (
           OUT OParam3 INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    4.       DECLARE EXIT HANDLER 
    5.           FOR SQLEXCEPTION 
    6.               SET OParam3 = 0;
    7.       DECLARE EXIT HANDLER 
    8.           FOR divide_by_zero 
    9.               SET OParam3 = 1;
    10.   …
    11.  END cs1;
         DROP TABLE notab;
         CALL condsp1(IOParam2, OParam3);

    The following example illustrates the non-valid specification of a condition name and a generic condition in the same handler. In this example, the handler at line 5 specifies both SQLEXCEPTION and the condition name divide_by_zero, which is not valid. A procedure defined in this way aborts and returns an error to the requestor during its compilation.

    1.   CREATE PROCEDURE condsp2 (
           OUT OParam3 INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    4.       DECLARE EXIT HANDLER 
    5.           FOR SQLEXCEPTION, divide_by_zero 
    6.               SET OParam3 = 0; 
    7.    …
    8.   END cs1;
  • A condition name cannot be specified more than once in a handler declaration. A procedure defined in this way aborts and returns an error to the requestor during its compilation.

    The following example illustrates the non-valid specification of the same condition name twice in the same handler. In this example, condition name divide_by_zero is specified more than once in the handler at line 5.

    1.   CREATE PROCEDURE condsp2 (
           OUT OParam3 INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    4.       DECLARE EXIT HANDLER 
    5.           FOR divide_by_zero, divide_by_zero 
    6.               SET OParam3 = 0; 
    7.    …
    8.   END cs1;
  • A handler declaration cannot specify both a condition name and the SQLSTATE value associated with that condition name. A procedure defined in this way aborts and returns an error to the requestor during its compilation.

    The following example illustrates the non-valid specification of a condition name and its associated SQLSTATE value in the same handler. In this example, the handler at line 4 is defined to handle both the condition name divide_by_zero and the SQLSTATE value '22012' associated with that condition name.

    1.   CREATE PROCEDURE condsp2 (
           OUT OParam3 INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    4.       DECLARE EXIT HANDLER 
    5.           FOR divide_by_zero, SQLSTATE '22012' 
    6.               SET OParam3 = 0; 
    7.    …
    8.   END cs1;
  • If a handler declaration is defined to handle a condition name, no other handler declaration in the same compound statement can define the SQLSTATE associated with that condition name. A procedure defined in this way aborts and returns an error to the requestor during its compilation.

    The following example illustrates the non-valid specification of a condition name and its associated SQLSTATE value in different handlers. In this example, the handler at line 4 is defined to handle divide_by_zero and the handler at line 7 is defined for SQLSTATE '22012', which is the SQLSTATE value associated with divide_by_zero.

    1.   CREATE PROCEDURE condsp2 (
           OUT OParam3 INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    4.       DECLARE EXIT HANDLER 
    5.           FOR divide_by_zero 
    6.               SET OParam3 = 0; 
    7.       DECLARE EXIT HANDLER 
    8.           FOR SQLSTATE '22012' 
    9.               SET OParam3 = 1; 
    10.   …
    11.  END cs1;
  • You cannot specify multiple handler declarations that have the same condition name in the same compound statement. A procedure defined in this way aborts and returns an error to the requestor during its compilation.

    The following example illustrates non-valid specification of the same condition name in multiple handlers. In this example, the handlers at lines 4 and 7 are defined to handle the same condition name, divide_by_zero.

    1.   CREATE PROCEDURE condsp1 (
           OUT OParam3 INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    4.       DECLARE EXIT HANDLER 
    5.           FOR divide_by_zero 
    6.               SET OParam3 = 0; 
    7.       DECLARE EXIT HANDLER 
    8.           FOR divide_by_zero 
    9.               SET OParam3 = 1; 
    10.   …
    11.  END cs1;
  • If you declare a handler to handle a condition name that has an SQLSTATE value associated with it, that handler is also associated with that SQLSTATE value.

    The following example illustrates how to specify a condition name and its associated SQLSTATE value in a handler. In this example, the condition declaration at line 3 defines condition name divide_by_zero and associates it with SQLSTATE '22012'. The EXIT handler at line 4 is defined to handle divide_by_zero. While the procedure executes, the divide by zero exception with SQLSTATE '22012' is raised at line 8 and is then handled by the EXIT handler defined to handle divide_by_zero. After the successful completion of the EXIT handler statements, compound statement cs1 yields its control of the procedure, which then completes successfully.

    1.   CREATE PROCEDURE condsp1 (
           INOUT IOParam2 INTEGER,
           OUT   OParam3  INTEGER)
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; 
    4.       DECLARE EXIT HANDLER 
    5.           FOR divide_by_zero, SQLSTATE '42000' 
    6.               SET OParam3 = 0; 
    7.       SET IOParam2=0;
    8.       SET OParam3 = 20/IOParam2;     /* raises exception 22012 */
    9.   END cs1;
  • The handler action is associated with every condition name defined in the condition multivalue of a handler declaration.

    The following example illustrates the association of the same handler action with multiple condition names. In this example, a CONTINUE handler is defined at line 5 for condition names divide_by_zero and table_does_not_exist. While the procedure executes, the CONTINUE handler at line 5 can handle exceptions SQLSTATE '22012' (SQLCODE 2802) and SQLSTATE '42000' (SQLCODE 3807) raised by lines 9 and 10, respectively.

    1.   CREATE PROCEDURE condsp1 (
           INOUT IOParam2 INTEGER,
           OUT   OParam3  CHARACTER(30))
    2.   cs1: BEGIN
    3.       DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    4.       DECLARE table_does_not_exist CONDITION FOR SQLSTATE '42000';
    5.       DECLARE CONTINUE HANDLER 
    6.           FOR divide_by_zero, table_does_not_exist 
    7.               SET OParam3 = 0; 
    8.       SET IOParam2=0;
    9.       SET OParam3 = 20/IOParam2;         /*raises exception 22012*/
    10.      INSERT notab VALUES (IOParam2+20);/*raises exception 42000*/
    11. END Cs1;
    BTEQ> DROP TABLE notab;
    BTEQ> CALL condsp1(IOParam2, OParam3);