Transfer of Control Statements | Intelligent Enterprises of the 21st Century

Transfer of control statements are used to tell the SQL procedure where to continue execution. This unconditional branching can be used to cause the flow of control to jump from one point to another point, which can either precede or follow the transfer of control statement.

SQL PL supports four such statements: GOTO, LEAVE, ITERATE, and RETURN. Each will be discussed in detail in the following sections.

ROLLBACK and COMMIT statements can also be used within the procedure body. These two are introduced here for completeness, even though they are more transaction control statements than flow of control statements.

GOTO

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.

Figure 4.19. The GOTO syntax diagram.

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

You should be aware of a few additional scope considerations:

If the GOTO statement is defined in a FOR statement, the label must be defined inside the same FOR statement, unless it is in a nested FOR statement or nested compound statement.
If the GOTO statement is defined in a compound statement, label must be defined inside the same compound statement, unless it is in a nested FOR statement or nested compound statement.
If the GOTO statement is defined in a handler, label must be defined in the same handler, following the other scope rules.
If the GOTO statement is defined outside of a handler, label must not be defined within a handler.
If label is not defined within a scope that the GOTO statement can reach, an error is returned (SQLSTATE 42736).

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.

Tip

If you must use GOTO, then try to use it to skip to the end of the SQL procedure or loop.

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.

Figure 4.20. Another GOTO example.

 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.

LEAVE

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.

Figure 4.21. The LEAVE syntax diagram.

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

Figure 4.22 illustrates how to use LOOP and LEAVE.

Figure 4.22. An example of 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 parsedin 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.

ITERATE

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.

Figure 4.23. An ITERATE syntax diagram.

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

Figure 4.24. An ITERATE example.

 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

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.

Note

In order for an OUT parameter to return a value, it must be set prior to the RETURN statement being invoked.

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.

Figure 4.25. The RETURN syntax diagram.

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

Figure 4.26. A RETURN example.

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

Tip

Only use the RETURN statement to return integers as status indicators.

COMMIT

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

Figure 4.27. COMMIT statement syntax.

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

Note

On zSeries, the CREATE PROCEDURE statement provides the COMMIT ON RETURN clause to automatically issue a COMMIT after the SQL procedure returns to the caller.

ROLLBACK

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.

Note

You cannot issue a ROLLBACK or COMMIT from within an ATOMIC compound statement, although on iSeries you can issue a ROLLBACK TO SAVEPOINT in an ATOMIC compound statement.

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

Figure 4.28. ROLLBACK statement syntax.

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