Condition Handlers

A condition handler is basically an SQL statement that is executed when a specified condition is encountered by a statement within the procedure body. The handler is declared within a compound statement, and its scope is limited to the compound statement in which it is declared. The body of an SQL procedure is always enclosed within a BEGIN and END block and hence, by definition, an SQL procedure is a compound statement. The scope for a handler declared at the start of a procedure is the entire SQL procedure.

You must include two things when declaring a condition handler:

• You need to determine under which condition the handler will be invoked.

• You need to decide which type of handler to declare.

The type of handler is used to indicate where control is passed after the handler completes execution.

DB2 SQL PL provides some general conditions: SQLEXCEPTION, SQLWARNING, and NOT FOUND. SQLEXCEPTION covers all conditions where the execution of an SQL procedure body statement results in an error, represented by an SQLSTATE value whose first two characters are not 00, 01, or 02, or an SQLCODE whose value is a negative integer. SQLWARNING and NOT FOUND will be discussed later in this section.

You can declare EXIT and CONTINUE handlers. In LUW and iSeries, UNDO handlers are also supported. An EXIT handler will execute the SQL statement in the handler, and continue execution at the end of the compound statement in which it was declared. Thus, an EXIT handler declared at the start of a procedure would EXIT the procedure upon completion. In contrast, a CONTINUE handler will continue execution at the statement following the one that raised the exception.

UNDO handlers (supported in LUW and iSeries only) are similar to EXIT handlers in that execution will continue at the end of the compound statement in which it was declared. Any statements that were successfully executed within the compound statement will be rolled back with an UNDO handler, however. UNDO handlers can only be declared within ATOMIC compound statements.

Figure 6.2 shows the syntax diagram for declaring handlers.

Figure 6.2. An abbreviated syntax diagram for condition handler declaration.

 ---DECLARE----+-CONTINUE-+---HANDLER--FOR-----condition-------->               +-EXIT-----+               '-UNDO-----' >----SQL-procedure-statement------------------------------------| 

You can see from the syntax diagram that the handler declaration is very simple. The variable condition refers to a specific condition described using a keyword for a general condition, an SQLSTATE, or a previously declared condition. The examples in the next few sections will explain handler declarations in detail.

Note that within compound statements, a condition handler declaration follows all other types of declarations, such as variable declarations and cursor declarations.

Tip

In LUW and iSeries, if you require multiple statements to be executed within a condition handler, use a compound statement (BEGIN and END block). To execute multiple statements within a condition handler on zSeries, refer to the examples presented in the section "DB2 UDB for zSeries Considerations" of this chapter.

Figure 6.3 expands on the example presented earlier and shows how it can be corrected using a condition handler.

Figure 6.3. An example of simple error checking.

 CREATE PROCEDURE simple_error2 ( IN p_midinit    CHAR                                 ,IN p_empno      CHAR(6) )     LANGUAGE SQL     SPECIFIC simple_error2                         -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                          -- applies to zSeries se: BEGIN    -- Declare variables    DECLARE v_sqlstate_test CHAR(5);                              -- (1)    DECLARE v_sqlcode_test INT;                                   -- (2)    -- Declare return codes    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';    DECLARE SQLCODE INT DEFAULT 0;    -- Declare condition handlers    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION                     -- (3)         SELECT SQLSTATE               ,SQLCODE           INTO v_sqlstate_test               ,v_sqlcode_test           FROM sysibm.sysdummy1;    -- Procedure logic    UPDATE employee                                               -- (4)       SET midinit = p_midinit      WHERE empno = p_empno;     IF SUBSTR(v_sqlstate_test,1,2) NOT IN ('00','01','02') THEN  -- (5)         . . . ;     END IF; END se 

As you can see, two variables, v_sqlstate_test and v_sqlcode_test, have been added on Lines (1) and (2), as well as a CONTINUE handler on Line (3). The handler sets the values of SQLCODE and SQLSTATE raised by the error condition to the variables v_sqlcode_test and v_sqlstate_test. The other change made was the test condition on Line (5) to test for the variable set in the handler as opposed to the actual SQLSTATE.

