15.00 - Special Condition Handling for SIGNAL and RESIGNAL Statements - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Special Condition Handling for SIGNAL and RESIGNAL Statements

The condition names defined in a condition declaration can be specified in a condition handler declaration for 2 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);