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 have a look at the syntax of the CASE statement.

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;

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

How does the Case Statement Work in PL/SQL?

Though the CASE statement introduced in the Oracle 9i it has been widely used in the 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

In the simple case statement, a statement gets executed 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 will 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 there is a review table that has employee year-end feedback details and you have to show the employee name and the 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 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 which are most likely to occur first or frequency is more. In addition to that of any WHEN clause is an expensive i.e. expression which requires a lot of CPU memory should be present at the last in the 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 can we optimize the CASE statement by the right ordering of the WHEN clause.

Recommended Articles

This is a guide to the CASE statement in PL/SQL. Here we discuss that how does the Case Statement work 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. Uses of SQL
4. Careers in PL/SQL