Note that SQLCODE and SQLSTATE from the statement that raised the condition are only accessible in the first statement of the handler. That is the reason why a SELECT INTO statement is used to capture both simultaneously so that both variables (v_sqlcode_test and v_sqlstate_test) can be assigned properly. The table, sysibm.sysdummy1, referenced in the FROM clause is a DB2 special built-in catalog view that contains one row only.

The previous example works, but it seems like a very cumbersome way to perform any error checking. It forces you to check for an error after each and every execution of a statement. In fact, it was only presented for demonstration purposes. Creating a handler just to set SQLCODE and SQLSTATE defeats its intended purpose. The real value in creating handlers is to group errors into classes for which similar action can be taken.

To elaborate, if you were to rethink this example, what you would really want to do is to return SQLCODE and SQLSTATE as output parameters to the calling application. This can be accomplished by writing an EXIT handler instead of a CONTINUE handler. Figure 6.4 shows the previous example rewritten with a more appropriate use of the condition handler.

Figure 6.4. A revised example of simple error checking.

 CREATE PROCEDURE simple_error3 ( IN p_midinit       CHAR                                 ,IN p_empno         CHAR(6)                                 ,OUT p_sqlstate_out CHAR(5)                                 ,OUT p_sqlcode_out  INT )      LANGUAGE SQL      SPECIFIC simple_error3                     -- applies to LUW and iSeries   -- WLM ENVIRONMENT <env>                      -- applies to zSeries se: BEGIN    -- Declare return codes    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';    DECLARE SQLCODE INT DEFAULT 0;    -- Declare condition handlers    DECLARE EXIT HANDLER FOR SQLEXCEPTION         SELECT SQLSTATE               ,SQLCODE           INTO p_sqlstate_out               ,p_sqlcode_out           FROM sysibm.sysdummy1;    -- Procedure logic    -- Initialize output parameters with defaults    VALUES (SQLSTATE, SQLCODE)      INTO p_sqlstate_out          ,p_sqlcode_out;      UPDATE employee                            -- (1)         SET midinit = p_midinit       WHERE empno = p_empno; END se 

As soon as an error is encountered, the handler is invoked. It sets the values for the output parameters p_sqlcode and p_sqlstate. After the handler completes, the procedure exits. Because the procedure terminates on error, there is no need to check for an error condition after the UPDATE statement on Line (1).

In addition to SQLEXCEPTION, two other general conditions exist for handlersSQLWARNING and NOT FOUND. SQLWARNING covers all conditions where the execution of an SQL statement completed successfully, but a warning was issuedthat is, an SQLSTATE value whose first two characters are 01, or an SQLCODE value that is a positive integer, not including +100. The NOT FOUND condition represents the case where the execution of an SQL statement returns an SQLSTATE value whose first two characters are 02, or an SQLCODE value of +100.

It is also possible to declare handlers for specific conditions, rather than one of the general conditions, by declaring a handler for a specific SQLSTATE. This can be done in two ways. One is through the declaration of the condition handler, as in

 DECLARE EXIT HANDLER FOR SQLSTATE '23503' ...; 

Although this statement will work, it does not make the code in the procedure very readable or intuitive. The SQL PL allows you to declare condition names for specific SQLSTATES to make the code clearer. Consider the following code snippet:

 DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE '23503'; DECLARE EXIT HANDLER FOR FOREIGN_KEY_VIOLATION ...; 

This makes your code more readable and easier to understand. Following are two syntax diagrams, one for the condition declaration (see Figure 6.5), and the other for the more complete handler declaration (see Figure 6.6).

Figure 6.5. A syntax diagram for condition declaration.

 |---DECLARE--condition-name--CONDITION--FOR--------------------->                  .-VALUE-.      .-SQLSTATE--+-------+---. >----+-----------------------+---string-constant----------------| 

