PL/SQL Control

index<--

PL/SQL Flow Control

The secret is to build a logical system that appear to be intelligent and capable to resolve problems. This is what give power to imperative programming. Once you understand the principles of control flow you can start writing programs

Conditional Expression

Before we present the control flow for PL/SQL we will remind you some simple principles of Logic that can be used to create intelligent statements. The idea of Logic is that we can make a deduction based on arguments. The Logic is implemented in computer languages using Boolean algebra. This is a mathematics science that handle two values: values true=1 and false=0. The maybe value is not a Boolean value.

In PL/SQL there are two values defined: TRUE and FALSE. A condition is a logical expression that have as result true or false. To create a valid condition we can combine variables, constants, numbers, strings and even objects using logical operators: [AND, OR, NOT] and relation relation operators: [=, >, <, >=, <=, <>, !=, ~=].

Decision Statement

The decision statement is the most easy to understand and is one of the most used control statement. This statement is based on a single logical expression and two regions. The true region and the false region. All statements in true region will be executed when the condition is true and the other region is executed when the condition is false.

decision diagram

Conditional Branching

In the picture above we have a classic structured programming scheme. The picture show the [before] statements as a block then the decision statement "if condition" then two path. The true path on the left and the false path on the right. So after decision is made one block is executed: "then" or "else" block. After this the logical thread continue with the common block [after].

Syntax for IF statement (with two branches):

[before statements]
if <condition> then
   -- statements for true branch
   ...
else
   -- statements for false branch
   ...
end if;
[after statements]

Syntax for IF statement (with one branch):

[before statements]
if <condition> then
 -- statements for condition == true
end if;
[after statements]

Ladder Statement

What if we have a series of conditions?. One sollution is to use nested IF statements. This is perfectly alright but there is a better alternative. We can use ELSIF keyword to make a "cascade" of conditions so called ladder statement. In the next picture you can see the workflow looks like a ladder.

Decision Ladder

[before statements]
if <condition1> then
 -- true statements
elsif <codnition2> then
 -- true statements]
elsif <codnition3> then
 -- true statements
...
else
 -- false statements
end if;
[after statements]

Infinite loop

Now the second more important structure is the Loop. This is a repetition of the same statement over and over forever. The infinite loop must be avoided in computer programming to avoid program being stack. This can be a defect in program when there is no exit point and the program do not give any result back. We can force a program to stop by killing the process. This is the worst programming error a developer can do.

Loop syntax:

loop
 -- sequence_of_statements
 ...
end loop;

This is basically the structure of the loop but is never used in practice. We need to create an exit condition to be able to terminate the loop.

Exit point:

loop
 -- sequence_of_statements
 if <condition> then
 exit;
 end if;
end loop;
...

So in the previous example the <condition> is used to trigger the EXIT statement that will terminate the loop. The program will continue after the loop.

Conditional exit:

loop
 -- sequence_of_statements
 exit when <condition>;
end loop;

I think you guess what this will do. The if statement is no longer required. It is just a simplification of previous technique. So now we can create a conditional exit point. In other languages this is achieved by BREAK keyword. Not in PL/SQL.

Using labels:

<<outer>>
loop
 ...
 loop
 ...
 exit outer when <condition> ... -- exit both loops
 end loop;
 ...
end loop outer;

In the previous example we have two nested loops. We wish to exit from both loops when a condition is true. This require labeling the outer loop. Any loop can have a label. This can be also useful to end the loop when we have multiple nested loops.

While loop

This loop is designed to execute only if a condition is true. If the condition is false the loop block skip over and is never executed. This is different then the previous loop that execute at least once. On this loop we can have no exit point, or we can also have several exit points defined like in a normal loop.

while loop diagram

Syntax Pattern:

while <condition> loop
 <sequence_of_statements>
 ...
end loop;

Tip:Infinite loop is possible using the while loop. If the <condition> is TRUE all the time then the loop will never terminate.

For loop

This kind of loop execute one or more time for a limited number of times using a control variable. In the next example the control variable is "counter". This variable start with lower_bound and end with higher_bound. These are two integer numbers, and counter will be incremented automatically with 1 for every iteration.

for counter in [reverse] lower_bound..higher_bound loop
 <sequence_of_statements>
 ...
end loop;

The [REVERSE] keyword is optional can can be used for counter to be decremented -1 for each iteration. There is no optional [STEP] keyword. The increment is always 1 or -1.

Example:

declare
 TYPE DateList IS TABLE OF DATE INDEX BY BINARY_INTEGER;
 dates DateList;
 k CONSTANT INTEGER := 5; -- set new increment
begin
 for j in 1..3 loop
 dates(j*k) := SYSDATE; -- multiply loop counter by increment
 end loop;
 ...
end;

Sometimes we need to increment the control variable with a step. In previous example we simulate counting: [1,5,15].

Scope of Control Variable:

In PL/SQL the loop have it's own scope. The control variable is pls_integer and can't be accessed outside the loop.

for i in 1..10 loop
 ...
end loop;
sum := i - 1; -- not allowed

The NULL;

This is the most simple statement. It does absolutely nothing. Sometimes we need this when a block of code is not yet done.

exception
 when ZERO_DIVIDE then
 ROLLBACK;
 when VALUE_ERROR then
 ...
 when OTHERS then
 null;
end;

The GOTO statement

What ? Yes I know this statement is am abomination into a modern language. However the PL/SQL is not so new. This statement is available in combination with the <<label>>. However we have some restrictions to avoid stupid mistakes. For example we can't jump inside a loop or inside an IF statement. It will be problematic no?

Jump backword:

begin
 ...
 <<update_row>>
 begin
 UPDATE emp SET ...
 ...
 end;
 ...
 goto update_row;
 ...
end;

Jump forward:

begin
 ...
 goto insert_row;
 ...
 <<insert_row>>
 INSERT INTO emp VALUES ...
end;

Restrictions:

the label must mark an executable statement or NULL;
we can't jump inside an inner block;
we can't jump from outside the loop to inside a loop;
we can't jump inside one if statement from outside;

Usually the compiler will protect against these types of errors. However avoid to use GOTO statement. Most algorithms do not need it.

Selector CASE

This statement use one control variable that is "selector" in next fragment of code. This is like a radio switch that can have many positions but only one at one time. It all depends on the value of selector.

[<<label_name>>]
case selector
    when v1 then
        sequence_of_statements;
    when v2 then
        sequence_of_statements;
        ...
    when vN then
        sequence_of_statements;
    [else]
        sequence_of_statements;
end case [label_name];

The <<lable>> is optional.

Conditional CASE

A very similar statement with selector CASE is this. Each case use a conditional expression.

Example:

case
    when grade = 'A' THEN dbms_output.put_line('Excellent');
    when grade = 'B' THEN dbms_output.put_line('Very Good');
    when grade = 'C' THEN dbms_output.put_line('Good');
    when grade = 'D' THEN dbms_output.put_line('Fair');
    when grade = 'F' THEN dbms_output.put_line('Poor');
    else dbms_output.put_line('No such grade');
end case;

In the example above we could use grade as selector and ‘A','B','C'. … as values V1… Vn. But for demonstrative purpose we have used logical conditions instead.

Conclusion: Now you know how to control the logical workflow into a PL/SQL program.

Read next: Data Types