Lesson 2	IF-THEN-ELSE statements
Objective	Build logic using the IF-THEN-ELSE and ELSIF statements.

IF-THEN-ELSE statements using PL/SQL

You can build logic within your PL/SQL block to execute a sequence of statements based on a particular condition. The selection structure tests a condition then executes one sequence of statements instead of another, depending on the condition. There are three forms of IF statements.

IF-THEN Structure
This sequence of statements executes only if the conditi evaluates to TRUE. If the condition evaluates to FALSE or NULL, the IF statement does nothing. In either case, the control passes to the next statement.

Oracle if then statement — IF condition_1 is TRUE THEN statement_1,..., statement_n END IF;
Oracle IF-THEN statement

IF-THEN-ELSE Structure in PL/SQL

The sequence of statements in the ELSE clause is executed only if the condition evaluates to FALSE or NULL. The ELSE clause ensures that a sequence of statements is executed. The ELSE clause can include IF statements (nested IF). This construct allows selection of action from several mutually exclusive alternatives. An IF statement can have any number of ELSIF clauses, but the final ELSE clause is optional. Conditions are evaluated one by one from top to bottom as described in the diagram below. Notice that the ELSEIF word is spelled as ELSIF, with a missing E, within PL/SQL.

**Syntax:**
IF condition_1 is TRUE THEN statement_1, ..., statement_n ELSEIF condition_2 is TRUE THEN statement_11,...,statement_n1 ELSE statement_12,...,statement_n2 END IF;

If_then_else syntax

condition_1: First condition that will be evaluated.

condition_2: Next condition that will be evaluated.

statement_1,...,statement_n: A sequence of statements that PL/SQL will execute if condition_1 evaluates to TRUE.

statement_11,...,statement_n1: A sequence of statements that PL/SQL will execute if condition_2 evaluates to TRUE.

statement_12,...,statement_n2: A sequence of statements that PL/SQL will execute if both condition_1 and condition_2 evaluate to FALSE.

current_reorder_count < re_order_quantity: First condition that will be evaluated.

pets_to_order := re_order_quantity - current_reorder_count

**Example:**
IF current_reorder_count < re_order_quantity THEN pets_to_order := re_order_quantity - current_reorder_count; ELSIF current_reorder_count > re_order_quantity THEN pets_to_order := 0; commission_for_sales := 0.10; ELSE pets_to_order := 0; END IF;

This represents the basic structure and an example of using `IF-THEN-ELSE` statements in Oracle PL/SQL for conditional logic.

This statement will be executed if the value of current_reorder_count is less than the value of re_order_quantity.

Oracle PL/SQL Programming

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as it can determine the result. Therefore, you can write expressions that might otherwise cause errors. In Example 4, short-circuit evaluation prevents the OR expression from causing a divide-by-zero error. When the value of on_hand is zero, the value of the left operand is TRUE, so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the OR operator, the right operand would cause a division by zero error.
Example 4: Short-Circuit Evaluation

DECLARE
on_hand INTEGER := 0;
on_order INTEGER := 100;
BEGIN
-- Does not cause divide-by-zero error;
-- evaluation stops after first expression
IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN
DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.');
END IF;
END;
/

Result: On hand quantity is zero.
You can have any number of IF clauses nested within an IF clause. However, you can have only one ELSE clause for every IF clause. In the next lesson, you will learn to build a PL/SQL block with the IF clause through an evaluative simulation.