Figure 6.6. A complete syntax diagram for condition handler declaration.

 ---DECLARE----+-CONTINUE-+---HANDLER--FOR---------------------->               +-EXIT-----+               '-UNDO-----'       .-,-----------------------------------.       V               .-VALUE-.             | >---------+-SQLSTATE--+-------+--string--+--+------------------->           +-condition-name---------------+           +-SQLEXCEPTION-----------------+           +-SQLWARNING-------------------+           '-NOT FOUND--------------------' >----SQL-procedure-statement------------------------------------| 

For any given procedure, you can declare multiple handlers for different conditions. For example, you can declare an EXIT handler for SQLEXCEPTION and another EXIT handler for SQLSTATE 23503. This can introduce some ambiguity because SQLSTATE 23503 is also an SQLEXCEPTION. In cases where more than one condition handler might fit the bill, the handler for the more specific condition is invoked. So in an example where handlers for both SQLEXCEPTION and SQLSTATE 23503 are declared, a statement that results in a foreign key violation would invoke the condition handler declared for SQLSTATE 23503, and the SQLEXCEPTION condition handler is ignored.

Several concepts have been introduced in the last section. It's time now to look at a more comprehensive example, with multiple handlers, to bring some of these ideas together.

The example in Figure 6.7 is a procedure that inserts into the department table. If the insert fails because of a duplicate row error, then an update is performed. It takes the column values for department as input and returns SQLCODE and SQLSTATE as output parameters.

Figure 6.7. An example of a named condition and multiple handlers.

 CREATE PROCEDURE insert_update_department1 ( IN p_deptno        CHAR(3)                                             ,IN p_deptname      VARCHAR(29)                                             ,IN p_mgrno         CHAR(6)                                             ,IN p_admrdept      CHAR(3)                                             ,IN p_location      CHAR(16)                                             ,OUT p_sqlstate_out CHAR(5)                                             ,OUT p_sqlcode_out  INT )     LANGUAGE SQL     SPECIFIC ins_upd_dept1                        -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries iud: BEGIN     -- Declare variables     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';     DECLARE SQLCODE INT DEFAULT 0;     DECLARE v_duplicate INT DEFAULT 0;     -- Declare condition     DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';               -- (1)     -- Declare handlers     DECLARE EXIT HANDLER FOR SQLEXCEPTION         SELECT SQLSTATE                                               -- (2)               ,SQLCODE           INTO p_sqlstate_out               ,p_sqlcode_out           FROM sysibm.sysdummy1;     -- Handler for duplicate condition     DECLARE CONTINUE HANDLER FOR c_duplicate                          -- (3)         SET v_duplicate = 1;     -- Initialize output parms     VALUES (SQLSTATE, SQLCODE)                                        -- (4)       INTO p_sqlstate_out           ,p_sqlcode_out;     -- Try insert, if duplicate, then update     INSERT INTO department ( deptno                             ,deptname                             ,mgrno                             ,admrdept                             ,location )                      VALUES ( p_deptno                              ,p_deptname                              ,p_mgrno                              ,p_admrdept                              ,p_location );      IF v_duplicate = 1 THEN          -- only update if non-null value provided as input parameter          UPDATE department             SET deptname = coalesce(p_deptname, deptname)                ,mgrno    = coalesce(p_mgrno, mgrno)                ,admrdept = coalesce(p_admrdept, admrdept)                ,location = coalesce(p_location, location)          WHERE deptno = p_deptno;     END IF; END iud 

A condition is declared on Line (1) for SQLSTATE 23505, which is the SQLSTATE for duplicate rows. A handler is then declared on Line (3) for the specific condition that sets the variable indicating a duplicate condition.

Take a look at the VALUES statement on Line (4). This is an alternative to issuing two SET statements or a statement such as SELECT ... FROM sysibm.sysdummy1 as seen on Line (2). The VALUES INTO statement is more efficient than two SET statements because the variables are assigned in parallel. Additionally, each statement has some overhead associated with itfor example, condition checking at the end of each statement. Therefore, reducing the number of statements also reduces the processing cost.