oracle case variable assignment

• Oracle PL/SQL Tutorial
• Introduction
• Query Select
• Insert Update Delete
• Table Joins
• SQL Data Types
• Character String Functions
• Aggregate Functions
• Date Timestamp Functions
• Numerical Math Functions
• Conversion Functions
• Analytical Functions
• Miscellaneous Functions
• Regular Expressions Functions
• Statistical Functions
• Linear Regression Functions
• PL SQL Data Types
• PL SQL Statements
• PL SQL Operators
• PL SQL Programming
• Collections
• Function Procedure Packages
• SQL PLUS Session Environment
• System Tables Data Dictionary
• System Packages
• Object Oriented
• Large Objects
• Transaction
• User Privilege

Variable assignment with case statement : CASE « PL SQL Statements « Oracle PL/SQL Tutorial

Home » PL/SQL Tutorial » PL/SQL CASE Statement

PL/SQL CASE Statement

Summary : in this tutorial, you will learn how to use the PL/SQL CASE statement to control the flow of a program.

The CASE statement chooses one sequence of statements to execute out of many possible sequences.

The CASE statement has two types: simple CASE statement and searched CASE statement. Both types of CASE statements support an optional ELSE clause.

Simple PL/SQL CASE statement

A simple CASE statement evaluates a single expression and compares the result with some values.

The simple CASE statement has the following structure:

Let’s examine the syntax of the simple CASE statement in detail:

1) selector

The selector is an expression that is evaluated once. The result of the selector is used to select one of the several alternatives e.g., selector_value_1 and selector_value_2 .

2) WHEN selector_value THEN statements

The selector values i.e., selector_value_1 , selector_value_2 , etc., are evaluated sequentially. If the result of a selector value equals the result of the selector , then the associated sequence of statements executes and the CASE statement ends. In addition, the subsequent selector values are not evaluated.

3) ELSE else_statements

If no values in WHERE clauses match the result of the selector in the CASE clause, the sequence of statements in the ELSE clause executes.

Because the ELSE clause is optional, you can skip it. However, if you do so, PL/SQL will implicitly use the following:

In other words, PL/SQL raises a CASE_NOT_FOUND error if you don’t specify an ELSE clause and the result of the CASE expression does not match any value in the WHEN clauses.

Note that this behavior of the CASE statement is different from the IF THEN statement . When the IF THEN statement has no ELSE clause and the condition is not met, PL/SQL does nothing instead raising an error.

Simple CASE statement example

The following example compares single value ( c_grade ) with many possible values ‘A’, ‘B’,’C’,’D’, and ‘F’:

Searched CASE statement

The searched CASE statement evaluates multiple Boolean expressions and executes the sequence of statements associated with the first condition that evaluates to TRUE .

The searched CASE statement has the following structure:

The searched CASE statement follows the rules below:

• The conditions in the WHEN clauses are evaluated in order, from top to bottom.
• The sequence of statements associated with the WHEN clause whose condition evaluates to TRUE is executed. If more than one condition evaluates to TRUE, only the first one executes.
• If no condition evaluates to TRUE, the else_statements in the ELSE clause executes. If you skip the ELSE clause and no expressions are TRUE, a CASE_NOT_FOUND exception  is raised.

Searched CASE statement example

The following example illustrates how to use the searched CASE statement to calculate sales commission based on sales revenue.

In this example, the sales revenue was set to 150,000. The first expression evaluated to FALSE:

But the second expression evaluates to TRUE and the sale commission was set to 15%:

PL/SQL stops evaluating the subsequent condition once it finds the first condition that evaluates to TRUE. Therefore, in this example, PL/SQL will never evaluate the last two conditions in the CASE statement. The ELSE statement clause will also never execute.

Simple or searched CASE statement

As a rule of thumb, use a searched CASE statement when you want to execute a sequence of statements based on the results of multiple Boolean expressions and use a simple CASE statement when you want to execute a sequence of statements based on the result of a single expression.

PL/SQL CASE statement vs. CASE expression

PL/SQL also has CASE expression which is similar to the CASE statement.

A CASE expression evaluates a list of conditions and returns one of multiple possible result expressions.

The result of a CASE expression is a single value whereas the result of a CASE statement is the execution of a sequence of statements.

In this tutorial, you have learned how to use the PL/SQL CASE statement to control the flow of a program.

• Skip to content
• Accessibility Policy
• Oracle blogs
• Lorem ipsum dolor

IF-THEN logic in SELECT and WHERE with CASE expressions in Oracle SQL

There’s no if keyword in SQL. If you want to do if-else-then logic in select , where or anywhere else in a statement, you need a case expression .

This is a series of when clauses that the database runs in order:

