Using GOTO Statement in Oracle

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

Description

The GOTO announcement motives the code to branch to the label after the GOTO statement.

Syntax

The syntax for the GOTO announcement in Oracle/PLSQL consists of two components – the GOTO announcement and the Label Declaration:

GOTO statement

The GOTO declaration consists of the GOTO keyword, observed through a label_name.

GOTO label_name;

Label Declaration

The Label Declaration consists of the label_name encapsulated in << >>, accompanied by means of at least one announcement to execute.

<<label_name>>
 {...statements...}

Note

label_name need to be unique inside the scope of the code. There need to be at least one assertion to execute after the Label Declaration.

Example

Let’s seem to be at an Oracle instance 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 2d one is called increment_number.