div class=”well”>Control flow have synonyms: control structure or structured statements. 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.

Define Condition

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:

[before statements]
IF <condition> THEN
   [then - true statements]
ELSE
   [else - false statements]
END IF;
[after statements]

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.

[before statements]
IF <condition1> THEN
   [then - true statements]
ELSIF <codnition2> THEN
 [then - true statements] 
ELSIF <codnition3> THEN
 [then - true statements] 
...
[ELSE]
 [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 <condition> LOOP
   <sequence_of_statements>
   ...
END 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.

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
      INSERT INTO errors VALUES ...
      COMMIT;
   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.