Conditional Formatting

 IF-ELSE Block:

DECLARE

    v_salary NUMBER := 50000;

BEGIN

    IF v_salary > 60000 THEN

        DBMS_OUTPUT.PUT_LINE('High salary');

    ELSIF v_salary BETWEEN 30000 AND 60000 THEN

        DBMS_OUTPUT.PUT_LINE('Medium salary');

    ELSE

        DBMS_OUTPUT.PUT_LINE('Low salary');

    END IF;

END;

/

CASE :

DECLARE

    v_salary NUMBER := 45000;

    v_grade CHAR(1) := 'B';

    v_category VARCHAR2(20);

BEGIN

    -- (Expression-Based)

    v_category := CASE v_grade

        WHEN 'A' THEN 'Excellent'

        WHEN 'B' THEN 'Good'

        WHEN 'C' THEN 'Average'

        ELSE 'Fail'

    END;

    DBMS_OUTPUT.PUT_LINE('Employee Performance: ' || v_category);

 

    -- (Search-Based)

    CASE

        WHEN v_salary > 60000 THEN DBMS_OUTPUT.PUT_LINE('High Salary')

        WHEN v_salary BETWEEN 30000 AND 60000 THEN DBMS_OUTPUT.PUT_LINE('Medium Salary')

        ELSE DBMS_OUTPUT.PUT_LINE('Low Salary')

    END CASE;

END;

/

 

Used in Select :

SELECT emp_name, salary,

    CASE

        WHEN salary > 60000 THEN 'High Salary'

        WHEN salary BETWEEN 30000 AND 60000 THEN 'Medium Salary'

        ELSE 'Low Salary'

    END AS salary_category

FROM employees;

Used in Update :

UPDATE employees

SET grade = CASE

    WHEN salary > 60000 THEN 'A'

    WHEN salary BETWEEN 40000 AND 60000 THEN 'B'

    ELSE 'C'

END;

Used in order By :

SELECT emp_name, salary

FROM employees

ORDER BY

    CASE

        WHEN salary > 60000 THEN 1       -- High salaries first

        WHEN salary BETWEEN 30000 AND 60000 THEN 2

        ELSE 3                          -- Low salaries last

    END;

 

Difference between IF ELSE and CASE:

 

IF-ELSE Statement	CASE Statement
Used for control flow (decision-making in PL/SQL blocks).	Used for both control flow in PL/SQL and inline expressions in SQL queries.
Not return a value directly.	Returns a single value
Only in PL/SQL (inside BEGIN...END).	Both in PL/SQL and SQL (SELECT, UPDATE, ORDER BY).
Slower when used for multiple conditions.	More efficient inside SQL queries since it is optimized by Oracle.
- Complex decision-making in PL/SQL blocks. - Executing multiple statements based on conditions.	- Simplifying conditions inside SQL queries. - Replacing IF-ELSE when a single return value is needed.
IF v_salary > 60000 THEN         DBMS_OUTPUT.PUT_LINE('High Salary');     ELSIF v_salary BETWEEN 30000 AND 60000 THEN         DBMS_OUTPUT.PUT_LINE('Medium Salary');     ELSE         DBMS_OUTPUT.PUT_LINE('Low Salary');     END IF;	v_category := CASE         WHEN v_salary > 60000 THEN 'High Salary'         WHEN v_salary BETWEEN 30000 AND 60000 THEN 'Medium Salary'         ELSE 'Low Salary'     END;