PL/SQL GOTO

Introduction to PL/SQL GOTO

Pl/SQL GOTO statement is used for transferring the control and flow of execution from one point to the other which is a labeled block or single statement with a label. The GOTO command transfers the flow of execution to a place in program which occurs exactly below the label which is mentioned in the GOTO statement. This statement proves very useful and beneficial while writing the programs in Pl/ SQL. However, there are certain restrictions on the usage of GOTO statement and where it should not be used in the program. In this article, we will study the general syntax of the GOTO statement and how we can use and implement it in our PL/ SQL program along with the help of certain examples. Further, we will also list out the cases where usage of GOTO statements is restricted in PL/ SQL.

Syntax:

The syntax of using the GOTO statement in PL/ SQL is as shown below –

GOTO name_of_label;

In the above syntax, the name_of_label clause is the label name assigned to a particular position in the program which acts as a target statement where the control of the program needs to be transferred. While using these label names in Pl/ SQL program we should enclose the same inside the double angular brackets as shown below –

<<name_of_label>>

Working of GOTO statement occurs in two steps:

Firstly, identify the statement or block where control needs to be transferred by searching the name of the label mentioned inside the two angular brackets.
Second, transfer the flow of control to the immediate next statement that occurs below the identified label name.
The name of the label can be any string that contains alphabets, underscore and numbers and the same rules apply over it as in case of usage of variables and the naming convention followed while giving names to variables in PL/ SQL.

Examples

Let us understand the usage and implementation of GOTO statement in PL/ SQL with the help of certain examples.

Example #1

Let us consider a simple example with three labels in our program to understand the flow of execution in GOTO statements. In this example, we have three labels namely header message, greeting message, and ending message. The PL/ SQL program we have written for demonstration of the same is as shown below –

BEGIN
GOTO header_message;
<<greeting_message>>
DBMS_OUTPUT.PUT_LINE( 'Hi' );
GOTO ending_message;
<<header_message>>
DBMS_OUTPUT.PUT_LINE( ' Demonstration of working of PL/ SQL GOTO statement ' );
GOTO greeting_message;
<<ending_message>>
DBMS_OUTPUT.PUT_LINE( 'Wish you all the best for your learnings...' );
END;

The output of the above PL/ SQL program is as shown below –

The detailed explanation about the execution of the above program is as specified here –

First of all, at the beginning, itself, GOTO statement is used with label header message, hence, the program immediately goes for searching <<header message>> label in the program, and the execution is transferred to the statement just below this label which prints the message “Demonstration of working of PL/ SQL GOTO statement” in the output.

The execution again returns back to the statement just below the greeting message label due to GOTO greeting message statement and executes the statement present below the greeting message label sequentially and prints the message “Hi” in the output.

After printing “Hi” message it encounters the GOTO statement with a label ending message label which again transfers the control to the statement just below the label with the name ending message printing the message “Wish you all the best for your learnings…” in the end.

Example #2

Let us consider one more example to understand the working of GOTO statement.

BEGIN
GOTO executed_first;
<<executed_second>>
DBMS_OUTPUT.PUT_LINE( ' But, Be careful! It also has some restrictions. ' );
<<executed_first>>
DBMS_OUTPUT.PUT_LINE( ' GOTO statement is very easy to use. ' );
GOTO executed_second;
END;

The output of the execution of above program is as shown below –

As we can see that due to the occurrence of GOTO executed first label statement the control transfers to the statement written below the executed first label and prints the message “GOTO statement is very easy to use.” In the output and then it goes back to executed second because of occurrence of GOTO statement with label executed second where it prints the message “But, Be careful! It also has some restrictions.” In the output.

Restrictions while using GOTO statement

Note that by using the GOTO statement you are giving the control over flow of execution in your hands and there is no more sequential flow. Also, there are certain places where GOTO statement is not allowed to be used in PL / SQL which are listed as shown below –

The GOT statement in PL/ SQL cannot be used to transfer the control to the statements such as LOOP, CASE, or IF inside the program or it will issue an error in that case. For example, when we try to transfer the flow inside the if body using GOTO statement then it will issue an error saying INSIDE_IF_STATEMENT cannot be branched as it is illegal usage.

We cannot transfer the flow of control inside the IF, ELSE IF, and ELSE statements as it is predefined format specified and used internally in PL/ SQL. This also applies while using WHEN and THEN statements in the program. Use of GOTO statement for transferring the control to the body of WHEN and THEN is also treated illegally.

Flow of control cannot be transferred to the blocks of exception handlers and the statements that are present outside the subprogram.

GOTO statements cannot be used inside the exception handlers. Neither the labels nor GOTO statement is legal inside exception handlers.

The flow of control cannot be transferred from the exception handler back to itself or anywhere inside the exception handler.

Conclusion – PL/SQL GOTO

The GOTO statement is used in PL/ SQL to transfer the flow of control of execution inside the program from the current statement to a particular target statement or block present just below the name of the label specified while using GOTO statement. We need to be careful while using it and understand the restrictions applied for the use of GOTO before we use it in our program.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL GOTO” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PL/SQL Cursor Loop
2. SQLite create index
3. PL/SQL Date Functions
4. SQLite Stored Procedures