Summary: this tutorial introduces you PL/SQL
GOTO statement and discusses the restrictions of the
Introduction to PL/SQL
GOTO statement allows you to transfer control to a labeled block or statement. The following illustrates the syntax of the
label_name is the name of a label that identifies the target statement. In the program, you surround the label name with double enclosing angle brackets as shown below:
When PL/SQL encounters a
GOTO statement, it transfers control to the first executable statement after the label.
GOTO statement example
The following shows an example of using the
BEGIN GOTO second_message; <<first_message>> DBMS_OUTPUT.PUT_LINE( 'Hello' ); GOTO the_end; <<second_message>> DBMS_OUTPUT.PUT_LINE( 'PL/SQL GOTO Demo' ); GOTO first_message; <<the_end>> DBMS_OUTPUT.PUT_LINE( 'and good bye...' ); END;
The output is:
PL/SQL GOTO Demo Hello and good Bye...
The following explains the sequence of the block in detail:
- First, the
GOTO second_messagestatement is encountered, therefore, the control is passed to the statement after the
- Second, the
GOTO first_messageis encountered, so the control is transferred to the statement after the
- Third, the
GOTO the_endis reached, hence the control is passed to the statement after the
The picture below illustrates the sequence:
PL/SQL GOTO statement restrictions
GOTO statement is subject to the following restrictions.
The following example attempts to transfer control into an
IF statement using a
DECLARE n_sales NUMBER; n_tax NUMBER; BEGIN GOTO inside_if_statement; IF n_sales > 0 THEN <<inside_if_statement>> n_tax := n_sales * 0.1; END IF; END;
Oracle issued the following error:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'INSIDE_IF_STATEMENT'
Second, you cannot use a
GOTO statement to transfer control from one clause to another in the
IF statement e.g., from
IF clause to
ELSE clause, or from one
WHEN clause to another in the
The following example attempts to transfer control to a clause in the
DECLARE n_sales NUMBER; n_commission NUMBER; BEGIN n_sales := 120000; IF n_sales > 100000 THEN n_commission := 0.2; GOTO zero_commission; elsif n_sales > 50000 THEN n_commission := 0.15; elsif n_sales > 20000 THEN n_commission := 0.1; ELSE <<zero_commission>> n_commission := 0; END IF; END;
Oracle issued the following error.
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'ZERO_COMMISSION'
Third, you cannot use a
GOTO statement to transfer control out of a subprogram or into an exception handler.
Fourth, you cannot use a
GOTO statement to transfer control from an exception handler back into the current block.
In this tutorial, you have learned how to use the PL/SQL
GOTO statement to transfer control to a labeled block or statement.