The PL/SQL NULL Statement

Sometimes PL/SQL requires a code statement when you may not have anything to execute.  Use of a nonsense statement is possible, but why have the server calculate or compare a value for no reason.  This is when you use the NULL statement . 

Remember that one of the requirements of the GOTO statement is that there is a least one line of code after the label.

SQL> declare
  2    n_num number := 5;
  3  begin
  4    goto then_clause;
  5    n_num := 3;
  6    <<then_clause>>
  7  end;
  8  /
end;
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

What if you want to end the code block?  You could add a “nonsense” code line.  A nonsense code line is a piece of code that never performs any action. 

Line 7 in the example below is a nonsense code example.

SQL> declare
  2    n_num number := 5;
  3  begin
  4    goto then_clause;
  5    n_num := 3;
  6    <<then_clause>>
  7    if 4=7 then n_num := 5; end if;
  8  end;
  9  / 

PL/SQL procedure successfully completed.

As we see, line 7 will never execute (4 will never equal 7) but the server will have to compare 4=7 every time it hits that statement.  It is better to just use the NULL statement .

SQL> declare
  2    n_num number := 5;
  3  begin
  4    goto then_clause;
  5    n_num := 3;
  6    <<then_clause>>
  7    null;
  8  end;
  9  / 

PL/SQL procedure successfully completed.

The NULL statement  can also be used to iteratively create a large section of code.  Many developers like to build complicated code segments by building the skeleton (or stems) and then expanding each section one piece at a time, compiling and testing at each step.

SQL> declare
  2    n_numb number := 4;
  3  begin
  4    -- check for valid value
  5    begin
  6      if n_numb < 0 AND n_numb > 10
  7        then goto invalid_number;
  8      end if;
  9    end;
 10    --  compute the requirements
 11    begin
 12    end;
 13    -- handle invalid numbers
 14    <<invalid_number>>
 15    begin
 16    end;
 17  end;
 18  /
  end;
  *
ERROR at line 12:
ORA-06550: line 12, column 3:
PLS-00103: Encountered the symbol "END" when expecting one of the following: …

The PL/SQL engine does not like the BEGIN and END statements on lines 11 and 12.  It will have the same problem with lines 15 and 16.  Use the NULL statement  to allow this partially completed code to compile and execute.

SQL> declare
  2    n_numb number := 4;
  3  begin
  4    -- check for valid value
  5    begin
  6      if n_numb < 0 AND n_numb > 10
  7        then goto invalid_number;
  8      end if;
  9    end;
 10    --  compute the requirements
 11    begin
 12      null;
 13    end;
 14    -- handle invalid numbers
 15    <<invalid_number>>
 16    begin
 17      null;
 18    end;
 19  end;
 20  /

Like the IF/THEN  statement, the LOOP is another common flow control structure that all programming languages implement.  PL/SQL implements three loop structures; the endless loop, the WHILE loop  and the FOR loop .  The loop boundaries are defined by the key words LOOP and END LOOP.  These never-ending WHILE loops are referred to as “indeterminate loops ” because they continue to loop forever until a condition causes the loop to exit.  This is in contrast to a FOR loop, a “determinate loop ” that executes for a specified number of iterations.