Certain PL/SQL control structures offer structured methods for processing executable statements in your program. You use an IF statement or a CASE statement to test a condition to determine which parts of your code to execute; you use a LOOP variation (described in Chapter 5) to execute a section of code more than once. In addition to these well-structured approaches to program control, PL/SQL offers the GOTO. The GOTO statement performs unconditional branching to another executable statement in the same execution section of a PL/SQLblock. As with other constructs in the language, if you use GOTO appropriately and with care, your programs will be stronger for it.
where label_name is the name of a label identifying the target statement. This GOTO label is defined in the program as follows:
You must surround the label name with double enclosing angle brackets (<< >>). When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label. Following is a complete code block containing both a GOTO and a label:
BEGIN GOTO second_output; DBMS_OUTPUT.PUT_LINE('This line will never execute.'); <<second_output>> DBMS_OUTPUT.PUT_LINE('We are here!'); END;
Contrary to popular opinion (including mine), the GOTO statement can come in handy. There are cases where a GOTO statement can simplify the logic in your program. On the other hand, because PL/SQL provides so many different control constructs and modularization techniques, you can almost always find a better way to do something than with a GOTO.
These restrictions are described in detail in the following sections.
A label itself is not an executable statement (notice that it does not have a semicolon after the label brackets), so it cannot take the place of one. All of the uses of the <<all_done>> label in the following code are illegal because the labels are not followed by an executable statement:
IF status_inout = 'COMPLETED' THEN <<all_done>> /* Illegal! */ ELSE schedule_activity; END IF; DECLARE CURSOR company_cur IS ...; BEGIN FOR company_rec IN company_cur LOOP apply_bonuses (company_rec.company_id); <<all_done>> /* Illegal! */ END LOOP; END; FUNCTION new_formula (molecule_in IN NUMBER) RETURN VARCHAR2 IS BEGIN ... construct formula for molecule ... RETURN formula_string; <<all_done>> /* Illegal! */ END;
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label
The only way to enter an IF statement is through an evaluation of an IF condition to TRUE. Therefore, this code produces an error:
GOTO label_inside_IF; IF status = 'NEW' THEN <<label_inside_IF>> /* Out of scope! */ show_new_one; END IF;
Likewise, you can’t jump into the middle of a CASE statement.
The only way to enter a block-within-a-block is through the sub-block’s BEGIN statement. PL/SQL insists on orderly entrances and exits. This code produces an error because it doesn’t comply with this structure:
GOTO label_inside_subblock; BEGIN <<label_inside_subblock>> /* Crosses block boundary! */ NULL; END;
Each IF clause of the IF statement is its own scope. A GOTO may not transfer from one clause to another. This code produces an error:
IF status = 'NEW' THEN <<new_status>> GOTO old_status; /* Crosses IF clause boundary! */ ELSIF status = 'OLD' THEN <<old_status>> GOTO new_status; /* Crosses IF clause boundary! */ END IF;
Likewise, you can’t jump from one clause to another within a CASE statement.
FOR month_num IN 1 .. 12 LOOP <<do_a_month>> schedule_activity (month_num); END LOOP; GOTO do_a_month; /* Can't go back into loop. */
DECLARE FUNCTION local_null IS BEGIN <<descrip_case_statement>> NULL; END; BEGIN GOTO descrip_case_statement; /* Label not visible here. */ END;
The target label must be in the same part of the PL/SQL block as the GOTO statement.A GOTO in the execution section may not go to a label in the exception section; a GOTO in the exception section may not go to a label in the execution section. A GOTO in an exception handler may reference a label in the same handler. The following code example generates the same PL/SQL error shown in the previous section (PLS-00375):
BEGIN /* || The label and GOTO must be in the same section! */ GOTO out_of_here; EXCEPTION WHEN OTHERS THEN <<out_of_here>> /* Out of scope! */ NULL; END;