The previous example illustrated a NOT ATOMIC compound statement and is the default type used in SQL procedures. If an unhandled error (that is, no condition handler has been declared for the SQLSTATE raised) occurs within the compound statement, any work that is completed before the error will not be rolled back, but will not be committed either. The group of statements can only be rolled back if the unit of work is explicitly rolled back using ROLLBACK or ROLLBACK TO SAVEPOINT. You can also COMMIT successful statements if it makes sense to do so.

The syntax for a NOT ATOMIC compound statement is shown in Figure 4.2.

                         .-NOT ATOMIC--.
>>-+---------+--BEGIN----+-------------+------------------------>
   '-label:--'
...
Misc. Statements;
...

>-------------------------------------+---END--+--------+------><
                                               '-label--'

The optional label is used to define a name for the code block. The label can be used to qualify SQL variables declared within the compound statement. If the ending label is used, it must be the same as the beginning label. You will learn more about labels in section, “Using Labels,” in this chapter.

The SQL procedure illustrated in Figure 4.3 demonstrates the non-atomicity of NOT ATOMIC compound statements.

CREATE PROCEDURE not_atomic_proc ()
    LANGUAGE SQL
    SPECIFIC not_atomic_proc                          -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                              -- applies to zSeries
nap: BEGIN NOT ATOMIC
   -- Procedure logic
   INSERT INTO cl_sched (class_code, day)
      VALUES ('R11:TAA', 1);
   SIGNAL SQLSTATE '70000';                                  -- (1)
   INSERT INTO cl_sched (class_code, day)
      VALUES ('R22:TBB', 2);
END nap

Although the SIGNAL statement on Line (1) has not been introduced, it is sufficient to understand that the statement is used to explicitly raise an error. Additionally, because this error is unhandled, the procedure will exit right after the error. More information can be found in Chapter 6, “Condition Handling”.

After calling this procedure, you will see that although an error has been raised halfway through execution, the first INSERT successfully inserted a row into the atomic_test table. You need to realize, however, that the procedure itself does not issue either COMMIT or ROLLBACK implicitly.

ATOMIC compound statements are supported in DB2 for LUW and iSeries only. In DB2 zSeries, the NOT ATOMIC compound statement is the default and the only type used in SQL procedures.

The ATOMIC compound statement, as the name suggests, can be thought of as a singular whole. If any unhandled error conditions arise within it, all statements that have executed up to that point are considered to have failed as well and are therefore rolled back.

On LUW, ATOMIC compound statements cannot be nested inside other ATOMIC compound statements. iSeries does not have this restriction.

In addition, you cannot use SAVEPOINTs or issue explicit COMMITs or ROLLBACKs from within an ATOMIC compound statement.

The syntax to declare an ATOMIC compound statement is shown in Figure 4.4.

>>-+---------+--BEGIN ATOMIC------------------------------------>
   '-label:--'
...
Misc. Statements;
...
>-------------------------------------+---END--+--------+------><
                                               '-label--'

A label is used in the same way as with a NOT ATOMIC compound statement.

The example in Figure 4.5 illustrates the behavior of an ATOMIC compound statement. It is quite similar to the NOT ATOMIC example shown in Figure 4.4, and only differs in name and in the fact that it uses an ATOMIC compound statement.

CREATE PROCEDURE atomic_proc ()
    LANGUAGE SQL
    SPECIFIC atomic_proc                             -- applies to LUW and iSeries
ap: BEGIN ATOMIC
   -- Procedure logic
   INSERT INTO cl_sched (class_code, day)
      VALUES ('R33:TCC', 3);
   SIGNAL SQLSTATE '70000';                          -- (1)
   INSERT INTO cl_sched (class_code, day)
      VALUES ('R44:TDD', 4);
END ap

When the error condition of SQLSTATE 70000 is raised on Line (1), the unhandled error causes procedure execution to stop. Unlike the NOT ATOMIC example in Figure 4.3, the first INSERT statement will be rolled back, resulting in a table with no inserted rows from this procedure.

Labels can be used to name any executable statement, which includes compound statements and loops. By using labels, you can have the flow of execution either jump out of a compound statement or loop, or jump to the beginning of a compound statement or loop.

