Part II: pl/sql Program Structure

This part of the book presents the basic PL/SQL programming elements and statement constructs. Chapters 4 through 6 describe conditional (IF and CASE) and sequential control statements (e.g., GOTO and NULL), loops, and exception handling in the PL/SQL language. When you complete this section of the book you will know how to construct blocks of code that correlate to the complex requirements in your applications.

Chapter 4

Chapter 5

Chapter 6

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
IF THEN END 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.
IF THEN ELSE END 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.
IF THEN ELSIF ELSE END 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.

4.1.1 The if-then Combination

The general format of the IF-THEN syntax is as follows:

IF condition

THEN

... sequence of executable statements ...

END IF;

The condition is a Boolean variable, constant, or expression that evaluates to TRUE, FALSE, or NULL. If condition evaluates to TRUE, then the executable statements found after the THEN keyword and before the matching END IF statement are executed. If condition evaluates to FALSE or NULL, those statements are not executed.

The following IF condition compares two different numeric values. Remember that if one of these two values is NULL, then the entire expression returns NULL; in the following example, the bonus is not given:

IF salary > 40000

THEN

give_bonus (employee_id,500);

END IF;

It's not necessary to put the IF, THEN, and END IF keywords on their own lines. In fact, line breaks don't matter at all for any type of IF statement. We could just as easily write:

IF salary > 40000 THEN give_bonus (employee_id,500); END IF;

Putting everything on one line is perfectly fine for simple IF statements such as the one shown here. However, when writing IF statements of any complexity at all, you'll find that readability is much greater when you format the statement such that each keyword begins a new line. For example, the following code would be very difficult to follow if it were all crammed on a single line. Actually, it's difficult to follow as it appears on three lines:

IF salary > 40000 THEN INSERT INTO employee_bonus (eb_employee_id, eb_bonus_amt)

VALUES (employee_id, 500); UPDATE emp_employee SET emp_bonus_given=1 WHERE emp_

employee_id=employee_id; END IF;

Ugh! Who'd want to spend time figuring that out? It's much more readable when formatted nicely:

IF salary > 40000

THEN

INSERT INTO employee_bonus

(eb_employee_id, eb_bonus_amt)

VALUES (employee_id, 500);

UPDATE emp_employee

SET emp_bonus_given=1

WHERE emp_employee_id=employee_id;

END IF;

This readability issue becomes even more important when using the ELSE and ELSIF keywords, and when nesting one IF statement inside the other. Take full advantage of indents and formatting to make the logic of your IF statements easily decipherable. Future maintenance programmers will thank you.