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
simple-case-statement2. 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-statementCASE 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. |
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.
Similar Reads
SQL CASE Statement
The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It allows you to return specific results based on certain conditions, enabling dynamic query outputs. Whether you need to create new columns, modify existing
4 min read
PL/SQL INSERT Statement
The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets
3 min read
PostgreSQL - CASE Statement
In PostgreSQL, CASE statements provide a way to implement conditional logic within SQL queries. Using these statements effectively can help streamline database functions, optimize query performance, and provide targeted outputs. This guide will break down the types of CASE statements available in Po
5 min read
PL/SQL CONTINUE Statement
PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to the Oracle engine all at once which increases processing speed and decreases the traffic. A key component of loop control in PL/SQL is the
4 min read
PL/SQL DELETE Statement
In PL/SQL(Procedural Language/Structured Query Language), the DELETE statement is the powerful command used to remove one or more records from the database table. It is an essential part of database management and enables the users to efficiently manage and maintain the data integrity by selectively
7 min read
PL/SQL GOTO Statement
PL/SQL also known as Procedural Language/Structured Query Language, PL/SQL is a powerful programming language used in Oracle databases to do interaction with data. One of its features is the GOTO statement, which helps control how a program flows by allowing it to jump to specific statements within
5 min read
PL/SQL CREATE TABLE Statement
PL/SQL CREATE TABLE statement is a fundamental aspect of database design and allows users to define the structure of new tables, including columns, data types, and constraints. This statement is crucial in organizing data effectively within a database and providing a blueprint for how data should be
4 min read
SQL SELECT IN Statement
The IN operator in SQL is used to compare a column's value against a set of values. It returns TRUE if the column's value matches any of the values in the specified list, and FALSE if there is no match. In this article, we will learn how IN operator works and provide practical examples to help you b
4 min read
SQL CREATE VIEW Statement
The SQL CREATE VIEW statement is a very powerful feature in RDBMSs that allows users to create virtual tables based on the result set of a SQL query. Unlike regular tables, these views do not store data themselves rather they provide a way of dynamically retrieving and presenting data from one or ma
4 min read
PL/SQL UPDATE Statement
The UPDATE statement in the PL/SQL(Procedural Language/ Structural Query Language) is the powerful SQL (Structured Query Language) command used to modify the existing data in the database table. In this article, we will explain the PL/SQL UPDATE Statement, its syntax, and examples in detail. PL/SQL
7 min read