Optionally, you may supply a corresponding label for the END of a compound statement. If an ending label is supplied, it must be the same as the label used at its beginning.

Each label must be unique within the body of an SQL procedure.

A label can also be used to avoid ambiguity if a variable with the same name has been declared in more than one compound statement of the stored procedure. A label can be used to qualify the name of an SQL variable.

Figure 4.6 shows the use of a label to name a compound statement and also how to avoid ambiguous references to similarly named variables. It uses two variables of the same name (v_ID) defined in two differently labeled compound statements.

-- Example applies to LUW and iSeries only
--   because nested compound statements are used
CREATE PROCEDURE show_label (OUT p_WorkerID INT)
    LANGUAGE SQL
    SPECIFIC show_label                             -- applies to LUW and iSeries
sl1: BEGIN
   -- Declare variables
   DECLARE v_ID INT;                               -- (1)

   -- New compound statement
   sl2: BEGIN
       -- Declare variables
       DECLARE v_ID INT;                           -- (2)

       -- Procedure logic
       SET sl1.v_ID = 1;                           -- (3)
       SET sl2.v_ID = 2;                           -- (4)
       SET v_ID = 3;                               -- (5)
       SET p_WorkerID = sl2.v_ID;                  -- (6)
   END sl2;
END sl1

You can see that, to avoid ambiguity, the two v_ID variables defined in Lines (1) and (2) are qualified with the label of the compound statement in which they were defined at Lines (3), (4), and (6). When qualification is not used, as in Line (5), the variable will be qualified with the label of the compound statement from which it is being referenced. So the value assignment at Line (5) will actually assign a value of 3 to sl2.v_ID, which means that p_WorkerID in Line (6) will also be assigned a value of 3.

Additionally, the label of the compound statement or loop can be used with the LEAVE statement to exit the labeled compound statement or loop. Labels can also be used with the ITERATE statement to jump back to the labeled beginning of a LOOP. These SQL PL statements are covered in greater detail later on in this chapter.

The most commonly used approach for conditional execution is the IF statement. There are essentially three different types of IF statements.

The simplest form of the IF statement does something if a condition is true, and nothing otherwise.

But what happens if you want to do one thing if a condition is true and something else if it is false? This is where the ELSE clause comes in handy. When used in conjunction with an IF statement, you can do something IF a condition is true and something ELSE if the condition is false.

Thirdly, ELSEIF is used to branch to multiple code paths based on mutually exclusive conditions in the same manner as an IF statement. You can make use of an ELSEIF statement to rewrite a ladder of nested IF ... ELSE statements for readability. Your procedure can specify an unlimited number of ELSEIF statements.

The syntax of an IF statement is depicted in Figure 4.7.

>>-IF--search-condition-THEN----------------------------------->

      .------------------------------.
      V                              |
>--------SQL-procedure-statement-;---+------------------------->

      .------------------------------------------------------------------------
      V
|
>----+--------------------------------------------------------------------+--+>
     |                                 .-------------------------------.  |
     |                                 V                               |  |
     '-ELSEIF--search-condition--THEN-----SQL-procedure-statement--;---+--'

>-----+------------------------------------------+--END IF-----><
      |        .------------------------------.  |
      |        V                              |  |
      '-ELSE-----SQL-procedure-statement-;---+---'

The search-condition specifies the condition for which an SQL statement should be invoked. If the condition is false, processing continues to the next search-condition, until either a condition is true or processing reaches the ELSE clause.

SQL-procedure-statement specifies the statements to be invoked if the preceding search-condition is true. If no search-condition evaluates to true, then the SQL-procedure-statement following the ELSE keyword is invoked.

The snippet of an SQL procedure shown in Figure 4.8 demonstrates how the rating of an employee determines the raise in salary and bonus that he or she will receive.

CREATE PROCEDURE demo_if ( IN p_rating INT
                         , IN p_employee_number char(6) )
    LANGUAGE SQL
    SPECIFIC demo_if                              -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
di: BEGIN
   -- Procedure logic
   IF p_rating = 1 THEN                           -- (1)
      UPDATE employee
         SET salary = salary * 1.10, bonus = 1000
       WHERE empno = p_employee_number;
   ELSEIF p_rating = 2 THEN                               -- (2)
      UPDATE employee
         SET salary = salary * 1.05, bonus = 500
       WHERE empno = p_employee_number;
   ELSE                                                   -- (3)
      UPDATE employee
         SET salary = salary * 1.03, bonus = 0
       WHERE empno = p_employee_number;
   END IF;
