Chapter 4. Conditional and Sequential Control

This chapter describes two types of PL/SQL control statements: conditional control statements and sequential control statements. Almost every piece of code you write will require conditional control, which is 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 (CASE statements are new in Oracle9i). There are also CASE expressions; while not the same as CASE statements, they can sometimes be used to eliminate the need for an IF or CASE statement altogether. Far less often, you will need to tell PL/SQL to transfer control unconditionally via the GOTO statement, or explicitly to do nothing via the NULL statement.

4.1 IF Statements

In your programs, you need to be able to implement requirements such as:

If the salary is between ten and twenty thousand, then apply a bonus of $1500. If the salary is between twenty and forty thousand, apply a bonus of $1000. If the salary is over forty thousand, give the employee a bonus of $500.

or:

If the user preference includes the toolbar, display the toolbar when the window first opens.

The IF statement allows you to design conditional logic into your programs. The IF statement comes in three flavors, as shown in the following table:

IF type	Characteristics
IFTHENEND IF;	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, the code is not executed.
IFTHENELSEEND IF;	This combination implements an 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 executable statements is performed.
IFTHENELSIFELSEEND IF;	This last and most complex form of the IF statement selects an action 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 Oracle9i, you should consider using searched CASE statements instead.