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
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
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.