Updated March 17, 2023
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.
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;
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.
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.
CASE expression WHEN condition_1 THEN statements1 WHEN condition_2 THEN statements2 ... ELSE statements_else END CASE;
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
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.
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;
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.
We hope that this EDUCBA information on “CASE statement in PL/SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.