Lesson 3	Using the IF clause
Objective	Build a PL/SQL block using an IF clause in Oracle 23ai

Using the IF Clause in Oracle PL/SQL

This lesson applies the IF-THEN-ELSIF construct to a practical business problem — calculating a tiered sales commission for a pet store. You will build a complete PL/SQL block that accepts a sale value at runtime, evaluates it against four commission tiers, computes the commission amount, and displays the result. The lesson then extends into the searched CASE statement as the preferred alternative for multi-tier logic in Oracle 23ai.

Commission Calculation Requirements

The pet store commission structure has four tiers based on the total sale value:

Sale value below $100 — commission rate is 10%
Sale value between $100 and $500 — commission rate is 15%
Sale value between $500 and $1,000 — commission rate is 20%
Sale value of $1,000 or above — commission rate is 25%

The four tiers are mutually exclusive — exactly one applies to any given sale value. This makes IF-THEN-ELSIF the correct construct: only the first matching condition executes, and the remaining branches are skipped.

The IF Clause for Commission Tiers

The following IF-THEN-ELSIF block implements the four-tier logic. Note that boundary values — exactly $100, $500, and $1,000 — fall into the upper tier of each pair because the conditions use strict less-than comparisons:

IF v_sale_value < 100 THEN
  v_commission_percent := 10;
ELSIF v_sale_value < 500 THEN
  v_commission_percent := 15;
ELSIF v_sale_value < 1000 THEN
  v_commission_percent := 20;
ELSE
  v_commission_percent := 25;
END IF;

The original hint code used compound AND conditions such as v_sale_value > 100 AND v_sale_value < 500. This is unnecessary — because conditions are evaluated top to bottom and the first matching branch exits the construct, any value that reaches the second ELSIF is already known to be 100 or above. The simplified form above is cleaner and avoids the gap where a value of exactly $100 would fall through without matching any branch.

Complete PL/SQL Block — Commission Calculator

The following block accepts a sale value using the SQL*Plus ACCEPT command, calculates the commission using the four-tier IF-THEN-ELSIF structure, and displays the result:

SET SERVEROUTPUT ON

ACCEPT av_sale_value NUMBER PROMPT 'Please enter the sale value: '

DECLARE
  v_sale_value         NUMBER := &av_sale_value;
  v_commission_percent NUMBER;
  v_commission         NUMBER;
BEGIN
  IF v_sale_value < 100 THEN
    v_commission_percent := 10;
  ELSIF v_sale_value < 500 THEN
    v_commission_percent := 15;
  ELSIF v_sale_value < 1000 THEN
    v_commission_percent := 20;
  ELSE
    v_commission_percent := 25;
  END IF;

  v_commission := v_sale_value * (v_commission_percent / 100);

  DBMS_OUTPUT.PUT_LINE('Sale value:   $' || TO_CHAR(v_sale_value,   '999,990.00'));
  DBMS_OUTPUT.PUT_LINE('Commission %: '  || v_commission_percent || '%');
  DBMS_OUTPUT.PUT_LINE('Commission:   $' || TO_CHAR(v_commission,   '999,990.00'));
END;
/

For a sale value of $750, the output would be:

Sale value:   $    750.00
Commission %: 20%
Commission:   $    150.00

Building the Block Step by Step

The block follows this construction sequence in SQL*Plus or SQL Developer:

Connect to the database using PETSTORE as the username, GREATPETS as the password, and MYDB as the host string.
Enable server output with SET SERVEROUTPUT ON so DBMS_OUTPUT.PUT_LINE results are visible.
Use ACCEPT av_sale_value NUMBER PROMPT '...' to accept the sale value from the user into a substitution variable.
Open the DECLARE section and declare three variables: v_sale_value NUMBER initialized from &av_sale_value, v_commission_percent NUMBER, and v_commission NUMBER.
Open the BEGIN section and write the four-tier IF-THEN-ELSIF block to assign v_commission_percent.
Calculate v_commission as v_sale_value * (v_commission_percent / 100).
Display the results using DBMS_OUTPUT.PUT_LINE with TO_CHAR for formatted numeric output.
Close the block with END; and execute with /.

IF-THEN-ELSIF Syntax Rules

Four syntax rules govern IF-THEN-ELSIF that are worth stating explicitly:

Spell it ELSIF, not ELSEIF. Oracle PL/SQL uses ELSIF — a single word with no embedded E. Using ELSEIF or ELSE IF (two words) produces a compile-time error.
Each ELSIF clause requires THEN. Only the ELSE keyword does not require THEN.
The ELSE clause is optional. If omitted and no condition matches, the entire IF block silently does nothing. For commission logic this would leave v_commission_percent uninitialized — always include ELSE when a default value is required.
Use ELSIF for mutually exclusive alternatives. Only one branch executes per evaluation. Once a matching condition is found, all remaining ELSIF and ELSE clauses are skipped.

CASE Statement as an Alternative

The searched CASE statement is functionally equivalent to IF-THEN-ELSIF for multi-tier logic and is generally preferred in Oracle 23ai when the alternatives map to a single expression. The commission tier logic expressed as a searched CASE statement:

v_commission_percent :=
  CASE
    WHEN v_sale_value < 100  THEN 10
    WHEN v_sale_value < 500  THEN 15
    WHEN v_sale_value < 1000 THEN 20
    ELSE 25
  END;

The CASE form assigns the result directly to the variable in a single expression rather than through a sequence of conditional assignments. It is more compact and its intent is immediately readable — the variable receives one of four values depending on which condition matches first.

Employee Bonus Example — CASE in Practice

The following block demonstrates a searched CASE statement applied to an employee bonus calculation. The performance rating drives the bonus tier, and the result is displayed using DBMS_OUTPUT.PUT_LINE:

DECLARE
  v_employee_id  NUMBER        := 101;
  v_performance  VARCHAR2(20)  := 'Excellent';
  v_bonus        NUMBER;
BEGIN
  v_bonus :=
    CASE
      WHEN v_performance = 'Excellent' THEN 1000
      WHEN v_performance = 'Good'      THEN 750
      WHEN v_performance = 'Average'   THEN 500
      ELSE 0
    END;

  DBMS_OUTPUT.PUT_LINE('Employee ID:  ' || v_employee_id);
  DBMS_OUTPUT.PUT_LINE('Performance:  ' || v_performance);
  DBMS_OUTPUT.PUT_LINE('Bonus:        $' || v_bonus);
END;
/

Output:

Employee ID:  101
Performance:  Excellent
Bonus:        $1000

In a production environment, v_performance would be fetched from a table using a SELECT INTO statement rather than assigned a literal value. The CASE logic itself remains unchanged regardless of how the input value is sourced.

Choosing Between IF-THEN-ELSIF and CASE

Both constructs implement multi-branch conditional logic, but they suit different scenarios:

Use IF-THEN-ELSIF when conditions involve different variables, range comparisons with two operands per branch, or complex Boolean expressions that cannot be reduced to a single selector.
Use CASE when a single variable or expression is evaluated against multiple alternatives, or when the result of the conditional logic is a single value being assigned to a variable. The CASE expression form is particularly clean for assignment scenarios because it eliminates the need for a separate assignment statement in each branch.

For the commission tier example, either construct produces correct results. The CASE form is marginally preferable because all four tier values are scalar constants and the construct reads as a single assignment rather than a sequence of conditional assignments.

Summary

Building a PL/SQL block with an IF clause requires declaring the variables that will hold input and calculated values, writing the IF-THEN-ELSIF logic to assign the correct tier value based on the input, computing the final result, and displaying it with DBMS_OUTPUT.PUT_LINE. Always spell the keyword as ELSIF, include an ELSE clause when a default value is required, and consider the searched CASE statement as a cleaner alternative when the conditional logic assigns a single value from a set of discrete alternatives. The next lesson covers iterative control structures — loops in Oracle PL/SQL.