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.
