Oracle / PLSQL: GOTO Statement

This Oracle tutorial explains how to use the GOTO statement in Oracle with syntax and examples.

Description

The GOTO statement causes the code to branch to the label after the GOTO statement.

Syntax

The syntax for the GOTO statement in Oracle/PLSQL consists of two parts - the GOTO statement and the Label Declaration:

GOTO statement

The GOTO statement consists of the GOTO keyword, followed by a label_name.

GOTO label_name;

Label Declaration

The Label Declaration consists of the label_name encapsulated in << >>, followed by at least one statement to execute.

<<label_name>>
 {...<strong>statements...</strong>}

Note

• label_name must be unique within the scope of the code.
• There must be at least one statement to execute after the Label Declaration.

Example

Let's look at an Oracle example that uses the GOTO statement.

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
   cnumber number;

   CURSOR c1
   IS
     SELECT MAX(course_number)
     FROM courses_tbl
     WHERE course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   IF c1%notfound then
      GOTO default_number;

   ELSE
      GOTO increment_number;
   END IF;

<<default_number>>
   cnumber := 0;

<<increment_number>>
   cnumber := cnumber + 1;

   close c1;

RETURN cnumber;

END;

In this GOTO example, we have created two GOTO statements. The first one is called default_number and the second one is called increment_number.