How to Use CASE Statement in Oracle Database 23ai PL/SQL

The CASE statement in Oracle PL/SQL is a powerful alternative to IF statements when you want to evaluate multiple conditions more clearly and concisely. In Oracle Database 23ai, you can use the CASE statement both in PL/SQL blocks and directly inside SQL queries. It improves readability, reduces nested conditions, and handles complex decision-making in a structured way. In this Oracle tutorial, you will learn how to use the CASE statement in PL/SQL with simple and advanced examples, including Oracle Database 23ai features like JSON and vectors.

Syntax of CASE Statement in PL/SQL

The PL/SQL CASE statement has two forms—simple CASE and searched CASE.

Simple CASE

Compares a single expression against multiple values:

CASE expression
   WHEN value1 THEN result1
   WHEN value2 THEN result2
   ELSE resultN
END

Searched CASE

Evaluates multiple Boolean conditions:

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ELSE resultN
END

Both forms can be used inside PL/SQL blocks or SQL statements.

Example 1: Simple CASE Statement in PL/SQL

This example shows how to use a simple CASE expression to map a grade value into a descriptive message.

DECLARE
   v_grade CHAR(1) := 'B';
   v_result VARCHAR2(20);
BEGIN
   v_result := CASE v_grade
                  WHEN 'A' THEN 'Excellent'
                  WHEN 'B' THEN 'Good'
                  WHEN 'C' THEN 'Average'
                  ELSE 'Fail'
               END;
   DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
/

Result:

Result: Good

Here, the variable v_grade is checked against fixed values, and the matching branch is executed.

Example 2: Searched CASE Statement in PL/SQL

This example demonstrates how searched CASE is useful when evaluating ranges or conditions rather than exact matches.

DECLARE
   v_salary NUMBER := 7500;
   v_bonus VARCHAR2(20);
BEGIN
   v_bonus := CASE
                 WHEN v_salary >= 10000 THEN 'High Bonus'
                 WHEN v_salary >= 7000 THEN 'Medium Bonus'
                 ELSE 'Low Bonus'
              END;
   DBMS_OUTPUT.PUT_LINE('Bonus Category: ' || v_bonus);
END;
/

Result:

Bonus Category: Medium Bonus

Example 3: CASE Statement in SQL Queries

CASE is often used directly in SQL queries for conditional display of values.

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

This assigns each employee a category based on salary ranges.

Example 4: Nested CASE Statements

You can also nest CASE statements when the logic requires more depth.

DECLARE
   v_department VARCHAR2(20) := 'IT';
   v_experience NUMBER := 4;
   v_role VARCHAR2(30);
BEGIN
   v_role := CASE v_department
                WHEN 'IT' THEN 
                   CASE
                      WHEN v_experience >= 5 THEN 'Senior IT Staff'
                      ELSE 'Junior IT Staff'
                   END
                WHEN 'HR' THEN 'Human Resources'
                ELSE 'Other Department'
             END;
   DBMS_OUTPUT.PUT_LINE('Assigned Role: ' || v_role);
END;
/

Result:

Assigned Role: Junior IT Staff

Nested CASE structures are better than multiple nested IF statements for readability.

Example 5: CASE with JSON Data in Oracle 23ai

Oracle Database 23ai supports advanced JSON operations. You can use CASE to check values inside JSON objects.

DECLARE
   v_customer JSON_OBJECT_T;
   v_status VARCHAR2(20);
BEGIN
   v_customer := JSON_OBJECT_T('{"id":101, "status":"active", "loyalty":"gold"}');
   v_status := CASE v_customer.get_String('status')
                  WHEN 'active' THEN 'Customer is active'
                  ELSE 'Customer is inactive'
               END;
   DBMS_OUTPUT.PUT_LINE(v_status);
END;
/

Result:

Customer is active

This blends modern JSON features with traditional PL/SQL logic.

Example 6: CASE with Vector Similarity in Oracle 23ai

Oracle 23ai introduced vectors for AI-powered applications. You can apply CASE on similarity values.

DECLARE
   v_similarity NUMBER := 0.78;
   v_result VARCHAR2(30);
BEGIN
   v_result := CASE
                  WHEN v_similarity > 0.85 THEN 'Highly Similar'
                  WHEN v_similarity > 0.70 THEN 'Moderately Similar'
                  ELSE 'Low Similarity'
               END;
   DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

Result:

Moderately Similar

This demonstrates how CASE can help categorize AI-driven results.

Example 7: Handling NULL Values with CASE

Oracle treats NULL as unknown, so you can use CASE to handle it properly.

DECLARE
   v_commission NUMBER := NULL;
   v_result VARCHAR2(30);
BEGIN
   v_result := CASE
                  WHEN v_commission IS NULL THEN 'No Commission'
                  ELSE 'Commission Available'
               END;
   DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

Result:

No Commission

This prevents unexpected results when working with NULL values in PL/SQL.

Example 8: Practical Business Rule with CASE

Here is a real-world style example of assigning employee grades based on salary and experience.

DECLARE
   v_salary NUMBER := 9000;
   v_experience NUMBER := 6;
   v_grade VARCHAR2(20);
BEGIN
   v_grade := CASE
                 WHEN v_salary > 10000 AND v_experience > 5 THEN 'Platinum'
                 WHEN v_salary >= 8000 AND v_experience >= 5 THEN 'Gold'
                 WHEN v_salary >= 5000 THEN 'Silver'
                 ELSE 'Bronze'
              END;
   DBMS_OUTPUT.PUT_LINE('Employee Grade: ' || v_grade);
END;
/

Result:

Employee Grade: Gold

This shows how CASE can be integrated into business rules.

Common Mistakes with CASE

1. Forgetting ELSE clause – If no condition matches, the result is NULL.
2. Mismatched types – All branches must return the same data type.
3. Overuse – Very large nested CASE may reduce readability; sometimes IF is better.

Conclusion

The CASE statement in Oracle Database 23ai PL/SQL is a versatile tool for conditional logic. It can simplify code, improve readability, and handle multiple outcomes elegantly. Whether you are categorizing salaries, handling JSON fields, or evaluating AI-driven vector similarity, CASE provides a consistent solution. By mastering both simple and searched CASE forms, you can write cleaner and more powerful PL/SQL programs in Oracle 23ai.