5.2.1.1 At least one executable statement must follow a label

A label itself is not an executable statement (notice that it does not have a semicolon (;) after the label brackets), so it cannot take the place of one. All of the uses of the loop label in the following blocks are illegal because the labels are not followed by an executable statement:

IF status_inout = 'COMPLETED'
THEN
   <<all_done>> /* Illegal! */
ELSE
   schedule_activity;
END IF;

DECLARE
   CURSOR company_cur IS ...;
BEGIN
   FOR company_rec IN company_cur
   LOOP
      apply_bonuses (company_rec.company_id);
      <<loop_termination>> /* Illegal! */
   END LOOP;
END;

FUNCTION new_formula (molecule_in IN NUMBER) RETURN VARCHAR2
IS
BEGIN
   ... construct formula for molecule ...
   RETURN formula_string;

   <<all_done>> /* Illegal! */

END;

5.2.1.2 Target labels and scope of GOTO

The target label must be in the same scope as the GOTO statement. In the context of the GOTO statement, each of the following constructs maintains its own scope: functions, procedures, anonymous blocks, IF statements, LOOP statements, and exception handlers. All of the code examples below generate the same PL/SQL error:

PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 

• IF conditions. The only way to enter an IF statement is through an evaluation to TRUE of an IF condition. Therefore, this code produces an error:

  GOTO label_inside_IF;
  IF status = 'NEW'
  THEN
     <<label_inside_IF>> /* Out of scope! */
     show_new_one;
  END IF;

• BEGIN statements. The only way to enter a block-within-a-block is through the sub-block's BEGIN statement. PL/SQL insists on orderly entrances and exits. This code produces an error because it doesn't comply with this structure:

  GOTO label_inside_subblock;
  BEGIN
     <<label_inside_subblock>> /* Crosses block boundary! */
     NULL;
  END;

• Scope of IF statements. Each IF clause of the IF statement is its own scope. A GOTO may not transfer from one clause to another. This code produces an error:

  IF status = 'NEW'
  THEN
     <<new_status>>
     GOTO old_status; /* Crosses IF clause boundary! */
  ELSIF status = 'OLD'
  THEN
     <<old_status>>
     GOTO new_status; /* Crosses IF clause boundary! */
  END IF;

• Don't jump into the middle of a loop. You cannot jump into the middle of a loop with a GOTO. This code produces an error:

  FOR month_num IN 1 .. 12
  LOOP
     <<do_a_month>>
     schedule_activity (month_num);
  END LOOP;
  GOTO do_a_month; /* Can't go back into loop. */

• Don't GOTO a local module. You also cannot issue a GOTO inside of a module to a label in the main body. This code produces an error:

  DECLARE
     FUNCTION local_null IS
     BEGIN
        <<descrip_case_statement>>
        NULL;
     END;
  BEGIN
     GOTO descrip_case_statement; /* Label not visible here. */
  END;

5.2.1.3 Target labels and PL/SQL blocks

The target label must be in the same part of the PL/SQL block as the GOTO statement. A GOTO in the executable section may not go to a label in the exception section. Similarly, a GOTO in the exception section may not go to a label in the executable section. A GOTO in an exception handler may reference a label in the same handler. The code example below generates the same PL/SQL error shown in the previous section (PLS-00375):

BEGIN
   /*
   || The label and GOTO must be in the same section!
   */
   GOTO out_of_here;
EXCEPTION
   WHEN OTHERS
   THEN
      <<out_of_here>> /* Out of scope! */
      NULL;
END;

5.2.2.1 Improving the readability of your program

There are many situations in your program where you logically do not want to take any action. In most of these cases, PL/SQL will let you write nothing and the program will execute as you wish. The only drawback is the ambiguity surrounding this solution: it is not clear to a person examining the program that you knowingly did not take any action.

Consider the IF statement. When you write an IF statement you do not have to include an ELSE clause. To produce a report based on a selection, you can code:

IF :report.selection = 'DETAIL'
THEN
   exec_detail_report;
END IF;

