The IF-THEN-ELSIF Combination

This last form of the IF statement comes in handy when you have to implement logic that has many alternatives; it is not an either/or situation. The IF-ELSIF formulation provides a way to handle multiple conditions within a single IF statement. In general, you should use ELSIF with mutually exclusive alternatives (i.e., only one condition can be TRUE for any execution of the IF statement). The general format for this variation of IF is:

IF condition-1THEN statements-1ELSIF condition-NTHEN statements-N[ELSE else_statements]END IF;
Be very careful to use ELSIF, not ELSEIF. The inadvertent use of ELSEIF is a fairly common syntax error. ELSE IF (two words) doesn't work either.

 

 

Logically speaking, the IF-THEN-ELSIF construct is one way of implementing CASE statement functionality in PL/SQL. Of course, if you are using Oracle9i or higher, you are probably better off actually using a CASE statement (discussed later in this chapter).

NULLs in IF Statements Be very cognizant of the effect of NULL values in IF expressions. One NULL value in an expression will usually cause the entire expression to be NULL, and a NULL is neither TRUE nor FALSE. For example, while seemingly equivalent, the following two IF statements do not have the same effect: IF x = 2 THEN DBMS_OUTPUT.PUT_LINE('x contains 2');ELSE DBMS_OUTPUT.PUT_LINE('x doesn''t contain 2');END IF; IF x <> 2 THEN DBMS_OUTPUT.PUT_LINE('x doesn''t contain 2');ELSE DBMS_OUTPUT.PUT_LINE('x contains 2');END IF; The difference between these two statements lies in what happens when the variable x is NULL. When x is NULL, the first IF-THEN-ELSE statement will display "x doesn't contain 2"; the second statement will display "x contains 2". Why? Because when x is NULL, the IF expression is not considered to be TRUE, and control passes to the ELSE clause. Protect yourself from inadvertent NULL-related bugs by using functions such as NVL or by explicitly checking for the NULL case using the IS NULL predicate.

 

Each ELSIF clause must have a THEN after its condition. Only the ELSE keyword does not need the THEN keyword. The ELSE clause in the IF-ELSIF is the "otherwise" of the statement. If none of the conditions evaluate to TRUE, then the statements in the ELSE clause are executed. But the ELSE clause is optional. You can code an IF-ELSIF that has only IF and ELSIF clauses. In this case, if none of the conditions are TRUE, then no statements inside the IF block are executed.

Following is an implementation of the complete bonus logic described at the beginning of this chapter using the IF-THEN-ELSEIF combination:

IF salary BETWEEN 10000 AND 20000THEN give_bonus(employee_id, 1500);ELSIF salary BETWEEN 20000 AND 40000THEN give_bonus(employee_id, 1000);ELSIF salary > 40000THEN give_bonus(employee_id, 500);ELSE give_bonus(employee_id, 0);END IF;

The conditions in the IF-ELSIF are always evaluated in the order of first condition to last condition. If two conditions evaluate to true, the statements for the first such condition are executed. With respect to the current example, a salary of 20,000 will result in a bonus of 1500 even though that 20,000 salary also satisfies the condition for a 1000 bonus (BETWEEN is inclusive). Once a condition evaluates to TRUE, the remaining conditions are not evaluated at all.

The CASE statement available beginning in Oracle9i represents a better solution to the bonus problem than the IF-THEN-ELSIF solution shown in this section. See the upcoming section Section 4.2.

 

 

Even though overlapping conditions are allowed in an IF-THEN-ELSIF statement, it's best to avoid them when possible. In our case, the original spec is a bit ambiguous about how to handle boundary cases such as 20,000. Assuming that the intent is to give the highest bonuses to the lowest-paid employees (which seems like a reasonable approach to us), we would dispense with the BETWEEN operator and use the following less-than/greater-than logic. Note that we've also dispensed with the ELSE clause just to illustrate that it is optional:

IF salary >= 10000 AND salary <= 20000THEN give_bonus(employee_id, 1500);ELSIF salary > 20000 AND salary <= 40000THEN give_bonus(employee_id, 1000);ELSIF salary > 40000THEN give_bonus(employee_id, 400);END IF;

By taking steps to avoid overlapping conditions in an IF-THEN-ELSIF, we are eliminating a possible (probable?) source of confusion for programmers who come after us. We also eliminate the possibility of inadvertent bugs being introduced as a result of someone's reordering the ELSIF clauses.

The language does not require that ELSIF conditions be mutually exclusive. Always be aware of the possibility that two or more conditions might apply to a given value, and that consequently the order of those ELSIF conditions might be important.