Special Condition Handling for SIGNAL & RESIGNAL Statements | Teradata Vantage - Special Condition Handling for SIGNAL and RESIGNAL Statements - Advanced SQL Engine

Special Condition Handling for SIGNAL & RESIGNAL Statements | Teradata Vantage - Special Condition Handling for SIGNAL and RESIGNAL Statements - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

July 2021

Language

English (United States)

Last Update

2021-07-27

dita:mapPath

imq1591724555718.ditamap

dita:ditavalPath

imq1591724555718.ditaval

dita:id

B035-1184

lifecycle

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);