What should the program have been doing if the report selection is not `DETAIL'? One would assume that the program was supposed to do nothing. But because this is not explicitly stated in the code, one is left to wonder if perhaps there was an oversight. If, on the other hand, you include an explicit ELSE clause that does nothing, you state very clearly, "Don't worry, I thought about this possibility and I really want nothing to happen."

IF :report.selection = 'DETAIL'
THEN
   exec_detail_report;
ELSE
   NULL;
END IF;

5.2.2.2 Nullifying the effect of a raised exception

The optional exception section of a program contains one or more exception handlers. These handlers trap and handle errors that have been raised in your program. The structure and flow of the exception section is similar in structure and flow to a conditional case statement, as follows:

EXCEPTION
   WHEN <exception_name1>
   THEN
      executable_statements;

   WHEN <exception_nameN>
   THEN
      executable_statements;

   WHEN OTHERS
   THEN
      executable_statements;
END;

If <exception_name1> is raised, then execute its statements; if <exception_nameN> is raised, then execute its statements; and so on. The WHEN OTHERS clause handles any exceptions not handled in the previous WHEN clauses (it is just like the ELSE clause of the IF statement). You can use the NULL statement to make sure that a raised exception halts execution of the current PL/SQL block, but does not propagate any exceptions to enclosing blocks:

PROCEDURE calc_avg_sales
BEGIN
   :sales.avg := :sales.month1 / :sales.total;
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      :sales.avg := 0;
      RAISE FORM_TRIGGER_FAILURE;

   WHEN OTHERS THEN NULL;

END;

If total sales are zero, then an exception is raised, the average is set to zero, and the trigger processing in Oracle Forms is halted. If any other exceptions occur (such as VALUE_ERROR, which would be raised if the number generated by the calculation is larger than the sales.avg item allows), then the procedure does nothing and processing continues.

Even though the WHEN OTHERS clause of the exception section is like the ELSE clause of an IF statement, the impact of a NULL statement is very different in each of these statements. The addition of an ELSE NULL clause to the IF statement has an impact only on the readability of the code. The IF statement executes in precisely the same way it would have without the ELSE NULL clause.

When you include an exception handler with the NULL executable statement, you are saying, in effect: "End processing in the current PL/SQL block and move to the enclosing block, but otherwise take no action." This is very different from leaving out the exception handler altogether. If there is no exception handler, then PL/SQL raises the exception in the enclosing block again, and continues to do so until the last block, at which point it becomes an unhandled exception and halts the program. A "null" exception handler passes control back to the enclosing block, but does not cause the exception to be raised again.

See Chapter 8, Exception Handlers, for more detailed information about exceptions.

5.2.2.3 Supporting top-down design of modules

With top-down design (also known as top-down decomposition), you move from a general description of your system through step-by-step refinements of that idea to the modules which implement that system, and finally to the code that implements the modules. By moving through levels of abstraction, your mind concentrates on a relatively small number of issues at a time and can better process the details.

From a programming perspective, you implement top-down design by creating "stubs," or dummy programs. A stub will have the name and parameter list you need, but not have anything under the covers. Using stubs you define the API (application programmatic interface), which indicates the way that the different modules connect to each other.

In order for a PL/SQL program to compile, it must have at least one executable statement. The smallest, most nonintrusive program you can build will therefore be composed of a single NULL statement. Here are sample stubs for both a procedure and a function:

PROCEDURE revise_timetable (year_in IN NUMBER) IS
BEGIN
   NULL;
END;

FUNCTION company_name (company_id_in IN NUMBER) RETURN VARCHAR2 IS
BEGIN
   RETURN NULL;
END;

The NULL statement gives you a way to quickly cobble together the programmatic interface you need to formulate the functional hierarchy of your application. I'd like to say that this is half the battle, but I am not really sure that is so. You still have to figure out how to fill in all of those stubs.

5.2.2.4 Using NULL with GOTO to avoid additional statement execution

In some cases, you can pair NULL with GOTO to avoid having to execute additional statements. Most of you will never have to use the GOTO statement; there are very few occasions where it is truly needed. If you ever do use GOTO, however, you should remember that when you GOTO a label, at least one executable statement must follow that label. In the following example, I use a GOTO statement to quickly move to the end of my program if the state of my data indicates that no further processing is required:

PROCEDURE process_data (data_in IN orders%ROWTYPE,
                        data_action IN VARCHAR2) IS
BEGIN
   -- First in series of validations.
   IF data_in.ship_date IS NOT NULL
   THEN
      status := validate_shipdate (data_in.ship_date);
      IF status != 0 THEN GOTO end_of_procedure;
   END IF;

   -- Second in series of validations.
   IF data_in.order_date IS NOT NULL
   THEN
      status := validate_orderdate (data_in.order_date);
      IF status != 0 THEN GOTO end_of_procedure;
   END IF;

   ... more validations ...

   << end_of_procedure >>
   NULL;
END;

With this approach, if I encounter an error in any single section, I use the GOTO to bypass all remaining validation checks. Because I do not have to do anything at the termination of the procedure, I place a NULL statement after the label because at least one statement is required there.