Lesson 1

Writing Control Structures in PL/SQL

Now that you know how to build your first PL/SQL block, let us learn how to write control structures. Control structures allow you to program your logic based on a certain condition or while a particular condition is satisfied. In this module, you will learn about conditional control statements such as IF-THEN, as well as iterative control statements such as LOOP and WHILE-LOOP.

Module Objectives
By the end of this module, you will know how to:
1. Build logic by using the IF-THEN-ELSE and ELSIF statements
2. Determine how to begin and end a simple loop in a PL/SQL block
3. Observe the structure of FOR and WHILE loops
4. Define uses for labels and GOTO statements

Conditional Control Statements

Here is a list of conditional and loop control structures in **Oracle PL/SQL**:

IF-THEN
Executes a block of statements if the condition is true.
```
IF condition THEN
    -- statements
END IF;
    
```
IF-THEN-ELSE
Executes one block if the condition is true, otherwise executes another block.
```
IF condition THEN
    -- statements
ELSE
    -- alternative statements
END IF;
    
```

IF-THEN-ELSIF
Provides multiple conditional checks.

IF condition1 THEN
    -- statements
ELSIF condition2 THEN
    -- statements
ELSE
    -- alternative statements
END IF;

CASE
Similar to a switch-case in other programming languages. It can be simple or searched:

Simple CASE:

CASE expression
    WHEN value1 THEN
        -- statements
    WHEN value2 THEN
        -- statements
    ELSE
        -- alternative statements
END CASE;

Searched CASE:

CASE
    WHEN condition1 THEN
        -- statements
    WHEN condition2 THEN
        -- statements
    ELSE
        -- alternative statements
END CASE;

LOOP
Executes a block of statements repeatedly until explicitly terminated using EXIT.
```
LOOP
    -- statements
    EXIT WHEN condition;
END LOOP;
    
```
WHILE LOOP
Repeats a block of statements while a condition is true.
```
WHILE condition LOOP
    -- statements
END LOOP;
    
```

FOR LOOP
Iterates over a range of values.

FOR counter IN start_value..end_value LOOP
    -- statements
END LOOP;

FOR LOOP with REVERSE
Iterates over a range of values in reverse order.

FOR counter IN REVERSE start_value..end_value LOOP
    -- statements
END LOOP;

CURSOR FOR LOOP
Iterates over rows returned by a cursor.

FOR record IN cursor_name LOOP
    -- statements
END LOOP;

GOTO
Transfers control to a labeled statement.

GOTO label;
<<label>>
-- statements

EXIT
Terminates a loop when a condition is met.
```
EXIT;
EXIT WHEN condition;
    
```
CONTINUE
Skips the current iteration and proceeds to the next iteration.
```
CONTINUE;
CONTINUE WHEN condition;
    
```

These control structures provide flexibility in implementing complex logic in Oracle PL/SQL.

Oracle PL/SQL Programming

Simple CASE Expression

A simple CASE expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a subprogram call. The CASE expression uses a selector, an expression whose value determines which alternative to return. A CASE expression has the form illustrated in Example 4 show below. The selector (grade) is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first WHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. If there are no matches, then the optional ELSE clause is performed.
Example 4: Using the WHEN Clause with a CASE Statement

SQL> DECLARE
2 grade CHAR(1) := 'B';
3 appraisal VARCHAR2(20);
4 BEGIN
5 appraisal :=
6 CASE grade
7 WHEN 'A' THEN 'Excellent'
8 WHEN 'B' THEN 'Very Good'
9 WHEN 'C' THEN 'Good'
10 WHEN 'D' THEN 'Fair'
11 WHEN 'F' THEN 'Poor'
12 ELSE 'No such grade'
13 END;
14 DBMS_OUTPUT.PUT_LINE
15 ('Grade ' || grade || ' is ' || appraisal);
16 END;
17 /
Grade B is Very Good
PL/SQL procedure successfully completed.

In the next lesson, we will begin by building logic using IF-THEN-ELSE statements.

January 20, 2025