END di

On Line (1), an employee with a 1 rating can expect a raise of 10 percent and a bonus of $1,000. On Line (2), an employee with a 2 rating earns a 5 percent raise with a $500 bonus. On Line (3), all other employees can expect a 3 percent pay hike with no bonus.

You are not simply limited to mathematical operators such as equals (=) and greater than (>). You can also use the SQL keywords NOT, AND, and OR to build conditions in your IF statements.

The CASE statement provides the ability to evaluate a list of options based on the value of a single variable. You would most likely choose to use a CASE statement if you have a large decision tree and all branches depend on the value of the same variable. Otherwise, you would be better off using a series of IF, ELSEIF, and ELSE statements. The syntax diagram for the CASE statement is shown in Figure 4.9.

>>-CASE----+-| searched-case-statement-when-clause |-+---------->
           '-| simple-case-statement-when-clause |---'

>----END CASE--------------------------------------------------><

simple-case-statement-when-clause

|---expression-------------------------------------------------->

      .-------------------------------------------------------------.
      |                          .-------------------------------.  |
      V                          V                               |  |
>--------WHEN--expression--THEN-----SQL-procedure-statement--;---+--+>

>-----+------------------------------------------+--------------|
      |        .------------------------------.  |
      |        V                              |  |
      '-ELSE-----SQL-procedure-statement--;---+--'

searched-case-statement-when-clause
    .-------------------------------------------------------------------.
    |                                .-------------------------------.  |
    V                                V                               |  |
|------WHEN--search-condition--THEN-----SQL-procedure-statement--;---+--+->

>----+------------------------------------------+---------------|
     |        .------------------------------.  |
     |        V                              |  |
     '-ELSE-----SQL-procedure-statement--;---+--'

The CASE statement has two general forms: one that uses a simple-case-statement-when-clause, and another that uses a searched-case-statement-when-clause.

In the simple-case-statement-when-clause, the expression prior to the first WHEN keyword is tested for equality with the value of each expression that follows the WHEN keyword. If the expression results in the same value, the SQL-procedure-statement following the THEN keyword is executed. Otherwise, comparisons are continued between the first expression and the expression following the next WHEN clause. If the result does not match any of the search conditions and an ELSE clause is present, the statements in the ELSE clause are processed.

In a searched-case-statement-when-clause, the search-condition following each WHEN keyword is evaluated. If search-condition evaluates to true, the statements in the associated THEN clause are processed. If it evaluates to false, the next search-condition is evaluated. If no search-condition evaluates to true and an ELSE clause is present, the statements in the ELSE clause are processed.

Both forms of the CASE statement require END CASE to denote the end of the statement.

The example that you have already seen in Figure 4.8 could be rewritten as shown in Figure 4.10 using the simple-case-statement-when-clause.

CREATE PROCEDURE demo_simple_case ( IN p_rating INT
                                  , IN p_employee_number char(6) )
    LANGUAGE SQL
    SPECIFIC demo_simple_case                     -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
dsc: BEGIN
   -- Procedure logic
   CASE p_rating
      WHEN 1 THEN                                        -- (1)
Note: WHEN argument is a value
         UPDATE EMPLOYEE
            SET SALARY = SALARY *1.10, BONUS = 1000
          WHERE EMPNO = p_employee_number;
      WHEN 2 THEN                                        -- (2)
         UPDATE EMPLOYEE
            SET SALARY = SALARY *1.05, BONUS = 500
          WHERE EMPNO = p_employee_number;
      ELSE
         UPDATE EMPLOYEE                                 -- (3)
            SET SALARY = SALARY *1.03, BONUS = 0
          WHERE EMPNO = p_employee_number;
   END CASE;
END dsc

Once again, on Line (1) an employee with a rating of 1 can expect a raise of 10 percent and a bonus of $1,000. On Line (2), an employee with a rating of 2 earns a 5 percent raise and a bonus of $500, while on Line (3), all other employees can simply expect a raise of 3 percent and no bonus.

Perhaps there have been some recent changes to the rating system, where there is now a wider range of ratings that employees can receive. Now, two employees with slightly different ratings can earn the same raise and bonus. Obviously, the code needs to be updated.

Figure 4.11 reflects the changes to the rating system and shows how to handle this using a searched-case-statement-when-clause. Note that the WHEN clause now contains a condition.

CREATE PROCEDURE demo_searched_case ( IN p_rating INT
                                    , IN p_employee_number char(6) )
    LANGUAGE SQL
    SPECIFIC demo_searched_case                   -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
dsc: BEGIN
   -- Procedure logic
   CASE
      WHEN p_rating >= 1 AND p_rating < 4 THEN           -- (1)
         UPDATE EMPLOYEE
            SET SALARY = SALARY *1.10, BONUS = 1000
          WHERE EMPNO = p_employee_number;
      WHEN p_rating >= 4 AND p_rating < 8 THEN
         UPDATE EMPLOYEE
            SET SALARY = SALARY *1.05, BONUS = 500
          WHERE EMPNO = p_employee_number;
      ELSE
         UPDATE EMPLOYEE
            SET SALARY = SALARY *1.03, BONUS = 0
          WHERE EMPNO = p_employee_number;
   END CASE;
END dsc

As you can see, the code now handles a range of ratings for each condition of the CASE statement. For example, on Line (1), an employee with a rating that falls between 1 and 3 inclusive will receive a raise of 10 percent and a bonus of $1,000.

The FOR loop statement is supported on DB2 for LUW and iSeries only. It is used to iterate over a read-only result set that is defined by its select-statement. Looping will cease when there are no rows left in the result set. You can easily use a WHILE loop to achieve the same result in DB2 for zSeries.

The syntax is depicted in Figure 4.12.

>>-+--------+--FOR--for-loop-name--AS--------------------------->
   '-label:-'

>--+----------------------------------------------+------------->
   |                                              |
   '-cursor-name--CURSOR--+-----------+--FOR------'
                          '-WITH HOLD-'

                         .----------------------------.
                         V                            |
>--select-statement--DO----SQL-procedure-statement--;-+--------->

>--END FOR--+-------+------------------------------------------><
            '-label-'

The for-loop-name specifies a label for the implicit compound statement generated to implement the FOR statement. It follows the rules for the label of a compound statement. The for-loop-name can used to qualify the column names in the result set as returned by the select-statement.

The cursor-name simply names the cursor that is used to select rows from the result set. If not specified, DB2 will automatically generate a unique cursor name internally.

The WITH HOLD option dictates the behavior of cursors across multiple units of work. It is described in detail in Chapter 5, “Understanding and Using Cursors and Result Sets.”

The column names of the select-statement must be unique, and a FROM clause specifying a table (or multiple tables if doing some kind of JOIN or UNION) is required. The table(s) and column(s) referenced must exist prior to the loop being executed. This allows you to iterate over result sets that are formed from tables which exist prior to invoking the stored procedure, or tables that have been created by a previous SQL PL statement (such as declared user-temporary tables, which are discussed in Chapter 10, “Leveraging DB2 Application Development Features”).

The FOR loop is essentially a CURSOR defined by the select-statement. This CURSOR cannot be referenced outside of the FOR loop, however, so OPEN, FETCH, and CLOSE statements will result in error.

In Figure 4.13, the FOR loop is used to iterate over all rows of the employee table (because no WHERE clause is being used). For each row, it generates a full name from the values out of three columns—last name, first name, and middle name initial.

-- Example applies to LUW and iSeries only
--   because of the usage of FOR loops.
CREATE PROCEDURE demo_for_loop ( )
    LANGUAGE SQL
    SPECIFIC demo_for_loop                          -- applies to LUW and iSeries
dfl: BEGIN
    -- Declare variables
    DECLARE v_fullname VARCHAR(50);

    -- Procedure logic
    FOR v_row AS SELECT firstnme, midinit, lastname                -- (1)
                   FROM employee
      DO
      SET v_fullname = v_row.lastname || ', ' ||
                       v_row.firstnme || ' ' || v_row.midinit;     -- (2)
      INSERT INTO tname VALUES (v_fullname);                       -- (3)
   END FOR;
