PL/SQL Control Structures

In computer programming, control structures manage the flow of a program's execution, determining the order in which statements are processed. Control structures are essential for any programming language, enabling branching, looping, and sequential control of statements. The three main types of control structures are:

• Selection: Execute different sequence of statements based on whether a given condition is true or false. 
• Iteration: Repeat a sequence of statements multiple times until a condition is met or for a specified number of iterations.
• Sequence: Alter the normal flow of execution in a more direct way.

 

Conditional Control

IF Statements

The IF statement is one way to represent conditional controls in PL/SQL. The basic syntax of the IF statement is as follows:

IF <condition 1> THEN
    --statements to execute if condition 1 is true
[ELSIF <condition 2> THEN
    --statements to execute if condition 1 is false and condition 2 is true]
[ELSE
    --statements to execute if all previous conditions are false]
END IF;

Here, condition can be any expression or variable that evaluates to a scalar Boolean value (true, false, or null). The simplest form of an IF statement, without ELSIF and ELSE clauses, executes the statements only if the provided condition is true; if the condition is false or null, it does nothing and control passes to the next statement. You can add the ELSIF clauses (not ELSEIF or ELSE IF) to introduce additional conditions that are checked if the first condition is not true. Statements under the ELSE clause will be executed, if all the previous conditions are not true. 

For example:

CASE Statements

The CASE statement provides an alternative approach to conditional branching in PL/SQL. Like IF statements, it selects which sequence of statements to execute based on the given condition. The basic syntax of the CASE statement is as follows:

CASE WHEN <condition 1> THEN
    -- statements to execute when condition 1 is true
     WHEN <condition 2> THEN
    -- statements to execute when condition 2 is true
     ...
     ELSE
    -- statements to execute if all previous conditions are not true
END CASE;

For example:

Aside: CASE Expressions

Unlike IF or CASE statements, CASE expressions compare a single expression, known as a selector, against multiple possible values; unlike IF or CASE statements, selector is evaluated only once.

To illustrate its typical usage, consider the following IF statement:

IF v_employee_performance = '5' THEN
    dbms_output.put_line('Outstanding!');
ELSIF v_employee_performance = '4' THEN
    dbms_output.put_line('Exceeds expectations!');
ELSIF v_employee_performance = '3' THEN
    dbms_output.put_line('Meets expectations!');
ELSIF v_employee_performance = '2' THEN
    dbms_output.put_line('Needs improvement...');
ELSE
    dbms_output.put_line('Unacceptable...');
END IF;

We see that there are four Boolean expressions; in each instance, Oracle tests if the v_employee_performance is equal to one of four values: '5', '4', '3', '2'. This can be rewritten using a CASE expression as follows:

CASE v_employee_performance
    WHEN '5' THEN dbms_output.put_line('Outstanding!');
    WHEN '4' THEN dbms_output.put_line('Exceeds expectations!');
    WHEN '3' THEN dbms_output.put_line('Meets expectations!');
    WHEN '2' THEN dbms_output.put_line('Needs improvement...');
    ELSE dbms_output.put_line('Unacceptable...');
END CASE;

Selector can be arbitrarily complex. For example, it can contain SQL function calls as follows:

Iterative Control

PL/SQL handles iterative execution of a given set of statements using the LOOP statements. The simplest form of a LOOP statement has the following syntax:

LOOP 
    -- statements to executes for each iteration
    EXIT WHEN <condition>;
END LOOP;

For each iteration of the loop, a sequence of statements is executed, after which control returns to the top of the loop. To avoid an infinite loop, an EXIT-WHEN statement can be used. When the EXIT statement is reached, the condition specified in the optional WHEN clause is evaluated. If the condition is true, the loop ends, and control moves to the next statement following the loop. If the WHEN clause is omitted, the iteration will be terminated as soon as the EXIT keyword is encountered:

The loop will continue to run until the condition becomes true. Thus, at least one statement within the loop must modify the condition's value to ensure the loop can eventually terminate. In the example shown above, the value of v_delta is updated for each iteration and if the condition based on the updated value satisfies the given condition. The loop moves to the next iteration only if the condition is false. Thus, it can eventually be terminated.

Including WHILE Clause to LOOP Statement

Before the LOOP keyword, you can include a WHILE clause to specify a condition for the loop. The condition is evaluated before each iteration. If the condition is true, the statements under the loop will be executed, and then control returns to the condition for the next evaluation. If the condition is false, the statements for the current iteration will not be executed, and control will pass to the next statement after the loop. 

The basic syntax for the WHILE-LOOP statement in PL/SQL is as follows:

WHILE <condition> LOOP 
    -- statements to executes if the condition is true
END LOOP;

For example, we can rewrite the previous example as follows:

The WHILE loop is particularly useful when the exact number of iterations is not known in advance, but you have a condition that determines whether the loop should continue. It allows you to repeatedly execute a block of code as long as the specified condition remains true.

You can also use the EXIT-WHEN statement inside the WHILE-LOOP statement. For example:

Including the FOR-IN Clause to LOOP Statement

A FOR loop is generally preferred over a WHILE loop in situations where the number of iterations is known ahead of time or can be easily determined. The basic syntax of the FOR-LOOP in PL/SQL is as follows:

FOR loop_variable IN [REVERSE] start_value..end_value
LOOP 
    -- statements to executes
END LOOP;

For example:

The REVERSE keyword is used to iterate the loop variable in descending order. Note that despite reversing the direction of iteration, the start_value and end_value of the loop range remain unchanged. For example:

Sequential Control

CONTINUE-WHEN Statements

In an iteration structure, CONTINUE-WHEN statements are used to skip the current iteration when a specified condition is true. Unlike the EXIT statement, which terminates the entire loop, the CONTINUE statement simply passes control back to the loop condition, allowing the loop to continue to the next iteration when the given condition is true. For example:

Observe that when the loop variable i is 3, 6, or 9, current iteration passes and continues to the next iteration.

GOTO Statement

The GOTO statement transfers control to the line of code specified by a label. For example:

Observe that when the loop variable i equals to 3, the control passes to the label << fourth >>. 

However, it is important to note that the excessive use of GOTO can lead to "spaghetti code," where the flow of control is tangled and complex. This can make the codebase harder to maintain and extend. Thus, it is generally advisable to avoid overusing GOTO.

NULL Statements

In PL/SQL, the NULL statement is a no-operation (NOP) statement that essentially does nothing. It serves as a placeholder where no action is required. This can be useful in situations where syntactical correctness is needed but no actual operation should be performed. For example: