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.