END dfl

You can see the defining select-statement on Line (1) and where the columns of the result set are being concatenated together to form the v_fullname on Line (2). Finally, this newly formed v_fullname is inserted into a table called tname on Line (3).

To test the procedure in Figure 4.13, you need to have a table created as

CREATE TABLE tname ( fullname VARCHAR(50) )

The defining feature of a WHILE loop is that its looping condition is evaluated prior to initial loop execution and all following loop iterations. The WHILE loop will continue to execute until the looping condition evaluates to false.

When defining the looping condition, be sure to specify a full conditional statement (that includes operators). Otherwise, your SQL procedure will not build. For example,

WHILE (variable) DO
        statement1;
        statement2;
END WHILE;

is not enough. You need to use an operator, as in

WHILE (variable = 1) DO
        statement1;
        statement2;
END WHILE;

The syntax for the WHILE loop is illustrated in Figure 4.14.

>>-+---------+--WHILE--search-condition--DO--------------------->
   '-:label--'

     .-------------------------------.
     V                               |
>--------SQL-procedure-statement--;---+--END WHILE-------------->

>-----+--------+-----------------------------------------------><
      '-label--'

The search-condition specifies a condition that is evaluated before each execution of the loop. If the condition is true, the SQL-procedure-statements in the loop are processed.

Figure 4.15 illustrates how to use a WHILE loop to sum all integer values between n and m (which are assumed to be positive and provided by input parameters to the procedure).

CREATE PROCEDURE sum_mn ( IN p_start INT
                        , IN p_end INT
                        , OUT p_sum INT )
    LANGUAGE SQL
    SPECIFIC sum_mn                                -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                           -- applies to zSeries
smn: BEGIN
   DECLARE v_temp INTEGER DEFAULT 0;
   DECLARE v_current INTEGER;

   SET v_current = p_start;

   WHILE (v_current <= p_end) DO
       SET v_temp = v_temp + v_current;            -- (1)
       SET v_current = v_current + 1;
   END WHILE;

   SET p_sum = v_temp;
END smn

This example is fairly simple and is intended to show you how the WHILE loop works using as little code as possible. More commonly, however, a WHILE loop is used to repeatedly perform SQL procedure statements, such as FETCH (for retrieving row values from a cursor). For examples of using WHILE loops with cursor operations such as OPEN, FETCH and CLOSE, see Chapter 5, “Understanding and Using Cursors and Result Sets.”

In the WHILE loop, you saw that the looping condition is evaluated at the very beginning of the loop. If the looping condition evaluates to false at this first examination, then the loop body will not execute at all.

In some cases, however, it may be necessary that the loop be executed at least once. This is where the REPEAT loop is useful. A REPEAT loop ensures that at least one iteration of the loop is completed. This is the case because the looping condition is not evaluated until the final line of code in the loop.

The syntax for the REPEAT loop is shown in Figure 4.16.

                          .-------------------------------.
                          V                               |
>>-+---------+--REPEAT-------SQL-procedure-statement--;---+----->
   '-label:--'

>----UNTIL--search-condition---END REPEAT----+--------+--------><
                                             '-label--'

In Figure 4.17, the procedure in Figure 4.15 is re-implemented using REPEAT.

CREATE PROCEDURE sum_mn2 ( IN p_start INT
                         , IN p_end INT
                         , OUT p_sum INT )
    LANGUAGE SQL
    SPECIFIC sum_mn2                             -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                        -- applies to zSeries
smn2: BEGIN
   DECLARE v_temp INTEGER DEFAULT 0;
   DECLARE v_current INTEGER;

   SET v_current = p_start;

   REPEAT
      SET v_temp = v_temp + v_current;                  -- (1)
      SET v_current = v_current + 1;
   UNTIL (v_current > p_end)
   END REPEAT;

   SET p_sum = v_temp;
END smn2

If you execute both the WHILE loop example in Figure 4.15 and the REPEAT loop example in Figure 4.17 with p_start smaller than p_end, you will see the difference between the two. The WHILE loop example in Figure 4.15 generates p_sum with 0. The statement on Line (1) in Figure 4.15 is never executed because the condition in the WHILE clause is not met. The REPEAT loop example generates p_sum with the value of p_start instead because the statement on Line (1) in Figure 4.17 is always executed once.

