CASE statement in SQL and PL/SQL - Oracle

In PL/SQL, the CASE statement is used to perform conditional logic within your SQL queries and PL/SQL blocks. It allows you to evaluate a condition and execute a specific block of code based on the result of that condition.

Syntax of CASE in PL/SQL:

There are two types of CASE expressions in PL/SQL: the simple CASE expression and the searched CASE expression.

1. Simple CASE Expression:

The simple CASE expression evaluates a single expression and compares it against multiple possible values.

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

2. Searched CASE Expression:

The searched CASE expression allows for more complex conditions by evaluating multiple Boolean expressions.

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

Examples of CASE Usage in PL/SQL:

Example 1: Simple CASE Expression in a SQL Query

SELECT employee_id,
       first_name,
       last_name,
       department_id,
       CASE department_id
         WHEN 10 THEN 'Administration'
         WHEN 20 THEN 'Marketing'
         WHEN 30 THEN 'Purchasing'
         ELSE 'Other'
       END AS department_name
FROM employees;

In this example, the CASE expression translates department IDs into department names.

Example 2: Searched CASE Expression in a SQL Query

SELECT employee_id,
       first_name,
       last_name,
       salary,
       CASE
         WHEN salary < 3000 THEN 'Low'
         WHEN salary BETWEEN 3000 AND 7000 THEN 'Medium'
         ELSE 'High'
       END AS salary_level
FROM employees;

This example categorizes employees' salaries into 'Low', 'Medium', and 'High' levels.

Example 3: CASE Statement in a PL/SQL Block

DECLARE
  v_grade CHAR(1) := 'B';
  v_message VARCHAR2(50);
BEGIN
  CASE v_grade
    WHEN 'A' THEN
      v_message := 'Excellent';
    WHEN 'B' THEN
      v_message := 'Good';
    WHEN 'C' THEN
      v_message := 'Average';
    ELSE
      v_message := 'Poor';
  END CASE;

  DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade || ', Message: ' || v_message);
END;

This PL/SQL block evaluates the variable v_grade and assigns an appropriate message to v_message based on the grade.

Tips for Using CASE in PL/SQL:

1. Ensure All Possible Conditions Are Handled:

   • Always include an ELSE clause to handle unexpected values, which prevents runtime errors.

2. Use Nested CASE Expressions if Necessary:

   • For more complex conditions, you can nest CASE expressions within each other.

3. Optimize for Readability:

   • Keep your CASE expressions simple and readable. If the logic becomes too complex, consider breaking it down into multiple steps or using functions.

Conclusion:

The CASE statement is a powerful tool in PL/SQL for implementing conditional logic. Whether you use it in SQL queries or PL/SQL blocks, it provides a flexible way to handle different conditions and produce desired outcomes based on those conditions.