Oracle PL/SQL: GOTO Statment

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;