GOTO Statement In ORACLE

GOTO Statement

The GOTO statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block.

BEGIN
   ...
   GOTO insert_row;
   ...
   <<insert_row>>
   INSERT INTO emp VALUES ...
END;

you go to a PL/SQL block farther up in a sequence of statements:

The label end_loop in the following example is not allowed because it does not precede an executable statement:

DECLARE
   done  BOOLEAN;
BEGIN
   ...
   FOR i IN 1..50 LOOP
      IF done THEN
         GOTO end_loop;
      END IF;
      ...
   <<end_loop>>  -- not allowed
   END LOOP;  -- not an executable statement
END;

 In the below case  GOTO statement can branch to an enclosing block from the current block:

DECLARE
   my_ename  CHAR(10);
BEGIN
   <<get_name>>
   SELECT ename INTO my_ename FROM emp WHERE ...
   BEGIN
      ...
      GOTO get_name;  -- branch to enclosing block
   END;
END;

Restrictions:

 1) GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or sub-block.

BEGIN
...

   GOTO update_row;  -- can't branch into IF statement
   ...
   IF valid THEN
      ...
      <<update_row>>
      UPDATE emp SET ...
   END IF;
END;

2)GOTO statement cannot branch from one IF statement clause to another. Likewise a GOTO statement cannot branch from one CASE statement WHEN clause to another.

BEGIN
...    IF valid THEN
      ...
      GOTO update_row;  -- can't branch into ELSE clause
   ELSE
      ...
      <<update_row>>
      UPDATE emp SET ...
   END IF;
END;
3)A GOTO statement cannot branch from an enclosing block into a sub-block:

BEGIN
...    IF status = 'OBSOLETE' THEN
      GOTO delete_part;  -- can't branch into sub-block
   END IF;
   ...
   BEGIN
      ...
      <<delete_part>>
      DELETE FROM parts WHERE ...
   END;
END;

4) A GOTO statement cannot branch out of a subprogram, as the following example shows:

DECLARE
   ...
   PROCEDURE compute_bonus (emp_id NUMBER) IS
   BEGIN
      ...
      GOTO update_row;  -- can't branch out of subprogram
   END;
BEGIN
   ...
   <<update_row>>
   UPDATE emp SET ...
END;

5) A GOTO statement cannot branch from an exception handler into the current block.

DECLARE
   ...
   pe_ratio  REAL;
BEGIN
   ...
   SELECT price / NVL(earnings, 0) INTO pe_ratio FROM ...
   <<insert_row>>
   INSERT INTO stats VALUES (pe_ratio, ...);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      pe_ratio := 0;
      GOTO insert_row;  -- can't branch into current block
END;