Oracle Pl Sql If Then Else

Introduction

The Oracle PL/SQL IF THEN ELSE construct is a fundamental control structure that enables developers to execute different blocks of code based on specified conditions, making it essential for building dynamic and responsive database applications. By mastering this construct, programmers can implement decision‑making logic directly within stored procedures, functions, and triggers, thereby enhancing data integrity, automating business rules, and improving overall application performance.

Basic Syntax

The IF THEN ELSE Structure

In Oracle PL/SQL, the basic form of the conditional statement is:

IF  THEN
    -- statements to execute when condition1 is TRUE
ELSE IF  THEN
    -- statements to execute when condition2 is TRUE
ELSE
    -- statements to execute when none of the above conditions are TRUE
END IF;

• <condition1>, <condition2>, etc., are Boolean expressions that evaluate to TRUE, FALSE, or NULL.
• The ELSE clause is optional; you can have a simple IF…THEN without an ELSE.
• The END IF keyword terminates the construct and is mandatory.

Key Components

• Condition – any expression that results in a Boolean value.
• THEN – marks the start of the code block to run when the condition is true.
• ELSE IF – allows you to test additional conditions after the first one fails.
• ELSE – provides a fallback block when all preceding conditions are false.
• END IF – signals the end of the conditional logic.

Simple Example

Example Code

DECLARE
    v_salary NUMBER := 5000;
    v_bonus  NUMBER;
BEGIN
    IF v_salary > 8000 THEN
        v_bonus := 500;
    ELSE IF v_salary BETWEEN 5000 AND 8000 THEN
        v_bonus := 250;
    ELSE
        v_bonus := 100;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/

In this example, the IF THEN ELSE logic evaluates the salary and assigns an appropriate bonus. The ELSE IF clause handles a middle range, while the final ELSE covers all lower salaries The details matter here..

Nested IF Statements

How Nesting Works

You can place an IF…THEN…ELSE block inside another, creating a hierarchy of decisions:

DECLARE
    v_score NUMBER := 75;
BEGIN
    IF v_score >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('Grade: A');
    ELSE
        IF v_score >= 80 THEN
            DBMS_OUTPUT.PUT_LINE('Grade: B');
        ELSE
            IF v_score >= 70 THEN
                DBMS_OUTPUT.PUT_LINE('Grade: C');
            ELSE
                DBMS_OUTPUT.PUT_LINE('Grade: Fail');
            END IF;
        END IF;
    END IF;
END;
/

Here, each ELSE contains another IF statement, allowing fine‑grained classification of the score And that's really what it comes down to..

ELSE IF vs ELSE

Using ELSE IF

• ELSE IF is useful when you have multiple mutually exclusive conditions.
• It improves readability because each condition is clearly separated.

Using ELSE

• ELSE acts as a catch‑all when you do not need to test additional conditions.
• It is ideal for default actions, such as error handling or fallback logic.

Practical Applications

Decision Making in Stored Procedures

Stored procedures often require validation of input parameters. An IF THEN ELSE block can verify ranges, check for null values, or enforce business rules before any DML operations are executed That's the part that actually makes a difference. Less friction, more output..

Data Validation

When inserting or updating rows, you might need to:

1. Verify that a primary key is not duplicated.
2. check that a date field falls within an acceptable range.
3. Raise a custom exception if any check fails.

Dynamic SQL Generation

By evaluating conditions, you can construct dynamic SQL statements that only include parts of a query when certain criteria are met, optimizing performance and reducing parsing overhead.

Common Pitfalls

• Missing END IF – Forgetting to close the construct results in a compilation error.
• Using NULL in Conditions – Comparisons with NULL (e.g., col = NULL) always evaluate to FALSE; use IS NULL or IS NOT NULL instead.
• Over‑nesting – Deeply nested IF statements can become hard to read and maintain; consider refactoring into separate procedures or using CASE expressions for simpler multi‑value checks.
• Incorrect Boolean Logic – Combining conditions with AND or OR without proper parentheses may lead to unexpected outcomes.

Best Practices

• Keep Conditions Simple – Break complex logic into multiple IF…ELSE IF blocks rather than cramming everything into one condition.
• Prefer CASE for Multiple Values – When dealing with a single

expression or variable with multiple possible values, a CASE statement is often cleaner than nested IF-ELSE IF. CASE reduces visual clutter and makes the intent obvious at a glance.

• Use Early Exits – If a condition invalidates further processing, raise an exception or RETURN immediately rather than nesting subsequent logic inside multiple ELSE blocks.
• Document the Rationale – Brief comments above complex IF blocks explain why certain branches exist, which helps future maintainers avoid introducing regressions.
• Test Boundary Values – Always validate edge cases such as NULLs, zero, negative numbers, and maximum allowable values to ensure each branch behaves as expected.

Complete Example: Input Validation with IF and CASE

The following procedure demonstrates combining conditional logic for reliable data validation before an INSERT operation:

CREATE OR REPLACE PROCEDURE p_insert_employee (
    p_emp_id    IN employees.employee_id%TYPE,
    p_salary    IN employees.salary%TYPE,
    p_de

### Complete Example: Input Validation with IF and CASE  

The following procedure demonstrates combining conditional logic for dependable data validation before an INSERT operation:

```sql
CREATE OR REPLACE PROCEDURE p_insert_employee (
    p_emp_id    IN employees.employee_id%TYPE,
    p_salary    IN employees.salary%TYPE,
    p_dept_id   IN employees.department_id%TYPE
)
IS
    v_dup_count   NUMBER;
    v_valid_dept  BOOLEAN := FALSE;
BEGIN
    -- 1. Check for duplicate primary key
    SELECT COUNT(*)
      INTO v_dup_count
      FROM employees
     WHERE employee_id = p_emp_id;

    IF v_dup_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee ID already exists.');
    END IF;

    -- 2. Validate salary range using IF‑THEN‑ELSE
    IF p_salary IS NULL THEN
        RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be NULL.');
    ELSIF p_salary <= 0 THEN
        RAISE_APPLICATION_ERROR(-20003, 'Salary must be positive.');
    ELSIF p_salary > 1000000 THEN
        RAISE_APPLICATION_ERROR(-20004, 'Salary exceeds maximum allowed.');
    END IF;

    -- 3. Verify department existence with a dynamic cursor
    FOR rec IN (SELECT 1 FROM departments WHERE department_id = p_dept_id) LOOP
        v_valid_dept := TRUE;
        EXIT;
    END LOOP;

    IF NOT v_valid_dept THEN
        RAISE_APPLICATION_ERROR(-20005, 'Department does not exist.');
    END IF;

    -- 4. Perform the insert only after all checks pass
    INSERT INTO employees (employee_id, salary, department_id)
    VALUES (p_emp_id, p_salary, p_dept_id);

EXCEPTION
    WHEN OTHERS THEN
        -- Re-raise with original error context
        RAISE;
END;
/

Conclusion

Conditional logic is the backbone of reliable PL/SQL code, enabling developers to enforce business rules, validate data, and adapt behavior dynamically. By mastering IF statements, CASE expressions, and their associated best practices—such as simplifying conditions, handling NULLs correctly, and testing edge cases—you can write procedures that are both solid and maintainable. The example above illustrates how a disciplined approach to validation prevents data integrity issues and provides clear feedback, ultimately fostering trust in the underlying systems.