The LOOP statement is somewhat different from the other types of loops that you have seen thus far. The LOOP does not have a terminating condition clause that is part of its declaration statement. It will continue to loop until some other piece of code inside it explicitly forces the flow of control to jump to some point outside of the loop.

LOOP will commonly have some logic that eventually branches to a LEAVE statement. You can also use a GOTO statement instead of a LEAVE, but the use of GOTO is discouraged.

The LOOP syntax is illustrated in Figure 4.18.

                        .-------------------------------.
                        V                               |
>>-+---------+--LOOP-------SQL-procedure-statement--;---+------->
   '-label:--'

>----END LOOP----+--------+------------------------------------><
                 '-label--'

An example of using LOOP is deferred until the discussion on LEAVE in the next section.

GOTO is a straightforward and basic flow of control statement that causes an unconditional change in the flow of control. It is used to branch to a specific user-defined location using labels defined in the procedure.

Usage of the GOTO statement is generally considered to be poor programming practice and is not recommended. Extensive use of GOTO tends to lead to unreadable code especially when procedures grow long. Besides, GOTO is not necessary because there are better statements available to control the execution path. There are no specific situations that require the use of GOTO; instead, it is more often used for convenience (or lack of effort).

The GOTO syntax is shown in Figure 4.19.

>>-GOTO--label-------------------------------------------------><

You should be aware of a few additional scope considerations:

Good programming practice should limit the use of the GOTO statement in your SQL procedure. The use of GOTO decreases the readability of your code since it causes execution to jump to a new line contained anywhere within the procedure body. This spaghetti code can be difficult to understand, debug, and maintain.

The GOTO statement is local to the SQL procedure which declares it. The label that a GOTO statement could jump to must be defined within the same SQL procedure as the GOTO statement, and don’t forget that scoping rules still apply.

In Figure 4.20, an SQL procedure is used to increase the salary of those employees who have been with the company for more than one year. The employee’s serial number and rating are passed into the stored procedure, which then returns an output parameter of the newly calculated salary. The employee’s salary is increased based on his or her rating.

CREATE PROCEDURE adjust_salary ( IN p_empno CHAR(6)
                               , IN p_rating INTEGER
                               , OUT p_adjusted_salary DECIMAL (8,2) )
    LANGUAGE SQL
    SPECIFIC adjust_salary                        -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
ads: BEGIN
   -- Declare variables
   DECLARE v_new_salary DECIMAL (9,2);
   DECLARE v_service DATE;
   -- Procedure logic
   SELECT salary, hiredate
     INTO v_new_salary, v_service
     FROM employee
    WHERE empno = p_empno;


    IF v_service > (CURRENT DATE - 1 year) THEN
       GOTO bye;                                                    -- (1)
    END IF;


    IF p_rating = 1 THEN
       SET v_new_salary = v_new_salary + (v_new_salary * .10);      -- (2)
    ELSEIF p_rating = 2 THEN
       SET v_new_salary = v_new_salary + (v_new_salary * .05);      -- (3)
    END IF;


    UPDATE employee                                                 -- (4)
       SET salary = v_new_salary
    WHERE empno = p_empno;

  bye:                                                              -- (5)
   SET p_adjusted_salary = v_new_salary;
END ads

This SQL procedure makes use of the GOTO statement on Line (1) to avoid increasing the salary of those employees who have not yet been with the company for more than a year.

To try the example on your own, use any valid employee number from the EMPNO column in the EMPLOYEE table as p_empno. Use either 1 or 2 for p_rating.

CALL adjust_salary('000010', 2, ?)

If the employee has worked for the company for more than a year, he or she is given a 5 or 10 percent raise if he or she received a rating of 2 or 1, respectively, on Lines (1) and (3). The EMPLOYEE table is updated to reflect the new salary on Line (4).

If it is discovered that the employee has not yet worked with the company for at least one year, the GOTO exit statement causes execution to jump to the second-last line of code on Line (5) in the procedure. The p_adjusted_salary is simply set to the original salary, and no changes are made to the EMPLOYEE table.

The LEAVE statement is used to transfer the flow of control out of a loop or compound statement. The syntax for the command, shown in Figure 4.21, is trivial.

