, , ,

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.


Iterative Control: LOOP and EXIT Statements

LOOP statements let you execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.
Examples:
1. LOOP

IF credit_rating < 3 THEN
EXIT; — exit loop immediately
END IF;

END LOOP;
— control resumes here

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:

Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

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:

 
The range is evaluated when the FOR loop is first entered and is never re-evaluated.
As the next example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.
FOR i IN 1..3 LOOP — assign the values 1,2,3 to i
sequence_of_statements — executes three times
END LOOP;

The following example shows that if the lower bound equals the higher bound, the sequence of statements is executed once:
FOR i IN 3..3 LOOP — assign the value 3 to i
sequence_of_statements — executes one time
END LOOP;
By default, iteration proceeds upward from the lower bound to the higher bound. However, as the example below shows, if you use the keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound. After each
iteration, the loop counter is decremented.
FOR i IN REVERSE 1..3 LOOP — assign the values 3,2,1 to i
sequence_of_statements — executes three times
END LOOP;

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;


Each clause in an IF statement must contain at least one executable statement. The NULL statement is executable, so you can use it in clauses that correspond to circumstances in which no action is taken. In the following example, the NULL
statement emphasizes that only top-rated employees get bonuses:
IF rating > 90 THEN
compute_bonus(emp_id);
ELSE
NULL;
END IF;

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;

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply