The IF-THEN-ELSE Combination

Use the IF-THEN-ELSE format when you want to choose between two mutually exclusive actions. The format of this either/or version of the IF statement is as follows:

IF conditionTHEN ... TRUE sequence of executable statements ...ELSE ... FALSE/NULL sequence of executable statements ...END IF;

The condition is a Boolean variable, constant, or expression. If condition evaluates to TRUE, then the executable statements found after the THEN keyword and before the ELSE keyword are executed (the "TRUE sequence of executable statements"). If condition evaluates to FALSE or NULL, then the executable statements that come after the ELSE keyword and before the matching END IF keywords are executed (the "FALSE/NULL sequence of executable statements").

Notice that the ELSE clause does not have a THEN associated with it.

The important thing to remember is that one of the two sequences of statements will always execute, because IF-THEN-ELSE is an either/or construct. Once the appropriate set of statements has been executed, control passes to the statement immediately following the END IF keyword.

Following is an example of the IF-THEN-ELSE construct that builds upon the IF-THEN example shown in the previous section:

IF salary <= 40000THEN give_bonus (employee_id, 0);ELSE give_bonus (employee_id, 500);END IF;

In this example, employees with a salary greater than 40,000 will get a bonus of 500 while all other employees will get no bonus at all. Or will they? What happens if salary, for whatever reason, happens to be NULL for a given employee? In that case, the statements following the ELSE will be executed, and the employee in question will get the bonus that is supposed to go only to highly paid employees. That's not good! If we're not sure that salary will never be NULL, we can protect ourselves against this problem using the NVL function:

IF NVL(salary,0) <= 40000THEN give_bonus (employee_id, 0);ELSE give_bonus (employee_id, 500);END IF;

The NVL function will return zero any time salary is NULL, ensuring that any employees with a NULL salary also get a zero bonus.

Using Boolean Flags Often, it's convenient to use Boolean variables as flags so that we don't need to evaluate the same Boolean expression more than once. When doing so, remember that the result of a Boolean expression can be assigned directly to a Boolean variable. For example, rather than write: IF :customer.order_total > max_allowable_orderTHEN order_exceeds_balance := TRUE;ELSE order_exceeds_balance := FALSE;END IF; we can instead write the following, much simpler, expression: order_exceeds_balance := :customer.order_total > max_allowable_order; Now, whenever we need to test in our code whether an order's total exceeded the maximum, we can write the following, easily understandable, IF statement: IF order_exceeds_balanceTHEN... If you have not had much experience with Boolean variables, it may take you a little while to learn how to integrate them smoothly into your code. It is worth the effort, though. The result is cleaner, more readable code.