Menu Close

Articles

PL/SQL: Control Flow

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.

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:

 

Now that is clarified we can say that only one block is actually required. The [then] block or single statement. The other block [else] is optional. So the ELSE keyword can be missing from this statement. So the if statement is used for simple decision. When we have multiple decisions to make we can use nested IF statements or ELSIF keyword.

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:

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:

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:

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:

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.

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.

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:

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.

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.

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:

Jump forward:

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.

The <<lable>> is optional.

Conditional CASE

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

Example:

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.