- Compound Statements
- Using Labels
- Conditional Statements
- Looping Statements
- Transfer of Control Statements
- Chapter Summary
This chapter is from the book
This chapter is from the book
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.
){kind=link}
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.
){kind=link}
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.
){kind=link}
Figure 3.21 LEAVE Syntax Diagram
>>-LEAVE--label------------------------------------------------><
Figure 3.22 illustrates how to use LOOP and LEAVE.
){kind=link}
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:
){kind=link}
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.
){kind=link}
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.
){kind=link}
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).
){kind=link}
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).