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: [=, >, <, >=, <=, <>, !=, ~=].
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]
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 -- 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.
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.
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.
<<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.
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.
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.
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
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?
BEGIN ... <<update_row>> BEGIN UPDATE emp SET ... ... END; ... GOTO update_row; ... END;
BEGIN ... GOTO insert_row; ... <<insert_row>> INSERT INTO emp VALUES ... END;
- 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.
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.
A very similar statement with selector CASE is this. Each case use a conditional expression.
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.