Controlling the Flow
of Execution
Part 2 in a series of articles on understanding
and using PL/SQL
ORACLE DATABASE
To help newcomers to PL/SQL make the most of this language, Oracle Magazine
has asked me to write a series of articles
for PL/SQL beginners, of which this is the
second. If you are an experienced PL/SQL
developer, you may find these articles a
handy refresher on PL/SQL fundamentals.
I will assume for this series that although
the readers are new to PL/SQL, they have
had some programming experience and are
familiar with SQL. My approach throughout,
in addition, will be to get you productive in
PL/SQL as quickly as possible.
There are also CASE expressions; although
not the same as CASE statements, they can
sometimes be used to eliminate the need for
an IF or CASE statement altogether.
IF. The IF statement enables you to imple-
ment conditional branching logic in your
programs. With it, you’ll be able to imple-
ment requirements such as the following:
If the collection contains more than 100 •
elements, truncate it.
The IF statement comes in three flavors,
as shown in Table 1. Let’s take a look at some
examples of IF statements.
IF-THEN. The following statement compares
two numeric values. Note that if one of these
two values is NULL, the entire expression will
return NULL. In the following example, the
bonus is not given when salary is NULL:
not NULL) results. For example, you can use
IS NULL to test for the presence of a NULL:
IF l_salary > 40000 OR l_salary
IS NULL
THEN
give_bonus (l_employee_id,500);
END IF;
PL/SQL: AT YOUR COMMAND
There is one way PL/SQL is just like every
other programming language you will ever
use: it (the PL/SQL runtime engine) does
only exactly what you tell it to do. Each block
of PL/SQL code you write contains one or
more statements implementing complex
business rules or processes. When you
run that block of code, as either an anonymous block or a script or by calling a stored
program unit that contains all the logic,
Oracle Database follows the directions you
specify in that block.
It is therefore critical to know how to
specify which statements should be run,
under what circumstances, and with what
frequency. To do this, Oracle Database offers
conditional and iterative constructs. This
article introduces you to the IF statement,
the CASE statement and expression, and the
various types of loops PL/SQL supports.
In this example, “salary IS NULL” evaluates to TRUE in the event that salary has no
value and otherwise to FALSE. Employees
whose salaries are missing will now get
bonuses too. (As indeed they probably
should, considering that their employer was
so inconsiderate as to lose track of their pay
in the first place.)
IF-THEN-ELSE. Here is an example of the
IF-THEN-ELSE construct (which builds upon
the IF-THEN construct):
IF l_salary > 40000
THEN
give_bonus (l_employee_id,500);
END IF;
There are exceptions to the rule that a
NULL in a Boolean expression leads to a
NULL result. Some operators and functions
are specifically designed to deal with NULLs
in a way that leads to TRUE and FALSE (and
IF l_salary <= 40000
THEN
give_bonus (l_employee_id, 0);
ELSE
give_bonus (l_employee_id, 500);
END IF;
In this example, employees with a salary
greater than $40,000 will get a bonus of
$500, whereas all other employees will get
CONDI TIONAL BRANCHING IN CODE
Almost every piece of code you write will
require conditional control, the ability to
direct the flow of execution through your
program, based on a condition. You do this
with IF-THEN-ELSE and CASE statements.
Characteristics
This is the simplest form of the IF statement. The condition between IF and THEN determines
whether the set of statements between THEN and END IF should be executed. If the condition
evaluates to FALSE or NULL, the code will not be executed.
IF THEN ELSE END IF; This combination implements either/or logic: based on the condition between the IF and THEN
keywords, execute the code either between THEN and ELSE or between ELSE and END IF. One of
these two sections of statements is executed.
IF THEN ELSIF ELSE END IF; This last and most complex form of the IF statement selects a condition that is TRUE from a series
of mutually exclusive conditions and then executes the set of statements associated with that
condition. If you’re writing IF statements like this in any Oracle Database release from Oracle9i
Database Release 1 onward, you should consider using searched CASE statements instead.
Table 1: IF statement flavors
LINDY GROENING
IF Type
IF THEN END IF;
