What are the Conditional Statements in Oracle?

Conditional Statements in Oracle

Conditional logic, which enables distinct code blocks to run depending on whether predetermined conditions are met, is essential to PL/SQL systems’ decision-making. The main tools used to accomplish this capability are CASE and IF statements.

Conditional Logic: IF Statements

IF statements offer a conditional branching technique. Following a condition evaluation, which may return TRUE, FALSE, or NULL (three-valued logic), they run the appropriate code blocks. Readability depends on appropriate formatting and indentation, particularly in complex situations. Although PL/SQL allows for the nesting of IF statements, too much nesting can make logic hard to debug and comprehend.

 IF…THEN…END IF

The IF...THEN...END IF construct is the simplest form of conditional logic. If the condition evaluates to TRUE, the statements between THEN and END IF are executed. If the condition is FALSE or NULL, these statements are skipped.

Example: Verify a worker’s eligibility for a promotion

Let’s generate an EMPLOYEES table and add some data to it. For the following instances, this table will be utilised again.

-- Enable DBMS_OUTPUT for seeing results
SET SERVEROUTPUT ON;

-- Create the EMPLOYEES table
CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID    NUMBER(6) PRIMARY KEY,
    FIRST_NAME     VARCHAR2(20),
    LAST_NAME      VARCHAR2(25) NOT NULL,
    EMAIL          VARCHAR2(25) NOT NULL,
    PHONE_NUMBER   VARCHAR2(20),
    HIRE_DATE      DATE NOT NULL,
    JOB_ID         VARCHAR2(10) NOT NULL,
    SALARY         NUMBER(8,2),
    DEPARTMENT_ID  NUMBER(4)
);

-- Insert values into EMPLOYEES
INSERT INTO EMPLOYEES VALUES (101, 'John', 'Doe', 'JD@example.com', '515.123.4567', TO_DATE('17-JUN-03', 'DD-MON-RR'), 'IT_PROG', 6000, 60);
INSERT INTO EMPLOYEES VALUES (102, 'Jane', 'Smith', 'JS@example.com', '515.123.4568', TO_DATE('21-JUL-05', 'DD-MON-RR'), 'HR_REP', 4500, 50);
INSERT INTO EMPLOYEES VALUES (103, 'Peter', 'Jones', 'PJ@example.com', '515.123.4569', TO_DATE('12-MAR-07', 'DD-MON-RR'), 'SA_MAN', 10000, 80);
INSERT INTO EMPLOYEES VALUES (104, 'Alice', 'Brown', 'AB@example.com', '515.123.4570', TO_DATE('01-APR-02', 'DD-MON-RR'), 'IT_PROG', 7500, 60);
INSERT INTO EMPLOYEES VALUES (105, 'Bob', 'White', 'BW@example.com', '515.123.4571', TO_DATE('25-DEC-06', 'DD-MON-RR'), 'AC_ACCOUNT', 3000, 90);
COMMIT;

-- PL/SQL IF...THEN...END IF example
DECLARE
    v_employee_id   EMPLOYEES.EMPLOYEE_ID%TYPE := 101;
    v_salary        EMPLOYEES.SALARY%TYPE;
    v_job_id        EMPLOYEES.JOB_ID%TYPE;
BEGIN
    SELECT SALARY, JOB_ID
    INTO v_salary, v_job_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = v_employee_id;

    -- Condition: If salary is above 5000 and is an 'IT_PROG'
    IF v_salary > 5000 AND v_job_id = 'IT_PROG' THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' is eligible for promotion.');
    END IF;
END;
/

Output:

Employee 101 is eligible for promotion.

IF…THEN…ELSE…END IF

This construct is used to choose between two mutually exclusive actions. If the condition is TRUE, the THEN block is executed. Otherwise (if FALSE or NULL), the ELSE block is executed. One of the two sequences of statements will always execute. It’s good practice to handle NULL values explicitly using functions like NVL to avoid unexpected behaviour in the ELSE clause.

Example: Calculate Employee Bonuses

-- PL/SQL IF...THEN...ELSE...END IF example
DECLARE
    v_employee_id   EMPLOYEES.EMPLOYEE_ID%TYPE := 102;
    v_salary        EMPLOYEES.SALARY%TYPE;
    v_bonus         NUMBER(8,2);
BEGIN
    SELECT SALARY
    INTO v_salary
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = v_employee_id;

    -- Condition: If salary is greater than 5000
    IF v_salary > 5000 THEN
        v_bonus := v_salary * 0.10; -- 10% bonus
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' receives a high bonus of $' || v_bonus);
    ELSE
        v_bonus := v_salary * 0.05; -- 5% bonus
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' receives a standard bonus of $' || v_bonus);
    END IF;
END;
/

Output:

Employee 102 receives a standard bonus of $225

IF…THEN…ELSIF…THEN…ELSE…END IF

This advanced IF statement handles multiple alternative conditions. Each ELSIF clause tests an additional condition if the preceding IF or ELSIF conditions were FALSE or NULL. If none of the IF or ELSIF conditions are met, the optional ELSE block is executed. The conditions are evaluated in order, and only the statements for the first TRUE condition are executed. It’s important to use ELSIF (single word) and not ELSEIF.

Example: Assignment Grading Based on Score

-- PL/SQL IF...THEN...ELSIF...THEN...ELSE...END IF example
DECLARE
    v_student_id    NUMBER := 1;
    v_score         NUMBER := 85;
    v_grade         VARCHAR2(1);
