PL/SQL GOTO

Summary: in this tutorial, you will learn how to use PL/SQL GOTO statement to jump unconditionally to another executable statement in a same PL/SQL block.

Introduction to PL/SQL GOTO statement

PL/SQL GOTO statement allows you to jump to a specific executable statement in the same execution section of a PL/SQL block.

The syntax of the PL/SQL GOTO statement is as follows:

GOTO label_nameCode language: SQL (Structured Query Language) (sql)

Where label_name is a label that defines the target statement. In a program, you define a label as follows:

<<label_name>>Code language: SQL (Structured Query Language) (sql)

The label name is enclosed in double angle brackets ( <<>>).

PL/SQL GOTO restrictions

There are some restrictions on using the PL/SQL GOTO statement that you should be aware of before using it.

First, the PL/SQL GOTO statement cannot jump to an IF statement, CASE statement, LOOP statement, or child-block.

The only way to branch into an IF statement is through its condition check. This is also applied to the CASE statement. In the following example, the GOTO statement tries to jump to inside an IF statement, which is not allowed. As a result, PL/SQL issues an error message.

BEGIN 
    GOTO goto_inside_if;
    IF v_status = 'COMPLETED' THEN
      <<goto_inside_if>>
      ...
    END IF;
END;Code language: SQL (Structured Query Language) (sql)

The GOTO statement cannot branch to the middle of a loop:

BEGIN
  GOTO goto_inside_loop;
  FOR n_day IN 1 .. 7
  LOOP
     <<goto_inside_loop>>
     ...
  END LOOP; 
END;Code language: SQL (Structured Query Language) (sql)

The only way to enter the child block is through its BEGIN statement. The following example does not comply with this rule, hence causes an error.

BEGIN
  GOTO inside_child_block;
  ...
  BEGIN
    ...
    <<inside_child_block>>
    ...
  END;
END;Code language: SQL (Structured Query Language) (sql)

Second, you cannot use a GOTO statement to branch from an exception handling section back into the current block. The following example does not comply with this rule, hence, it causes an error.

BEGIN
  --...
  <<back_to_exec>>
  --...
  EXCEPTION.
    --...
    WHEN SOMETHING_WRONG_HAPPENED.
        GOTO back_to_exec;
    --...
END;Code language: SQL (Structured Query Language) (sql)

Third, you cannot use a GOTO statement to jump out of a subprogram. To end a subprogram, you jump to the end of subprogram using the GOTO statement or use a RETURN statement instead.

Fourth, you cannot use a GOTO statement to branch from one IF clause to another, or from one WHEN clause of a CASE statement to another.

Fifth, there must be at least one executable statement appears after the target label. If you just want to branch to a target label without doing anything, you use a NULL statement.

PL/SQL GOTO examples

The following example demonstrates how to use PL/SQL GOTO statement:

SET SERVEROUTPUT ON SIZE 1000000;
BEGIN 
  GOTO label_1;
  DBMS_OUTPUT.PUT_LINE('Right after the GOTO statement');
  <<label_1>>
  DBMS_OUTPUT.PUT_LINE('It is here!');
END;
/Code language: SQL (Structured Query Language) (sql)

The output of the script is:

It is here!

When PL/SQL reaches the GOTO statement, it skips everything and immediately jumps to the target label i.e. label_1 and it displays the message It is here

The PL/SQL GOTO statement has a bad reputation because it makes the code hard to understand and difficult to debug. In general, the PL/SQL GOTO statement should be avoided and replaced by other statements such as IF or CASE statement.

However, there are some cases that using the GOTO statement can make the program more efficient. See the following example:

BEGIN
   IF ... THEN
         FOR rec_emp IN cur_emp LOOP
            -- By pass all using GOTO
            GOTO <<exit_now>>
         END LOOP;
         -- a lot of code here
   END IF;
   -- target label
   <<exit_now>> 
   NULL;
END;Code language: SQL (Structured Query Language) (sql)

In the above example, instead of going through a lot of code after the first FOR loop statement, we used the GOTO statement to jump to the end of the block that makes the code very efficient.

In this tutorial, you have learned how to use the PL/SQL GOTO statement to branch unconditionally to a target label in the same block.