This chapter is from the book
This chapter is from the book
This chapter is from the book
3.2 Using Labels
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.
Labels also help increase the readability of code. Try to label based on the purpose of the statement or code block.
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 the SQL variable.
Figure 3.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.
){kind=link}
Figure 3.6 Labeled Compound Statement Example
CREATE PROCEDURE show_label (OUT p_WorkerID INT)
SPECIFIC show_label
LANGUAGE SQL
-- Procedure logic
sl1: BEGIN
-- Declare variables
DECLARE v_ID INT; -- (1)
sl2: BEGIN
DECLARE v_ID INT; -- (2)
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;
END sl1
You can see that, to avoid ambiguity, the two v_ID variables defined at (1) and (2) are qualified with the label of the compound statement that they were defined in at (3), (4), and (6). When qualification is not used, as in (5), the variable will be qualified with the label of the compound statement from which it is being referenced. So the value assignment at (5) will actually assign a value of 3 to sl2.v_ID, which means that p_WorkerID at (6) will also be assigned a value of 3.
It is always good programming practice not to declare multiple variables of the same name, regardless of the fact that they can be referenced as label.variable_name.
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 further on in this chapter.