For example, if you want to map exam correct percentages to grade letters according to these rules:

• 90% or greater => A
• 80% or greater but less than 90% => B
• 70% or greater but less than 80% => C
• 60% or greater but less than 70% => D
• 50% or greater but less than 60% => E
• Less than 50% => F

You can use a case expression like this:

The database processes the expression from top-to-bottom. It returns the value for the first when clause that is true. If none are true (the percentage is less than 50 or null), it returns the value in the else clause which is F.

For example, if a student scored 75% correct on an exam the database runs these operations:

• >= 90 => false
• >= 80 => false
• >= 70 => true => return C

The order of clauses is important! If you reverse them in the case like this:

If a student scores 75%, then the database processes:

• < 50 => false
• >= 50 => true => return E

Thus everyone scoring 50% or higher will get grade E!

Ensuring the when clauses are in the correct order is one of many things to be aware of when writing case expressions. In this post we’ll dive into:

• Simple case expressions
• Searched case expressions
• How to use case in select
• How to use case in where
• How to use case in joins
• Case in PL/SQL

If you'd like to see a demo, check the recording of the Ask TOM Office Hours on case expressions. Finally get the scripts and the case in SQL cheat sheet .

Map key-value pairs with simple CASE expressions

With a simple case you can check if an input equals any listed key. If it does you can return a corresponding value.

This structure of this is:

• Evaluates <selector> once
• Works down the list of when clauses, stopping on the first key that equals the <selector> and returning the corresponding value
• If the  <selector> matches none of the clauses, if you have an else clause it returns <else_value> . Otherwise it returns <null> .

For example, to map exam ids to names according to these rules:

• 1 => SQL
• 2 => Java
• 3 => Python
• 4 => JavaScript

You can use a simple expression like :

This has no else clause, so returns null for values of exam_id other than 1, 2, 3, or 4.

CASE vs DECODE

A simple case expression is like decode . This also has one selector followed by a list of key-value pairs. Instead of the expression above, you could use this decode logic:

Choosing between case and decode in these situations is down to personal preference. They offer the same functionality and performance. Personally I prefer case expressions, because I find it easier to understand. They’re also easier to adapt if you need to change the expression to have range or other non-equality comparisons.

To do this you need a searched case expression.

Map complex conditions to values with searched CASE expressions

The format for a searched case expression is the syntax shown above:

The core differences between this and simple expressions are:

• You can use multiple selectors
• The selectors are evaluated in every when clause the database runs
• Any SQL conditions that return true or false are possible in the <boolean conditions>

The exam percentage to grade letter at the start of this posted used a searched expression.

Let’s expand its requirements. The application uses the id zero for test entities. The expression must return TEST if the result is for either the test exam or test student.

To do this, you need update the case to check these values.

There are several ways you can do this. One is to add the student and exam ids checks in separate when clauses at the top:

Or you could have one clause that checks both the exam and student ids. Then place the original expression in the else clause:

Nesting cases like this can make sense if you've made a function for the percentages to letter conversions. For example:

How you decide to structure complex case expressions is a combination of:

• Clarity – some ways will be easier to understand later
• Performance – the database stops processing case when it finds the first true condition. Ordering the clauses so the most likely are at the top can make the expression faster. In most cases, this benefit is tiny so only worth considering when speed is critical.

With your case expression in hand, using it is a matter of pasting it in the appropriate place in your SQL.

How to use CASE for IF-THEN logic in SQL SELECT

If you want to see the grade for each exam, select the case expression like a regular column:

It’s a good idea to give the expression an alias. This is particularly important if the case is in a subquery.

How to use CASE in the WHERE clause

If you want to find all the exam results with A or B grades, you can place the select above in a subquery. Then filter it in the outer query.

Or you can slap the case expression directly in the where clause, like so:

Using columns in case expressions like this limits the optimizer’s ability to use indexes on those columns. To overcome this, you can index the expression itself:

How to join to a CASE expression

You can also join tables using case . For example, you may have a table of exam grades you want to link to the exam results.

Use the case expression in the join clause like so:

As with where , joining to  case limits the optimizer. If you find yourself using case in many joins, you may want to rethink your data model!

Make reusable CASE expressions with virtual columns

Writing case expressions directly in SQL statements is fine for one-off logic. Often you’ll want to reuse this logic in many queries. Copy-pasting it into every statement is error-prone and hard to maintain.

You can avoid these problems by moving the logic into the table definition with virtual columns .

This statement adds a column mapping percentages to grades:

With this in place, you then access the expression like a regular column:

If you need to use this logic in many tables you could place it in a PL/SQL function. Then call this function in your SQL:

From Oracle Database 23ai , the automatic SQL transpiler can extract SQL expressions in PL/SQL. These are then part of the SQL statement, so at runtime it's as-if the function doesn't exist!

