The IF statement allows you to implement conditional branching logic in your programs. With it, you’ll be able to implement requirements such as:
If the salary is between $10,000 and $20,000, apply a bonus of $1,500.
If the collection contains more than 100 elements, truncate it.
The IF statement comes in three flavors, as shown in the following table:
The general format of the IF-THEN syntax is as follows:
IFconditionTHEN... 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, 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 when salary is NULL:
IF salary > 40000 THEN give_bonus (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 not NULL) results. For example, you can use IS NULL to test for the presence of a NULL:
IF salary > 40000 OR salary IS NULL THEN give_bonus (employee_id,500); END IF;
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 their employer was so inconsiderate as to lose track of their pay in the first place.)
Note
Using operators such as IS NULL and IS NOT NULL, or functions such as COALESCE and NVL2, are good ways to detect and deal with potentially NULL values. For every variable that you reference in every Boolean expression that you write, be sure to think carefully about the consequences if that variable is NULL.
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. You 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.
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:
IFconditionTHEN... 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, 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, 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”).
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 <= 40000 THEN 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 (well, it was good in the last section, but not now)! If the salary could be NULL, you can protect yourself against this problem using the NVL function:
IF NVL(salary,0) <= 40000 THEN 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 (those poor employees).
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:
IFcondition-1THENstatements-1ELSIFcondition-NTHENstatements-N[ELSEelse_statements] END IF;
Warning
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 to implement CASE statement functionality in PL/SQL. Of course, if you are using Oracle9i Database onwards, you are probably better off actually using a CASE statement (discussed later in this chapter).
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, 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 such a case, if none of the conditions are TRUE, 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 20000 THEN give_bonus(employee_id, 1500); ELSIF salary BETWEEN 20000 AND 40000 THEN give_bonus(employee_id, 1000); ELSIF salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END IF;
Keep in mind these points about IF statement syntax:
- Always match up an IF with an END IF
In all three variations of the IF statement, you must close off the executable statements associated with the conditional structure with an END IF keyword.
- You must have a space between the keywords END and IF
If you type ENDIF instead of END IF, the compiler will get confused and give you the following hard-to-understand error messages:
ORA-06550: line 14, column 4: PLS-00103: Encountered the symbol ";" when expecting one of the following:
- The ELSIF keyword should not have an embedded “E”
If you type ELSEIF in place of ELSIF, the compiler will also get confused and not recognize the ELSEIF as part of the IF statement. Instead, the compiler will interpret ELSEIF as a variable or a procedure name.
- Place a semicolon (;) only after the END IF keywords
The keywords THEN, ELSE, and ELSIF should not have a semicolon after them. They are not standalone executable statements, and, unlike END IF, do not complete a statement. If you include a semicolon after these keywords, the compiler will issue messages indicating that it is looking for a statement of some kind before the semicolon.
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 $1,500 even though that $20,000 salary also satisfies the condition for a $1,000 bonus (BETWEEN is inclusive). Once a condition evaluates to TRUE, the remaining conditions are not evaluated at all.
The CASE statement represents a better solution to the bonus problem than the IF-THEN-ELSIF solution shown in this section. See CASE Statements and Expressions.
Even though overlapping conditions are allowed in an IF-THEN-ELSIF statement, it’s best to avoid them when possible. In my example, 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 me), I would dispense with the BETWEEN operator and use the following less-than/greater-than logic. Note that I’ve also dispensed with the ELSE clause just to illustrate that it is optional:
IF salary >= 10000 AND salary <= 20000 THEN give_bonus(employee_id, 1500); ELSIF salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000); ELSIF salary > 40000 THEN give_bonus(employee_id, 400); END IF;
By taking steps to avoid overlapping conditions in an IF-THEN-ELSIF, I am eliminating a possible (probable?) source of confusion for programmers who come after me. I also eliminate the possibility of inadvertent bugs being introduced as a result of someone’s reordering the ELSIF clauses. Note, though, that if salary is NULL, then no code will be executed, because there is no ELSE section.
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.
You can nest any IF statement within any other IF statement. The following IF statement shows several layers of nesting:
IFcondition1THEN IFcondition2THENstatements2ELSE IFcondition3THENstatements3ELSIFcondition4THENstatements4END IF; END IF; END IF;
Nested IF statements are often necessary to implement complex logic rules, but you should use them carefully. Nested IF statements, like nested loops, can be very difficult to understand and debug. If you find that you need to nest more than three levels deep in your conditional logic, you should review that logic and see if there is a simpler way to code the same requirement. If not, then consider creating one or more local modules to hide the innermost IF statements.
A key advantage of the nested IF structure is that it defers evaluation of inner conditions. The conditions of an inner IF statement are evaluated only if the condition for the outer IF statement that encloses them evaluates to TRUE. Therefore, one obvious reason to nest IF statements is to evaluate one condition only when another condition is TRUE. For example, in my code to award bonuses, I might write the following:
IF award_bonus(employee_id) THEN
IF print_check (employee_id) THEN
DBMS_OUTPUT.PUT_LINE('Check issued for ' || employee_id);
END IF;
END IF;This is reasonable, because I want to print a message for each bonus check issued, but I don’t want to print a bonus check for a zero amount in cases where no bonus was given.
PL/SQL uses short-circuit evaluation, which means that PL/SQL need not evaluate all of the expression in an IF statement. For example, when evaluating the expression in the following IF statement, PL/SQL stops evaluation and immediately executes the ELSE branch if the first operand is either FALSE or NULL:
IFcondition1ANDcondition2THEN ... ELSE ... END IF;
PL/SQL can stop evaluation of the expression when condition1 is FALSE or NULL, because the THEN branch is executed only when the result of the expression is TRUE, and that requires both operands to be TRUE. As soon as one operand is found to be other than TRUE, there is no longer any chance for the THEN branch to be taken.
Note
I found something interesting while researching PL/SQL’s short-circuit behavior. The behavior that you get depends on the expression’s context. Consider the following statement:
my_boolean:=condition1ANDcondition2
Unlike the case with an IF statement, when condition1 is NULL, this expression will not short-circuit. Why not? Because the result could be either NULL or FALSE, depending on condition2. For an IF statement, NULL and FALSE both lead to the ELSE branch, so a short-circuit can occur. But for an assignment, the ultimate value must be known, and short-circuiting, in this case, can (and will) occur only when condition1 is FALSE.
Similar to the case with AND, if the first operand of an OR operation in an IF statement is TRUE, PL/SQL immediately executes the THEN branch:
IFcondition1ORcondition2THEN ... ELSE ... END IF;
This short-circuiting behavior can be useful when one of your conditions is particularly expensive in terms of CPU or memory utilization. In such a case, be sure to place that condition at the end of the set of conditions:
IFlow_CPU_conditionANDhigh_CPU_conditionTHEN ... END IF;
The low_CPU_condition is evaluated first, and if the result is enough to determine the end result of the AND operation (i.e., the result is FALSE), the more expensive condition will not be evaluated, and your application’s performance is the better for that evaluation’s not happening.
Warning
However, if you are depending on that second condition’s being evaluated, perhaps because you want the side effects from a stored function that the condition invokes, then you have a problem and you need to reconsider your design. I don’t believe it’s good to depend on side effects in this manner.
You can achieve the effect of short-circuit evaluation in a much more explicit manner using a nested IF statement:
IFlow_CPU_conditionTHEN IFhigh_CPU_conditionTHEN ... END IF; END IF;
Now, high_CPU_condition is evaluated only if low_CPU_condition evaluates to TRUE. This is the same effect as short-circuit evaluation, but it’s more obvious at a glance what’s going on. It’s also more obvious that my intent is to evaluate low_CPU_condition first.
Short-circuiting also applies to CASE statements and CASE expressions. These are described in the next section.
Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
