Control Structures in PL SQL

This chapter shows you how to structure the flow of control through a PL/SQL program. You learn how statements are connected by simple but powerful control structures that have a single entry and exit point. Collectively, these structures can handle any situation. Their proper use leads naturally to a well-structured program.
IF Statements
Often, it is necessary to take alternative actions depending on circumstances. The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

2. LOOP
FETCH c1 INTO …
EXIT WHEN c1%NOTFOUND; — exit loop if condition is true
…
END LOOP;
CLOSE c1;
Loop Labels
Like PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement, as follows:
<<label_name>>
LOOP
sequence_of_statements
END LOOP;
Optionally, the label name can also appear at the end of the LOOP statement, as the
following example shows:
<<my_loop>>
LOOP
…
END LOOP my_loop;
When you nest labeled loops, you can use ending label names to improve readability.
With either form of EXIT statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement, as follows:
<<outer>>
LOOP
…
LOOP
…
EXIT outer WHEN … — exit both loops
END LOOP;
…
END LOOP outer;
Every enclosing loop up to and including the labeled loop is exited.

WHILE-LOOP
The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as follows:

The number of iterations depends on the condition and is unknown until the loop completes. The condition is tested at the top of the loop, so the sequence might execute zero times. In the last example, if the initial value of total is larger than
25000, the condition is false and the loop is bypassed.
FOR-LOOP
Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. (Cursor FOR loops iterate over the result set of a cursor, are discussed in later section) The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double dot (..) serves as the range operator. The syntax follows:

Dynamic Ranges
PL/SQL lets you determine the loop range dynamically at run time, as the following example shows:
SELECT COUNT(empno) INTO emp_count FROM emp;
FOR i IN 1..emp_count LOOP
…
END LOOP;
The value of emp_count is unknown at compile time; the SELECT statement returns the value at run time.
Using the EXIT Statement
The EXIT statement allows a FOR loop to complete prematurely. For example, the following loop normally executes ten times, but as soon as the FETCH statement fails to return a row, the loop completes no matter how many times it has executed:
FOR j IN 1..10 LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
…
END LOOP;
Suppose you must exit from a nested FOR loop prematurely. You can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement to specify which
FOR loop to exit, as follows:
<<outer>>
FOR i IN 1..5 LOOP
…
FOR j IN 1..10 LOOP
FETCH c1 INTO emp_rec;
EXIT outer WHEN c1%NOTFOUND; — exit both FOR loops
…
END LOOP;
END LOOP outer;
— control passes here

NULL Statement
The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. It can, however, improve readability. In a construct allowing alternative actions, the NULL statement serves as a placeholder. It tells
readers that the associated alternative has not been overlooked, but that indeed no action is necessary. In the following example, the NULL statement shows that no action is taken for unnamed exceptions:
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN VALUE_ERROR THEN
INSERT INTO errors VALUES …
COMMIT;
WHEN OTHERS THEN
NULL;
END;

Also, the NULL statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that allows you to defer the definition of a procedure or function until you test and debug the main program. In the following example, the NULL statement meets the requirement that at least one statement must appear in the executable part of a subprogram:
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
BEGIN
NULL;
END debit_account;