PL/SQL CASE Statement

PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers.

The PL/SQL CASE statement is a powerful conditional control structure in Oracle databases that allows you to execute different blocks of code based on specified conditions. Here, we explore the syntax, types, and practical use cases of the PL/SQL CASE statement to make better decisions and improve your ability to use conditional logic in Oracle PL/SQL.

CASE Statement in PL/SQL

CASE statement gives you a clear way to handle conditional logic within PL/SQL blocks. It is a conditional control statement that allows you to execute different blocks of code based on the specified conditions. It is particularly useful when dealing with multiple conditions and provides a more readable and maintainable alternative to nested IF-ELSE statements.

Syntax

CASE
   WHEN condition_1 THEN
      -- code block for condition_1
   WHEN condition_2 THEN
      -- code block for condition_2
   ...
   ELSE
      -- default code block
END CASE;

The syntax starts with the keyword CASE, followed by multiple WHEN clauses that specify conditions. If a condition evaluates as true, the corresponding code block is executed. If none of the conditions are met, the ELSE clause (optional) provides a default action.

Types of CASE Statement

There are two primary types of CASE statements in PL/SQL:

1. Simple CASE Statement

In a Simple CASE statement, the value of an expression is compared to constant values (predefined). It is useful when you want to match a single expression with different constant values.

Example:

DECLARE
   day_number NUMBER := 1;
   day_name VARCHAR2(20);
BEGIN
   CASE day_number
      WHEN 1 THEN
         day_name := 'Monday';
      WHEN 2 THEN
         day_name := 'Tuesday';
      WHEN 3 THEN
         day_name := 'Wednesday';
      WHEN 4 THEN
         day_name := 'Thursday';
      WHEN 5 THEN
         day_name := 'Friday';
      WHEN 6 THEN
         day_name := 'Saturday';
      WHEN 7 THEN
         day_name := 'Sunday';
      ELSE
         day_name := 'Invalid day';
   END CASE;

   DBMS_OUTPUT.PUT_LINE('The day is: ' || day_name);
END;

In this example,

• The variable day_number is set to 1 that representing Monday.
• The Simple CASE statement then checks the value of day_number and assigns the corresponding day name to the variable day_name.
• The output will be The day is: Monday

Output:

Statement processed.
The day is: Monday

2. Searched CASE Statement

In a Searched CASE statement, each condition is evaluated independently. It allows for more complex conditions such as comparisons, logical operators, and functions.

Example:

DECLARE
   product_price NUMBER := 120.50;
   product_category VARCHAR2(20);
BEGIN
   CASE
      WHEN product_price < 50 THEN
         product_category := 'Low Cost';
      WHEN product_price >= 50 AND product_price <= 100 THEN
         product_category := 'Medium Cost';
      WHEN product_price > 100 THEN
         product_category := 'High Cost';
      ELSE
         product_category := 'Invalid Price';
   END CASE;

   DBMS_OUTPUT.PUT_LINE('The product falls into the category: ' || product_category);
END;

In this example,

• The variable product_price is set to 120.50.
• The Searched CASE statement evaluates different conditions based on the value of product_price and assigns the appropriate category to the variable product_category.
• The output will be The product falls into the category: High Cost.

Output:

Statement processed.
The product falls into the category: High Cost

CASE Statement vs CASE Expression

Important Points About PL/SQL CASE Statement

• Both Simple and Searched CASE statements support an optional ELSE clause to handle situations where no conditions are met.
• CASE statement controls procedural logic within PL/SQL, while CASE expression is used within SQL queries like SELECT, WHERE, and ORDER BY.
• It is primarily used within PL/SQL procedural blocks but not in standalone SQL queries unless it is a CASE expression.
• CASE statements are used for decision-making within PL/SQL blocks, such as stored procedures, functions, and triggers.