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