Controlling how errors are handled within different scopes in an SQL procedure
You can use nested compound statements in an SQL procedure to specify that errors be handled differently within different scopes. You can also ensure that condition handlers are checked only with a particular compound statement.
Procedure
To control how errors are handled within different scopes in an SQL procedure:
Examples
- Example
- In the following example, a condition with the name ABC is declared twice, and a condition named XYZ is declared once.
The following notes refer to the preceding example:CREATE PROCEDURE... DECLARE ABC CONDITION... DECLARE XYZ CONDITION... BEGIN DECLARE ABC CONDITION... SIGNAL ABC; 1 END; SIGNAL ABC; 2- ABC refers to the condition that is declared in the innermost block. If this statement were changed to SIGNAL XYZ, XYZ would refer to the XYZ condition that is declared in the outermost block.
- ABC refers to the condition that is declared in the outermost block.
- Example
- The following example contains multiple declarations of a condition with the name FOO, and a single declaration of a condition with the name GORP.
CREATE PROCEDURE MYTEST (INOUT A CHAR(1), INOUT B CHAR(1)) L1: BEGIN DECLARE GORP CONDITION FOR SQLSTATE '33333'; -- defines a condition with the name GORP for SQLSTATE 33333 DECLARE EXIT HANDLER FOR GORP --defines a condition handler for SQLSTATE 33333 L2: BEGIN DECLARE FOO CONDITION FOR SQLSTATE '12345'; --defines a condition with the name FOO for SQLSTATE 12345 DECLARE CONTINUE HANDLER FOR FOO --defines a condition handler for SQLSTATE 12345 L3: BEGIN SET A = 'A'; ...more statements... END L3; SET B = 'B'; IF... SIGNAL FOO; --raises SQLSTATE 12345 ELSEIF SIGNAL GORP; --raises SQLSTATE 33333 END IF; END L2; L4: BEGIN DECLARE FOO CONDITION FOR SQLSTATE '54321' --defines a condition with the name FOO for SQLSTATE 54321 DECLARE EXIT HANDLER FOR FOO...; --defines a condition handler for SQLSTATE 54321 SIGNAL FOO SET MESSAGE_TEXT = '...'; --raises SQLSTATE 54321 L5: BEGIN DECLARE FOO CONDITION FOR SQLSTATE '99999'; --defines a condition with the name FOO for SQLSTATE 99999 ...more statements... END L5; END L4; --At this point, the procedure cannot reference FOO, because this condition is not defined --in this outer scope END L1 - Example
- In the following example, the compound statement with the label OUTER contains two other compound statements: INNER1A and INNER1B. The INNER1A compound statement contains another compound statement, which has the label INNER1A2, and the declaration for a condition handler HINNER1A. The body of the condition handler HINNER1A contains another compound statement, which defines another condition handler, HINNER1A_HANDLER.
The following notes apply to the preceding example:OUTER: BEGIN <=============. -- Handler for OUTER | DECLARE ... HANDLER -- HOUTER | BEGIN <---. | : | | END; -- End of handler <---. | : | : | | -- Level 1 - first compound statement | INNER1A: | BEGIN <---------. | -- Handler for INNER1A | | DECLARE ... HANDLER -- HINNER1A | | BEGIN <------. | | -- Handler for handler HINNER1A | | DECLARE...HANDLER --HINNER1A_HANDLER| | | BEGIN <---. | | | : | | | | END; -- End of handler <---. | | | : | | | : -- stmt that gets condition | | | 2 : | | | : -- more statements in handler | | | END; -- End of HINNER1A handler<------. | | | | INNER1A2: | | BEGIN <--. | | DECLARE ... HANDLER...-- HINNER1A2 | | | BEGIN; <---. | | | : | | | | END; -- End of handler <---. | | | : | | | : -- statement that gets condition | | | 1 : | | | : -- statement after statement | | | : -- that encountered condition | | | END INNER1A2; <--' | | : | | : -- statements in INNER1A | | END INNER1A; <---------' | | -- Level 1 - second compound statement | INNER1B: | BEGIN <---------. | -- Handler for handler INNER1B | | DECLARE ...HANDLER -- HINNER1B | | BEGIN <------. | | -- Handler for HINNER1B -- | | | DECLARE ...HANDLER --HINNER1B_HANDLER| | | BEGIN <---. | | | : | | | | END; -- End of handler <---. | | | : | | | : -- statements in handler | | | END; -- End of HINNER1B handler<-------. | | : | | : -- statements in INNER1B | | END INNER1B; <---------' | | : -- statements in OUTER | END OUTER; <============='- If an exception, warning, or NOT FOUND condition occurs within the INNER1A2 compound statement, the most appropriate handler within that compound statement is activated to handle the condition. Then, one of the following actions occurs depending on the type of condition handler:
- If the condition handler (HINNER1A2) is an exit handler, control is returned to the end of the compound statement that contained the condition handler.
- If the condition handler (HINNER1A2) is a continue handler, processing continues with the statement after the statement that encountered the condition.
- The most appropriate handler within the INNER1A compound statement.
- The most appropriate handler within the OUTER compound statement.
- If an exception, warning, or NOT FOUND condition occurs within the body of the condition handler HINNER1A, and the condition handler HINNER1A_HANDLER is the most appropriate handler for the exception, that handler is activated. Otherwise, the most appropriate handler within the OUTER compound statement handles the condition. If no appropriate handler exists within the OUTER compound statement, the condition is treated as an unhandled condition.
- If an exception, warning, or NOT FOUND condition occurs within the INNER1A2 compound statement, the most appropriate handler within that compound statement is activated to handle the condition. Then, one of the following actions occurs depending on the type of condition handler:
- Example
- In the following example, when statement2 results in a NOT FOUND condition, the appropriate condition handler is activated to handle the condition. When the condition handler completes, the compound statement that contains that condition handler terminates, because the condition handler is an EXIT handler. Processing then continues with statement4.
BEGIN DECLARE EXIT HANDLER FOR NOT FOUND SET OUT_OF_DATA_FLAG = ON; statement1... statement2... --assume that this statement results in a NOT FOUND condition statement3... END; statement4 ... - Example
- In the following example, Db2 checks for SQLSTATE 22H11 only for statements inside the INNER compound statement. Db2 checks for SQLEXCEPTION for all statements in both the OUTER and INNER blocks.
OUTER: BEGIN DECLARE var1 INT; DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -3; INNER: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '22H11' RETURN -1; DECLARE C1 CURSOR FOR SELECT col1 FROM table1; OPEN C1; CLOSE C1; : : -- more statements END INNER; : : -- more statements - Example
- In the following example, Db2 checks for SQLSTATE 42704 only for statements inside the A compound statement.
The following notes describe a possible flow for the preceding example:CREATE PROCEDURE EXIT_TEST () LANGUAGE SQL BEGIN DECLARE OUT_BUFFER VARCHAR(80); DECLARE NO_TABLE CONDITION FOR SQLSTATE '42704'; A: BEGIN 1 DECLARE EXIT HANDLER FOR NO_TABLE 3 BEGIN SET OUT_BUFFER ='Table does not exist'; 4 END; -- Drop potentially nonexistent table: DROP TABLE JAVELIN; 2 B: SET OUT_BUFFER ='Table dropped successfully'; END; -- Copy OUT_BUFFER to some message table: C: INSERT INTO MESSAGES VALUES (OUT_BUFFER); 5- A nested compound statement with label A confines the scope of the NO_TABLE exit handler to the statements that are specified in the A compound statement.
- If the table JAVELIN does not exist, the DROP statement raises the NO_TABLE condition.
- The exit handler for NO_TABLE is activated.
- The variable OUT_BUFFER is set to the string 'Table does not exist.'
- Execution continues with the INSERT statement. No more statements in the A compound statement are processed.
- Example
-
The following example illustrates the scope of different condition handlers.
The following table summarizes the behavior of the preceding example:CREATE PROCEDURE ERROR_HANDLERS(IN PARAM INTEGER) LANGUAGE SQL OUTER: BEGIN DECLARE I INTEGER; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE EXIT HANDLER FOR SQLSTATE VALUE '38H02', SQLSTATE VALUE '38H04', SQLSTATE VALUE '38HI4', SQLSTATE VALUE '38H06' OUTER_HANDLER: BEGIN DECLARE TEXT VARCHAR(70); SET TEXT = SQLSTATE || ' RECEIVED AND MANAGED BY OUTER ERROR HANDLER' ; RESIGNAL SQLSTATE VALUE '38HE0' SET MESSAGE_TEXT = TEXT; END OUTER_HANDLER; INNER: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE VALUE '38H03' RESIGNAL SQLSTATE VALUE '38HI3' SET MESSAGE_TEXT = '38H03 MANAGED BY INNER ERROR HANDLER'; DECLARE EXIT HANDLER FOR SQLSTATE VALUE '38H04' RESIGNAL SQLSTATE VALUE '38HI4' SET MESSAGE_TEXT = '38H04 MANAGED BY INNER ERROR HANDLER'; DECLARE EXIT HANDLER FOR SQLSTATE VALUE '38H05' RESIGNAL SQLSTATE VALUE '38HI5' SET MESSAGE_TEXT = '38H05 MANAGED BY INNER ERROR HANDLER'; CASE PARAM WHEN 1 THEN -- (1) SIGNAL SQLSTATE VALUE '38H01' SET MESSAGE_TEXT = 'EXAMPLE 1: ERROR SIGNALED FROM INNER COMPOUND STMT'; WHEN 2 THEN -- (2) SIGNAL SQLSTATE VALUE '38H02' SET MESSAGE_TEXT = 'EXAMPLE 2: ERROR SIGNALED FROM INNER COMPOUND STMT'; WHEN 3 THEN -- (3) SIGNAL SQLSTATE VALUE '38H03' SET MESSAGE_TEXT = 'EXAMPLE 3: ERROR SIGNALED FROM INNER COMPOUND STMT'; WHEN 4 THEN -- (4) SIGNAL SQLSTATE VALUE '38H04' SET MESSAGE_TEXT = 'EXAMPLE 4: ERROR SIGNALED FROM INNER COMPOUND STMT'; ELSE SET I = 1; /*Do not do anything */ END CASE; END INNER; CASE PARAM WHEN 5 THEN -- (5) SIGNAL SQLSTATE VALUE '38H05' SET MESSAGE_TEXT = 'EXAMPLE 5: ERROR SIGNALED FROM OUTER COMPOUND STMT'; WHEN 6 THEN -- (6) SIGNAL SQLSTATE VALUE '38H06' SET MESSAGE_TEXT = 'EXAMPLE 6: ERROR SIGNALED FROM OUTER COMPOUND STMT'; ELSE -- (7) SET I = 1; /*Do not do anything */ END CASE; END OUTERInput value for PARM Expected behavior 1 SQLSTATE 38H01 is signaled from the INNER compound statement. Because no appropriate handler exists, the procedure terminates and returns the unhandled exception condition, 38H01 with SQLCODE -438, to the calling application. 2 SQLSTATE 38H02 is signaled from the INNER compound statement. The condition handler in the OUTER compound statement is activated. A RESIGNAL statement, with SQLSTATE 38HE0, is issued from within the body of the condition handler. This exception causes control to be returned to the end of the OUTER compound statement with exception condition 38HE0 and SQLCODE -438. The procedure terminates and returns the unhandled condition to the calling application. 3 SQLSTATE 38H03 is signaled from the INNER compound statement. A condition handler within the INNER compound statement is activated. A RESIGNAL statement, with SQLSTATE 38HI3, is issued from within the body of the condition handler. Because no appropriate handler exists, the procedure terminates and returns the unhandled exception condition, 38HI3 with SQLCODE -438, to the calling application. 4 SQLSTATE 38H04 is signaled from the INNER compound statement. A condition handler within the INNER compound statement is activated. A RESIGNAL statement, with SQLSTATE 38HI4, is issued from within the body of the condition handler. A condition handler in the OUTER compound statement is activated. A RESIGNAL statement, with SQLSTATE 38HE0, is issued from within the body of the condition handler. This exception causes control to be returned to the end of the OUTER compound statement with exception condition 38HE0 and SQLCODE -438. The procedure terminates and returns the unhandled condition to the calling application. 5 SQLSTATE 38H05 is signaled from the OUTER compound statement. Because no appropriate handler exists, the procedure terminates and returns the unhandled exception condition, 38H05 with SQLCODE -438, to the calling application. 6 SQLSTATE 38H06 is signaled from the OUTER compound statement. A condition handler in the OUTER compound statement is activated. A RESIGNAL statement, with SQLSTATE 38HE0, is issued from within the body of the condition handler. This exception causes control to be returned to the end of the OUTER compound statement with exception condition 38HE0 and SQLCODE -438. The procedure terminates and returns the unhandled condition to the calling application. 7 The ELSE clause of the CASE statement executes and processes the SET statement. A successful completion code is returned to the calling application. - Example
- In the following example SQL procedure, the condition handler for exception1 is not within the scope of the condition handler for exception0. If exception condition exception1 is raised in the body of the condition handler for exception0, no appropriate handler exists, and the procedure terminates with an unhandled exception.
CREATE PROCEDURE divide ( .....) LANGUAGE SQL CONTAINS SQL BEGIN DECLARE dn_too_long CHAR(5) DEFAULT 'abcde'; -- Declare condition names -------------------------- DECLARE exception0 CONDITION FOR SQLSTATE '22001'; DECLARE exception1 CONDITION FOR SQLSTATE 'xxxxx'; -- Declare cursors ---------------------------------- DECLARE cursor1 CURSOR WITH RETURN FOR SELECT * FROM dept; -- Declare handlers --------------------------------- DECLARE CONTINUE HANDLER FOR exception0 BEGIN some SQL statement that causes an error 'xxxxx' END DECLARE CONTINUE HANDLER FOR exception1 BEGIN ... END -- Mainline of procedure ---------------------------- INSERT INTO DEPT (DEPTNO) VALUES (dn_too_long); -- Assume that this statement results in SQLSTATE '22001' OPEN CURSOR1; END