CASE | DECODE | IF ELSE - Conditional Logic in PLSQL

1. CASE Statement

The CASE statement is flexible and allows for complex conditional logic based on multiple conditions.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

SQL Example

SELECT employee_id,
       first_name,
       salary,
       CASE
           WHEN salary > 5000 THEN 'High'
           WHEN salary BETWEEN 3000 AND 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

Explanation:

• This SQL query selects the employee_id, first_name, and salary from the employees table.
• It categorizes the salary into three categories: "High" for salaries greater than 5000, "Medium" for salaries between 3000 and 5000, and "Low" for salaries below 3000.

---

2. DECODE Function

The DECODE function is an Oracle-specific function used for equality checks in conditional logic.

Syntax

DECODE(expression, search1, result1, search2, result2, ..., default_result)

SQL Example

SELECT employee_id,
       first_name,
       salary,
       DECODE(salary,
              5000, 'High',
              3000, 'Medium',
              'Low') AS salary_category
FROM employees;

Explanation:

• In this SQL query, the DECODE function checks the salary value.
• If the salary equals 5000, it returns 'High'; if it equals 3000, it returns 'Medium'; otherwise, it returns 'Low'.
• This is a more straightforward way to handle conditions when you are comparing specific values.

---

3. IF-THEN-ELSE (PL/SQL Block)

The IF-THEN-ELSE statement is used in PL/SQL blocks for implementing conditional logic.

Syntax

IF condition THEN
    -- statements
ELSIF condition THEN
    -- statements
ELSE
    -- statements
END IF;

PL/SQL Example

DECLARE
   v_salary NUMBER := 4000;  -- Initializing a salary variable
   v_category VARCHAR2(10);   -- Variable to hold the salary category
BEGIN
   IF v_salary > 5000 THEN
      v_category := 'High';   -- Set category to 'High'
   ELSIF v_salary BETWEEN 3000 AND 5000 THEN
      v_category := 'Medium'; -- Set category to 'Medium'
   ELSE
      v_category := 'Low';    -- Set category to 'Low'
   END IF;

   DBMS_OUTPUT.PUT_LINE('Salary Category: ' || v_category); 
-- Output the result
END;

Explanation:

• In this PL/SQL block, the variable v_salary is initialized with a value of 4000.
• The IF-THEN-ELSE statement checks the value of v_salary and assigns the appropriate category to v_category.
• Finally, it outputs the category using DBMS_OUTPUT.PUT_LINE.