Control Structures  «Prev 

If_then_else 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

  1. condition_1: First condition that will be evaluated.
  2. condition_2: Next condition that will be evaluated.
  3. statement_1,...,statement_n: A sequence of statements that PL/SQL will execute if condition_1 evaluates to TRUE.
  4. statement_11,...,statement_n1: A sequence of statements that PL/SQL will execute if condition_2 evaluates to TRUE.
  5. statement_12,...,statement_n2: A sequence of statements that PL/SQL will execute if both condition_1 and condition_2 evaluate to FALSE.
  6. current_reorder_count < re_order_quantity: First condition that will be evaluated.
  7. pets_to_order := re_order_quantity - current_reorder_count

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

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.