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. It is a block-structured language that combines SQL with the procedural features of programming languages.

In this article, we explore the CASE statement in PL/SQL. It is one of the most powerful conditional logic statements in Oracle databases. We look at the syntax, and types and compare them to the CASE expression. You can use this information to make better decisions and improve your ability to use conditional logic in Oracle PL/SQL.

CASE Statement

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;

Types of CASE Statement

There are two primary types of CASE statements in PL/SQL: Simple CASE and Searched CASE.

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

simple-case-statement

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

searched-case-statement

CASE Statement vs CASE Expression

CASE Statement	CASE Expression
CASE Statement is used to handle flow control within procedural code and it determines which code block to execute based on specified conditions.	CASE Expression in SQL is used for transforming or selecting values within a query and returning different results based on conditions.
It is limited to PL/SQL blocks such as procedures, functions, and blocks.	It is primarily used within SQL statements like SELECT, WHERE, and ORDER BY clauses.
It supports both Simple CASE and Searched CASE.	It only supports Searched CASE.
It supports ELSE clause for defining a default action.	It also supports ELSE clause for defining a default action.

Conclusion

Pl/SQL is a Procedural Language that is used to write program blocks, procedures, functions, cursors, triggers for databases. It provides a CASE statement feature that is used to handle conditional logic within PL/SQL blocks. CASE statement provides more clear and readable alternative to nested IF-ELSE statements. By efficiently managing branching logic, the CASE statement enhances code clarity and maintainability in PL/SQL programming.