>>-LEAVE--label------------------------------------------------><

Figure 4.22 illustrates how to use LOOP and LEAVE.

CREATE PROCEDURE verify_ids ( IN p_id_list VARCHAR(100)
                            , OUT p_status INT )
    LANGUAGE SQL
    SPECIFIC verify_ids                               -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                             -- applies to zSeries
vid: BEGIN
   DECLARE v_current_id VARCHAR(10);
   DECLARE v_position INT;
   DECLARE v_remaining_ids VARCHAR(100);
   DECLARE v_tmp INT;
   DECLARE SQLCODE INT DEFAULT 0;

   SET v_remaining_ids = p_id_list;
   SET p_status = 0;

   L1: LOOP
        SET v_position = LOCATE (':',v_remaining_ids);                     --(1)

        -- take off the first id from the list
        SET v_current_id = SUBSTR (v_remaining_ids, 1, v_position);        --(2)

        IF LENGTH(v_remaining_ids) - v_position > 0 THEN                   --(3)
                SET v_remaining_ids = SUBSTR (v_remaining_ids, v_position+1);
        ELSE
            SET v_remaining_ids = '';
        END IF;

        -- take off the colon in last position of the current token
        SET v_current_id = SUBSTR (v_current_id, 1, v_position-1);         --(4)

           -- determine if employee exists
        SELECT 1 INTO v_tmp FROM employee where empno = v_current_id;      --(5)



        IF (SQLCODE <> 0) THEN
            -- employee id does not exist
               SET p_status=-1;
                LEAVE L1;                                                  --(6)
        END IF;



        IF length(v_remaining_ids) = 0 THEN
               LEAVE L1;
        END IF;

   END LOOP;
END vid

The SQL procedure in Figure 4.22 takes a colon-separated list of employee IDs as input. For example, this input might look like:

000310:000320:000330:

The list is then parsed—in Lines (1) through (4)—to determine if all employee IDs are valid by verifying if the employee exists on Line (5). If any IDs in the list are not valid, the LOOP immediately exits using LEAVE on Line (6). If all employee IDs in the list are valid, the result of p_status is 0. Otherwise, the result of p_status is -1 to indicate an error.

The ITERATE statement is used to cause the flow of control to return to the beginning of a labeled LOOP. The syntax for ITERATE, depicted in Figure 4.23, is simple.

>>-ITERATE--label----------------------------------------------><

The example in Figure 4.24 is similar to the example in Figure 4.22, except that instead of exiting on the first invalid employee ID, the procedure returns the number of valid IDs found. ITERATE at Line (1) is used to return to the top of the LOOP whenever an invalid ID is encountered so that it is not counted.

CREATE PROCEDURE verify_ids2 ( IN p_id_list VARCHAR(100)
                             , OUT p_status INT )
     LANGUAGE SQL
     SPECIFIC verify_ids2                         -- applies to LUW and iSeries
  -- WLM ENVIRONMENT <env>                        -- applies to zSeries
vid: BEGIN
   DECLARE v_current_id VARCHAR(10);
   DECLARE v_position INT;
   DECLARE v_remaining_ids VARCHAR(100);
   DECLARE v_tmp INT;
   DECLARE SQLCODE INT DEFAULT 0;


   SET v_remaining_ids = p_id_list;
   SET p_status = 0;


   L1: LOOP
        SET v_position = LOCATE (':',v_remaining_ids);


        -- take off the first id from the list
        SET v_current_id = SUBSTR (v_remaining_ids, 1, v_position);

        IF LENGTH(v_remaining_ids) - v_position > 0 THEN
               SET v_remaining_ids = SUBSTR (v_remaining_ids, v_position+1);
        ELSE
            SET v_remaining_ids = '';
        END IF;


        -- take off the colon in last position of the current token
        SET v_current_id = SUBSTR (v_current_id, 1, v_position-1);


            -- determine if employee exists
        SELECT 1 INTO v_tmp FROM employee where empno = v_current_id;


        IF (SQLCODE <> 0) THEN
            -- employee id does not exist
               IF length(v_remaining_ids) > 0 THEN
                   ITERATE L1;                                      --(1)
               ELSE
                   LEAVE L1;
               END IF;
        END IF;


        SET p_status = p_status + 1;
            IF length(v_remaining_ids) = 0 THEN
               LEAVE L1;
            END IF;
   END LOOP;
