Oracle PL/SQL Programming, 5th Edition

The NULL Statement

Usually when you write a statement in a program, you want it to do something. There are cases, however, when you want to tell PL/SQL to do absolutely nothing, and that is where the NULL statement comes in handy. The NULL statement has the following format:

NULL;

Well, you wouldn’t want a do-nothing statement to be complicated, would you? The NULL statement is simply the reserved word NULL followed by a semicolon (;) to indicate that this is a statement and not a NULL value. The NULL statement does nothing except pass control to the next executable statement.

Why would you want to use the NULL statement? There are several reasons, described in the following sections.

Improving Program Readability

Sometimes, it’s helpful to avoid any ambiguity inherent in an IF statement that doesn’t cover all possible cases. For example, 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_mgr.selection = 'DETAIL'
THEN
   exec_detail_report;
END IF;

What should the program be doing if the report selection is not ‘DETAIL'? One might assume that the program is supposed to do nothing. But because this is not explicitly stated in the code, you are 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_mgr.selection = 'DETAIL'
THEN
   exec_detail_report;
ELSE
   NULL; -- Do nothing
END IF;

My example here was of an IF statement, but the same principle applies when writing CASE statements and CASE expressions. Similarly, if you want to temporarily remove all the code from a function or procedure, and yet still invoke that function or procedure, you can use NULL as a placeholder. Otherwise, you cannot compile a function or procedure without having any lines of code within it.

Using NULL After a Label

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
   status INTEGER;
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;
   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;
   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 executable statement is required there. Even though NULL does nothing, it is still an executable statement.