Nested IF Statements

You can nest any IF statement within any other IF statement. The following IF statement shows several layers of nesting:

IF condition1THEN IF condition2 THEN statements2 ELSE IF condition3 THEN statements3 ELSIF condition4 THEN statements4 END 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 to 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, the obvious reason to nest IF statements is to evaluate one condition only when another condition is true. For example, in our code to award bonuses, we 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 we want to print a message for each bonus check issued, but we don't want to print a bonus check for a zero amount in cases where no bonus was given.

Another situation in which you'd want to use nested IF statements is when the evaluation of a condition is very expensive in terms of CPU or memory utilization. In such a case, you may want to defer that processing to an inner IF statement so that it is executed only when absolutely necessary. This is especially true of code that will be performed frequently or in areas of the application where quick response time is critical. The following IF statement illustrates this concept:

IF condition1 AND condition2THEN ...END IF;

The PL/SQL runtime engine evaluates both conditions in order to determine whether the Boolean expression evaluates to TRUE. Suppose that condition2 is an expression that PL/SQL can process simply and efficiently, such as:

total_sales > 100000

but that condition1 is a much more complex and CPU-intensive expression, perhaps calling a stored function that executes a query against the database. If condition2 is evaluated in a tenth of a second to FALSE, and condition1 is evaluated in three seconds to TRUE, then it has taken more than three seconds to determine that the code inside the IF statement should not be executed.

Now consider this next version of the same IF statement:

IF condition2THEN IF condition1 THEN ... END IF;END IF;

Now condition1 will be evaluated only if condition2 evaluates to TRUE. In those situations where total_sales <= 100000, the user will never have to wait the extra three seconds to continue.

Avoiding Syntax Gotchas 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 very 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 get very confused and not recognize the ELSEIF as part of the IF statement. It will interpret it as a variable or 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.