To do this, ensure the sql_transpiler parameter is on (it's off by default). When a function in the where clause is transpiled, you can see the case expression instead of the function in the predicate section of the plan:

There are a few differences between case in PL/SQL and Oracle SQL .

Using case in PL/SQL

There are several enhancements to case available in PL/SQL:

• case statements
• Extended case controls (from 23ai)

Case statements in PL/SQL

A case expression returns a single value. In PL/SQL you can write a case statement to run one or more actions.

The differences between case expressions and statements are:

• You complete them with end case (instead of just end )
• Each then/else clause contains a statement, rather than returning a value

For example, you could write a statement that assigns grade letters and pass/fail outcomes to variables:

Functionally these are similar to a series if-elsif statements in PL/SQL.

So you may be wondering why bother with case statements?

Case statements can raise a case_not_found exception. This happens if they run without processing one of the clauses. i.e. none of the when conditions are true and there’s no else clause.

There is no such exception for if-elsif blocks. If you have a series like this:

The database will continue processing if none are true.

Thus case statements are useful when you want to ensure that a block of code always runs an action.

Extended case controls

The original logic to map exam results to grades checked the percent_correct in every clause. Duplicating it in every position means the database evaluates it for every when clause run.

If it’s a regular table column this is no big deal. But what if its calls a slow function?

It would be better to list and check it once, like a simple case expression.

From Oracle Database 23ai you can get the “best of both” with extended case controls.

These enable you to:

• List and evaluate the selector once
• Compare it using all SQL conditions

For the comparison, the selector is implicitly on the left-hand side. You just list the condition and the values to compare it to.

For example, let’s extend the exam result to grade logic to also return:

• A* if the result is 100%
• U if the percentage is null or zero

This enhancement is currently only in PL/SQL.

See CASE statements in action

I walked through the details of case statements in August 2023’s SQL Office Hours . Watch the recording here:

Try them out yourself with these Live SQL scripts . Use this script to create the tables shown in this post:

CASE expressions cheat sheet

Here's an overview of the different types of case in Oracle Database :

Chris Saxon

Developer advocate.

Chris Saxon is an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL!

To help you with this he blogs at All Things SQL . He also creates videos combining SQL and magic on YouTube at the The Magic of SQL .

If you have questions about working with Oracle Database technology, please reach out to him. You can do this via Twitter or on Ask Tom .

Previous Post

How to compare two tables to get the different rows with SQL

Announcing the 2023 oracle dev gym championships.

• Analyst Reports
• Cloud Economics
• Corporate Responsibility
• Diversity and Inclusion
• Security Practices
• What is Customer Service?
• What is ERP?
• What is Marketing Automation?
• What is Procurement?
• What is Talent Management?
• What is VM?
• Try Oracle Cloud Free Tier
• Oracle Sustainability
• Oracle COVID-19 Response
• Oracle and SailGP
• Oracle and Premier League
• Oracle and Red Bull Racing Honda
• US Sales 1.800.633.0738
• How can we help?
• Subscribe to Oracle Content
• © 2024 Oracle
• Privacy / Do Not Sell My Info

CASE Statement

The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE .

searched case statement ::=

simple case statement ::=

Keyword and Parameter Description

The value of the CASE operand and WHEN operands in a simple CASE statement can be any PL/SQL type other than BLOB , BFILE , an object type, a PL/SQL record, an index-by table, a varray, or a nested table.

If the ELSE clause is omitted, the system substitutes a default action. For a CASE statement, the default when none of the conditions matches is to raise a CASE_NOT_FOUND exception. For a CASE expression, the default is to return NULL .

Usage Notes

The WHEN clauses are executed in order. Each WHEN clause is executed only once. After a matching WHEN clause is found, subsequent WHEN clauses are not executed. You can use multiple statements after a WHEN clause, and that the expression in the WHEN clause can be a literal, variable, function call, or any other kind of expression. The WHEN clauses can use different conditions rather than all testing the same variable or using the same operator.

The statements in a WHEN clause can modify the database and call non-deterministic functions. There is no fall-through mechanism as in the C switch statement. Once a WHEN clause is matched and its statements are executed, the CASE statement ends.

The CASE statement is appropriate when there is some different action to be taken for each alternative. If you just need to choose among several values to assign to a variable, you can code an assignment statement using a CASE expression instead.

You can include CASE expressions inside SQL queries, for example instead of a call to the DECODE function or some other function that translates from one value to another.

Example 13-2 shows the use of a simple CASE statement.

Example 13-2 Using a CASE Statement

For examples, see the following:

Related Topics

Scripting on this page enhances content navigation, but does not change the content in any way.