O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The GOTO Statement

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.

The general format for a GOTO statement is:

GOTO label_name;

where label_name is the name of a label identifying the target statement. This GOTO label is defined in the program as follows:

               <<label_name>>

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.

Restrictions on the GOTO Statement

There are several restrictions on the GOTO statement:

  • At least one executable statement must follow a label.

  • The target label must be in the samescope as the GOTO statement.

  • The target label must be in the same part of the PL/SQL block as the GOTO.

These restrictions are described in detail in the following sections.

At least one executable statement must follow a label

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;

Keywords such as END, THEN, ELSE, ELSIF, and END LOOP do not represent executable statements, and thus such keywords cannot be immediately preceded by a label.

The target label must be in the same scope as the GOTO statement

A GOTO statement’s target label must be in the same scope as the GOTO statement. In the context of the GOTO statement, each of the following constructs maintains its own scope:

  • Functions

  • Procedures

  • Anonymous blocks

  • IF statements

  • LOOP statements

  • Exception handlers

  • CASE statements

The following code examples show common programming errors. Each illustrates an attempt to branch to a label that is out of scope, and each generates the following PL/SQL error:

PLS-00375: illegal GOTO statement; this GOTO cannot branch to label
IF conditions

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.

BEGIN statements

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;
Scope of IF statements

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.

Don’t jump into the middle of a loop

You cannot jump into the middle of aloop with a GOTO. This code produces an error:

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. */
Don’t GOTO a local module

You cannot issue a GOTO from the main body of a block to a label in a function, procedure, or other module defined within that block. This code produces an error:

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

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;

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required