Using GOTO and NULL 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. In the following example, you go to an executable statement farther down in a sequence of statements:

BEGIN
...
GOTO insert_row;
...
<>
INSERT INTO emp VALUES ...
END;
In the next example, you go to a PL/SQL block farther up in a sequence of statements:
DECLARE
x NUMBER := 0;
BEGIN
<>
BEGIN
x := x + 1;
END;
IF x < 10 THEN
GOTO increment_x;
END IF;
END;

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;
<> -- not allowed
END LOOP; -- not an executable statement
END;
To correct the previous example, add the NULL statement::
FOR i IN 1..50 LOOP
IF done THEN

GOTO end_loop;
END IF;
...
<>
NULL; -- an executable statement
END LOOP;

As the following example shows, a GOTO statement can branch to an enclosing block from the current block:

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

The GOTO statement branches to the first enclosing block in which the referenced label

appears.

Restrictions on the GOTO Statement

Some possible destinations of a GOTO statement are not allowed. Specifically, a GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or

sub-block. For example, the following GOTO statement is not allowed:

BEGIN

GOTO update_row; -- can't branch into IF statement

IF valid THEN

<>

UPDATE emp SET ...

END IF;

END;

As the example below shows, a 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 emp SET ...
   END IF;
END;

The next example shows that 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 FROM parts WHERE ...
   END;
END;

Also, 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 emp SET ...
END;

Finally, a GOTO statement cannot branch from an exception handler into the current block. For example, the following GOTO statement is not allowed:

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

However, a GOTO statement can branch from an exception handler into an enclosing block.

NULL Statement

The NULL statement does nothing other than pass control to the next statement. In a conditional construct, the NULL statement tells readers that a possibility has been considered, but no action is necessary. In the following example, the NULL statement shows that no action is taken for unnamed exceptions:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      ROLLBACK;
   WHEN VALUE_ERROR THEN
      INSERT INTO errors VALUES ...
      COMMIT;
   WHEN OTHERS THEN
      NULL;
END;

In IF statements or other places that require at least one executable statement, the NULL statement to satisfy the syntax. In the following example, the NULL statement emphasizes that only top-rated employees get bonuses:

IF rating > 90 THEN
   compute_bonus(emp_id);
ELSE
   NULL;
END IF;

Also, the NULL statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that lets you defer the definition of a procedure or function until you test and debug the main program. In the following example, the NULL statement meets the requirement that at least one statement must appear in the executable part of a subprogram:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
BEGIN
   NULL;
END debit_account;