END vid

RETURN is used to unconditionally and immediately terminate an SQL procedure by returning the flow of control to the caller of the stored procedure.

It is mandatory that when RETURN statement is issued, it returns an integer value. If the return value is not provided, the default is 0. The value returned is typically used to indicate success or failure of the stored procedure’s execution. This value can be a literal, variable, or an expression as long as it is an integer or evaluates to an integer.

You can make use of more than one RETURN statement in a stored procedure. RETURN can be used anywhere after the declaration blocks within the SQL procedure body.

The partial syntax for RETURN in the SQL procedure is illustrated in Figure 4.25.

>>-RETURN--+------------+--------------------------------------><
           '-expression-'

The following example uses the employee serial number (p_empno) to check if an employee’s last name, as stored in the database, matches the last name passed in as an input parameter (p_emplastname).

CREATE PROCEDURE return_test ( IN p_empno CHAR(6)
                             , IN p_emplastname VARCHAR(15) )
    LANGUAGE SQL
    SPECIFIC return_test                          -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
rt: BEGIN
   -- Declare variables
   DECLARE v_lastname VARCHAR(15);
   -- Procedure logic
   SELECT lastname
     INTO v_lastname
     FROM EMPLOYEE
WHERE empno = p_empno;
   IF v_lastname = p_emplastname THEN             -- (1)
      RETURN 1;                                   -- (2)
   ELSE                                           -- (3)
      RETURN -1;                                  -- (4)
   END IF;
END rt

This procedure receives two input parameters: p_emplastname and p_empno. If p_emplastname matches the lastname in the employee table identified by the employee number (p_empno) at Line (1), then the procedure exits with a return value of 1 at Line (2) implying success. If there is no match as shown in Line (3), then the SQL procedure returns with a failure indicated by a -1 return code on Line (4).

The COMMIT statement is used to complete the current unit of work, and to permanently record any of the changes made inside it to the database.

The syntax is trivial (see Figure 4.27).

             .-WORK--.
>>-COMMIT----+-------+-----------------------------------------><

A HOLD option is also provided for the COMMIT clause in DB2 for iSeries. If the COMMIT is invoked with the HOLD option, any open cursors are not closed, and any resources (except locks on table rows) acquired during the unit of work are held. The similar behavior in DB2 for LUW and zSeries is defined at the cursor declaration time. Refer to the FOR loop discussion in this chapter and Chapter 5, “Understanding and Using Cursors and Result Sets,” for more details.

The ROLLBACK statement is used to explicitly back out of any database changes that were made within the current unit of work (UOW). A unit of work is a sequence of SQL statements that are atomic for the purposes of recovery. Once the changes have been rolled back, a new unit of work is initiated.

DB2 also supports transaction save points and ROLLBACK TO SAVEPOINT. A ROLLBACK will cause the flow of control in your application to return to the previous save point declared within your unit of work.

The syntax for the ROLLBACK statement is shown in Figure 4.28.

               .-WORK--.
>>-ROLLBACK----+-------+---------------------------------------->

>-----+------------------------------------+-------------------><
      '-TO SAVEPOINT--+-----------------+--'
                      '-savepoint-name--'

ROLLBACK TO SAVEPOINT indicates that a partial rollback is to be performed. If no save point is active, an SQL error is returned (SQLSTATE 3B001).

The save point name indicates which save point to rollback to. After a successful ROLLBACK TO SAVEPOINT, the save point defined by save point name continues to exist. If the save point name does not exist, an error is returned (SQLSTATE 3B001). Data and schema changes made since the save point was set are undone.

To rollback the entire transaction, use the ROLLBACK WORK statement. All save points within the transaction are also released. For more information about save points, refer to Chapter 10, “Leveraging DB2 Application Development Features.”

In addition to the WORK and TO SAVEPOINT options of the ROLLBACK statement, DB2 UDB for iSeries also supports the HOLD option. When the ROLLBACK statement is issued with this option, any open cursors are not closed, and any resources (except locks on table rows) acquired during the unit of work are held.