Home > Articles > Data > DB2

  • Print
  • + Share This
This chapter is from the book

3.5 Transfer of Control Statements

Transfer of control statements are used to specifically 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.

3.5.1 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 get long. Besides, GOTO is not necessary since 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 3.19.

Figure 3.19 GOTO Syntax Diagram

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

There are a few additional scope considerations to be aware of:

If the GOTO statement is defined in a FOR statement, label must be defined inside the same FOR statement, excluding 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, excluding 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 stored 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.

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

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

In Figure 3.20, a stored procedure is used to increase the salary of those employees who have been with the company for over 1 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 the rating.

Figure 3.20 GOTO Example

CREATE PROCEDURE adjust_salary ( IN p_empno CHAR(6) 
            ,IN p_rating INTEGER 
            ,OUT p_adjusted_salary DECIMAL (8,2) ) 
    SPECIFIC adjust_salary 
    LANGUAGE SQL 
as: 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 exit;	                                        -- (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;	  	 	 
 	 	 
exit:	                                                        -- (5)	 
    SET p_adjusted_salary = v_new_salary;	 	 
END as	 	 

This stored procedure makes use of the GOTO statement at (1) to avoid increasing the salary of those employees who have not yet been with the company for over a year.

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

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

3.5.2 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 3.21, is trivial.

Figure 3.21 LEAVE Syntax Diagram

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

Figure 3.22 illustrates how to use LOOP and LEAVE.

Figure 3.22 Example of LOOP and LEAVE

CREATE PROCEDURE verify_ids (IN p_id_list VARCHAR(100) 
                            ,OUT p_status INT) 
SPECIFIC verify_ids 
LANGUAGE SQL 
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 3.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 (lines (1) through (4)) to determine if all employee IDs are valid by verifying if the employee exists at (5). If any IDs in the list are not valid, the LOOP immediately exists using LEAVE at (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.

3.5.3 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 3.23, is simple:

Figure 3.23 ITERATE Syntax Diagram

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

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

Figure 3.24 ITERATE Example

CREATE PROCEDURE verify_ids (IN p_id_list VARCHAR(100) 
                            ,OUT p_status INT) 
SPECIFIC verify_ids 
LANGUAGE SQL 
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 

3.5.4 RETURN

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

It is mandatory when RETURN is issued that it return an integer value. 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. 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 within the SQL procedure body.

The syntax for RETURN is illustrated in Figure 3.25.

Figure 3.25 RETURN Syntax Diagram

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

Figure 3.26 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 3.26 RETURN example

CREATE PROCEDURE return_test ( IN p_empno CHAR(6) 
            ,IN p_emplastname VARCHAR(15) ) 
    SPECIFIC return_test 
    LANGUAGE SQL 
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 (1), then the procedure exits with a return value of 1 at (2) implying success. If there is no match (3), then the stored procedure returns with a failure indicated by a -1 return code at (4).

  • + Share This
  • 🔖 Save To Your Account