EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial CASE statement in PL/SQL
Secondary Sidebar
Ubuntu Budgie

Address Binding in an Operating System

Normal and Trace of a Matrix in Java

Preemptive vs Non-Preemptive Scheduling

Right View of a Binary Tree in Java

How to Install Python on Linux

CASE statement in PL/SQL

By Priya PedamkarPriya Pedamkar

CASE statement in PL/SQL

Introduction to Case Statement in PL/SQL

The CASE statement in PL/SQL is similar to the IF-ELSEIF condition, where different conditions are listed within a block, and only that statements get executed for which it satisfies the condition. CASE statement matches the expression value instead of a Boolean expression. The expression could be of any data type, and the same data type has to be used in the statements. Each statement has a predefined value, and if that value matches up with the values passed in the selector expression, then that particular statement gets executed. In this topic, we are going to learn about the CASE statement in PL/SQL.

Syntax

Now that you have got a gist of what a CASE statement in PL/SQL is, let’s look at the CASE statement’s syntax.

CASE (expression)
WHEN <value_l> THEN statement_l;
WHEN <value_2> THEN statement_2;
WHEN <value_3> THEN statement_3;
ELSE default_statement;
END CASE;

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,560 ratings)

Code Explanation

In the syntax above, the expression will return a value of any data type; all the statements will have a predefined unique value, the CASE expression will go through all the statements until it gets the exact match defined in the value expression, in this case, Value_1, Value_2, and Value_3. If the Case expression didn’t find the match in the statement, then the default statement will get executed.

Note that the ELSE block is optional, and if you do not want any default statement to get executed, you can skip this statement. The END Case is a mandatory part of the case.

Flow Diagram

Flow Diagram

How does the Case Statement Work in PL/SQL?

Though the CASE statement was introduced in the Oracle 9i, it has been widely used in other programming languages. Like other programming languages, the CASE statement works in a similar way.

Oracle 9i supports two types of CASE statements simple CASE statement and searched CASE statement.

1. Simple Case Statement

A statement gets executed in the simple case statement if the expression value matches up with the stated condition. These statement conditions are predefined while writing the CASE statement. Once the compiler finds a match, it breaks and comes out of the loop after executing the statement avoiding further unnecessary expression evaluation. If the compiler did not find any match, it would execute the statement of the default case. The default case is not mandatory and can be skipped.

Syntax

CASE expression
WHEN condition_1 THEN
statements1
WHEN condition_2 THEN
statements2
...
ELSE
statements_else
END CASE;

Explanation

As mentioned earlier, the ELSE case is optional. The compiler first evaluates the CASE expression; then, it compares the expression with the first condition. If the condition matches up with the expression statement, 1 will get executed; otherwise, condition 2 is checked and so forth.

Let’s see some real-time example to understand the concept more clearly

SELECT Employee_Name,
CASE rating
WHEN 1 THEN 'Low'
WHEN 3 THEN 'High'
ELSE 'Medium' END
FROM Review;

Let’s assume a review table has employee year-end feedback details, and you have to show the employee name and ratings. But instead of numbers, you want to show Low of the rating is 1, High of the rating is 3, otherwise medium.

The query written above will produce the below results

Employee_Name       CASECR

——————– ——

Bogart               Low

Nolte                Medium

Loren                Medium

Gueney               High

2. Searched CASE Statement

The searched CASE statement is slightly different than the simple CASE statement. In the Searched Case Statement, instead of a predefined condition, you can write a condition that will get evaluated at the run time.

let’s have a look at the Searched CASE statement syntax with this example

CASE
WHEN salary >= 10000 AND salary <=20000 THEN
AllocateBonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000 THEN
AllocateBonus(employee_id, 1000);
WHEN salary > 40000 THEN
AllocateBonus(employee_id, 500);
WHEN salary > 60000 THEN
AllocateBonus(employee_id, 250);
ELSE
AllocateBonus(employee_id, 0);
END CASE;

Assume that the requirement is to allocate bonuses to an employee based on his / her current salary range. If an employee falls into certain criteria, the AllocateBonus function should get called, which takes employee_id and amount bonus as the parameters.

In the example above, we have defined different salary ranges and based on that  AllocateBonus function will get called if the salary falls within the specified range.

Note: In each iteration, one and only one statement will get executed even though multiple statements satisfy the criteria. When clauses are always evaluated in order from top to bottom.

Tips

Since the WHEN clause is evaluated in a certain order, i.e. from top to bottom, it would be recommended to list down the clauses that are most likely to occur first or frequently. In addition to that of any WHEN clause is an expensive, i.e. expression that requires a lot of CPU memory, should be present at the last in order to minimize the chances of getting executed.

3. Nested CASE Statement

Like the nested IF-ELSE statement, the CASE statement can also be nested. The syntax remains the same, just that when the outer condition satisfies the criteria, it again goes through a series of statements. Let’s improvise the above-Searched CASE example to understand how the Nested CASE statement works.

CASE
WHEN salary >= 10000 THEN
CASE
WHEN salary <= 20000 THEN
AllocateBonus(employee_id, 1500);
WHEN salary > 20000 THEN
AllocateBonus(employee_id, 1000);
END CASE;
WHEN salary > 40000 THEN
AllocateBonus(employee_id, 500);
WHEN salary < 10000 THEN
give_bonus(employee_id,0);
END CASE;

Explanation

As you see, the syntax remains the same, just that when the condition satisfies, it again go through a CASE statement to finally execute the Allocate function.

Conclusion – CASE statement in PL/SQL

Now that we have reached the end of the article, let’s summarize the key points that we have discussed in this article. We have seen what a CASE statement in PL/SQL is, learned the basic syntax with an example. We have discussed the different types of CASE statement, i.e. simple CASE, Searched CASE and the Nested CASE statement with an example. We have also learned how to optimize the CASE statement by ordering the WHEN clause.

Recommended Articles

This is a guide to the CASE statement in PL/SQL. Here we discuss how the Case Statement works in PL/SQL with the Examples and Syntax. You may also have a look at the following articles to learn more –

  1. What is PL/SQL?
  2. PL/SQL Commands
  3. Careers in PL/SQL
  4. Loops in PL/SQL
Popular Course in this category
PL SQL Training (4 Courses, 2+ Projects)
  4 Online Courses |  2 Hands-on Projects |  17+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more