PL/SQL Control Structures

In any computer program, a condition is a variable or expression 
that returns a Boolean value (TRUE or FALSE). Generally, we have three controls 
structures:
- The selection structure that tests a condition, and then executes one 
sequence of statements instead of another, depending on whether the condition is 
true or false, 
- The iteration structure that  executes a sequence of statements 
repeatedly as long as a condition is true. 
- The sequence structure that  simply executes a sequence of statements 
in the order in which they occur.

1. IF and CASE statements

The IF statement executes a sequence of statements depending  on the 
value of a condition (true or false). There are three forms of IF statements: 
IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. The CASE statement is 
used when the condition takes several cases; each  corresponds to its related action.

1.1. IF-THEN Statement

The sytax is:
IF condition THEN
   // the sequence of statements goes here ..
END IF;

The sequence of statements is executed only if the condition is true, 
otherwise the IF statement does nothing. 

1.2. IF-THEN-ELSE Statement

Here, the keyword ELSE is added for an alternative sequense of statements. 
followed by an alternative sequence of statements. The syntax is:
IF condition THEN
   // the sequence of statements 1 goes here ..
ELSE
   // the sequence of statements 2 goes here ..
END IF;

Here, in the case where the condition is false or null, "the sequence of 
statements 2" is executed. The clauses THEN and ELSE can include IF 
statements. 

1.3. IF-THEN-ELSIF Statement

The syntax is:

IF condition1 THEN
  // the sequence of statements 1 goes here ..
ELSIF condition2 THEN
 // the sequence of statements 2 goes here ..
ELSE
   sequence_of_statements3
END IF;

Here a second condition is introduced. The ELSIF clause tests another 
condition (condition2), If the first condition is false or null. We can 
have several ELSIF clauses following the first IF. The ELSE clause is 
optional and executed when all the conditions are false or null.

1.4. CASE Statement

The CASE statement replaces the IF-THEN-ELSIF Statement containinig several 
ELSIF's. To select and execute one sequence of statements, The CASE 
statement uses a selector (that is an expression whose value is 
used to select one of several alternatives. It could be also a function call). 
The syntax is:

<<label_name>>
CASE selector
WHEN expression1 THEN sequence_of_statements_1;
WHEN expression2 THEN sequence_of_statements_2;
...
WHEN expressionN THEN sequence_of_statements_N;
ELSE sequence_of_statements_N+1;
END CASE label_name;

The label is optional. The ELSE clause is optional. It is a default action. 
When omitted, and all the WHEN clauses fail, PL/SQL will add 
"ELSE RAISE CASE_NOT_FOUND;" which is a predefined exception, and therefore 
outputs "CASE_NOT_FOUND".

1.5. Searched CASE Statement

In the searched CASE statement, we have no selector. It is 
included in the search_condition expression. It yiels a boolean value, 
that determine which WHEN clause is executed.

<<label_name>>
CASE
   WHEN search_condition1 THEN sequence_of_statements_1;
   WHEN search_condition2 THEN sequence_of_statements_2;
   ...
   WHEN search_conditionN THEN sequence_of_statements_N;
ELSE sequence_of_statements_N+1;
END CASE label_name;

The expression "search_condition_x" could be as follows:
 WHEN selector = something  THEN do_some_thing .. ;

2. Iterative Control: LOOP and EXIT Statements

LOOP statements executes a sequence of statements several times. 
There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.

2.1. LOOP

The syntax is:
LOOP
// The sequence of statements go here .. 
END LOOP;

For each iteration of the loop, the sequence of statements is executed; then 
control start again from the top till the end of iterations. If further 
processing is not desirable, we use an EXIT statement to complete the loop. 
We can place any number of EXIT statement inside a loop. There are two forms of 
EXIT statements: EXIT and EXIT-WHEN.

2.2. EXIT

The EXIT statement stops a loop immediately. It is not used in PL/SQL blocks. In 
this case use RETURN instead.
Example:
LOOP
...
IF condition .. 
...
EXIT; 
END IF;
END LOOP;

2.3. EXIT-WHEN

The EXIT-WHEN statement states a condition to be satisfied in order to stop 
the loop.
Example:
LOOP
...
EXIT WHEN condition  
// the loop is left if condition is true
...
END LOOP;

Loops can be labeled:
<<label_name>>
LOOP
sequence of statements ..
END LOOP label_name;
 
With the two forms of EXIT (EXIT or EXIT-WHEN) statement, we can complete the 
current loop and the loops that can be enclosed inside. In this case, we have 
to label the enclosing (the first) loop. This is an example:

<<label_name>>
LOOP
...
LOOP
...
EXIT label_name WHEN ...  
END LOOP;
...
END LOOP label_name;

The both loops are exited.

2.4. WHILE-LOOP

The syntax is:
WHILE condition LOOP
sequence of statements ..
END LOOP;

The condition is evaluated each iteration. If it is true, the sequence of 
statements is executed, then control restarts at the top of the loop. If the 
condition is false or null, the loop is left and control passes to the 
next statement.

Some languages have a LOOP UNTIL or REPEAT UNTIL structure, which tests the 
condition at the bottom of the loop instead of at the top. Therefore, the 
sequence of statements is executed at least once. PL/SQL has no 
such structure, but you can easily build one, as follows:

2.5. FOR-LOOP

FOR loops iterate over a specified range of integers. We use double dot (..) ti 
indicate the range operator. The syntax is:
FOR number IN range LOOP
sequence of statements .. 
END LOOP;


After each iteration, the loop number is incremented. Iteration can be 
processed  downward; in this case, we use IN REVERSE keyword instead of IN 
alone. When we have enclosing loops, to specify each integer we mean, we 
label the loops and use dot notation as this example shows:
<<label_name>>
FOR number  IN 1..6 LOOP
FOR number IN 1..3 LOOP
...
IF label_name.number ...  THEN ...
END LOOP;
END LOOP label_name;

We can use  the EXIT Statement to quit a loop. For example:
FOR j IN 1..7 LOOP
FETCH custom_number INTO custom_table;
EXIT WHEN custom_number%NOTFOUND;
...
END LOOP;

In the case that we have enclosing loops, we have to specify which loop 
to exit; for example if we want to quit the two loops at the same time, 
we write: 
<<label_name>>
FOR i IN 1..7 LOOP
...
FOR k IN 1..10 LOOP
FETCH custom_number  INTO custom_table;
EXIT label_name WHEN custom_number %NOTFOUND; 
...
END LOOP;
END LOOP label_name;