BEGIN
    IF v_score >= 90 THEN
        v_grade := 'A';
    ELSIF v_score >= 80 THEN
        v_grade := 'B';
    ELSIF v_score >= 70 THEN
        v_grade := 'C';
    ELSIF v_score >= 60 THEN
        v_grade := 'D';
    ELSE
        v_grade := 'F';
    END IF;
    DBMS_OUTPUT.PUT_LINE('Student ' || v_student_id || ' received grade: ' || v_grade);
END;
/

Output:

Student 1 received grade: B

CASE Statements

A structured substitute for intricate IF…ELSIF techniques, CASE statements in PL/SQL frequently offer greater readability and speed. Out of the several alternative sequences, CASE statements let you choose just one to run. Release 1 of the Oracle9i Database for PL/SQL introduced these.

Two primary categories of CASE statements exist:

1. Simple CASE statement: Packed with values from a single expression.
2. Searched CASE statement: Predicated on analysing a set of Boolean conditions.

Unlike IF statements, if no WHEN clause matches and an ELSE clause is omitted in a CASE statement, a CASE_NOT_FOUND exception is raised. However, in CASE expressions, if no WHEN conditions are met, NULL is returned instead of raising an error.

Simple CASE Statement

One expression is compared to several potential values using a straightforward CASE statement, which then runs the statements linked to the first match.

Example: Connect the Category Code with the Description

To illustrate this, let’s utilise a PRODUCT_CATEGORIES database.

-- Create the PRODUCT_CATEGORIES table
CREATE TABLE PRODUCT_CATEGORIES (
    CATEGORY_CODE VARCHAR2(10) PRIMARY KEY,
    CATEGORY_NAME VARCHAR2(50)
);

-- Insert values into PRODUCT_CATEGORIES
INSERT INTO PRODUCT_CATEGORIES VALUES ('ADULTFIC', 'Adult Fiction');
INSERT INTO PRODUCT_CATEGORIES VALUES ('ADULTNF', 'Adult Nonfiction');
INSERT INTO PRODUCT_CATEGORIES VALUES ('CHILDREN', 'Children Books');
INSERT INTO PRODUCT_CATEGORIES VALUES ('REFERENCE', 'Reference Books');
INSERT INTO PRODUCT_CATEGORIES VALUES ('FICTION', 'General Fiction');
COMMIT;

-- PL/SQL Simple CASE statement example
DECLARE
    v_category_code      PRODUCT_CATEGORIES.CATEGORY_CODE%TYPE := 'ADULTFIC';
    v_category_description VARCHAR2(100);
BEGIN
    CASE v_category_code
        WHEN 'ADULTFIC' THEN
            v_category_description := 'Adult Fiction (Best Sellers)';
        WHEN 'ADULTNF' THEN
            v_category_description := 'Adult Non-Fiction (Educational)';
        WHEN 'CHILDREN' THEN
            v_category_description := 'Children''s Books (Illustrated)';
        ELSE
            v_category_description := 'Other Category';
    END CASE;
    DBMS_OUTPUT.PUT_LINE('Description for code ' || v_category_code || ': ' || v_category_description);
END;
/

Output:

Description for code ADULTFIC: Adult Fiction (Best Sellers)

Searched CASE Statement

A searched CASE statement executes the statements for the first condition that evaluates to TRUE after evaluating several Boolean conditions. This is especially helpful when the conditions don’t depend on the value of a single expression.

Example: Calculate the Cost of Shipping by Order Value

To illustrate this, let’s consider an ORDERS table.

-- Create the ORDERS table
CREATE TABLE ORDERS (
    ORDER_ID    NUMBER(6) PRIMARY KEY,
    CUSTOMER_ID NUMBER(6) NOT NULL,
    ORDER_VALUE NUMBER(10,2) NOT NULL,
    ORDER_DATE  DATE NOT NULL
);

-- Insert values into ORDERS
INSERT INTO ORDERS VALUES (1, 10, 150.75, SYSDATE - 5);
INSERT INTO ORDERS VALUES (2, 11, 45.00, SYSDATE - 3);
INSERT INTO ORDERS VALUES (3, 12, 500.20, SYSDATE - 1);
COMMIT;

-- PL/SQL Searched CASE statement example
DECLARE
    v_order_id    ORDERS.ORDER_ID%TYPE := 1;
    v_order_value ORDERS.ORDER_VALUE%TYPE;
    v_shipping_cost NUMBER(5,2);
BEGIN
    SELECT ORDER_VALUE
    INTO v_order_value
    FROM ORDERS
    WHERE ORDER_ID = v_order_id;

    CASE
        WHEN v_order_value < 50 THEN
            v_shipping_cost := 5.00;
        WHEN v_order_value >= 50 AND v_order_value < 200 THEN
            v_shipping_cost := 10.00;
        WHEN v_order_value >= 200 AND v_order_value < 1000 THEN
            v_shipping_cost := 20.00;
        ELSE -- For orders >= 1000 or any other unhandled case
            v_shipping_cost := 0.00; -- Free shipping
    END CASE;

    DBMS_OUTPUT.PUT_LINE('Order ' || v_order_id || ' with value $' || v_order_value || ' has a shipping cost of $' || v_shipping_cost);
END;
/

Output:

Order 1 with value $150.75 has a shipping cost of $10.00

In general, CASE statements especially searched ones are favoured over intricate IF…ELSIF structures due to their potential for clarity and efficiency, especially when managing many conditions. They make the code easier to comprehend and maintain by enabling a cleaner expression of logical decisions.