CASE statement in Oracle PL / SQL
Oracle Database versions 9i and later include the ability to use the CASE statement within an SQL statement (SELECT, UPDATE, and so on). The CASE statement allows you to perform the same operations as the PL / SQL IF-THEN-ELSIF-ELSE control statements, but with the particularity that they can be used within an SQL statement. The syntax of the CASE statement is as follows:
CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
The expression option is optional. This is the value that we would be comparing with the list of conditions (condition_1, condition_2, …, condition_n).
The conditions, condition_1 to condition_n, are analyzed in the same order in which they are listed and, when one of these conditions is true, the CASE statement returns the corresponding result and stops analyzing the other conditions.
The results, result_1 to result_n, must all be of the same PLSQL data type. These are the values ​​returned by the CASE when oracle statement at the time a condition is true.
If none of the conditions is true, then the CASE statement returns the value of the ELSE clause. If the ELSE clause is omitted and no condition is true, then the CASE statement returns the value NULL.
Example of case statement in Oracle:
In a CASE statement, there can be up to 255 WHEN-THEN-ELSE clauses, therefore we can build up to 128 comparisons.
Below, you can see a couple of examples of a SELECT statement that include a CASE statement:
SELECT last name,
CASE name
WHEN 'Pepe' THEN 'is called Pepe'
WHEN 'Juan' THEN 'is called Juan'
ELSE 'is not called neither Pepe nor Juan'
END
FROM employees;
SELECT last name,
CASE
WHEN name = 'Pepe' THEN 'is called Pepe'
WHEN name = 'Juan' THEN 'is called Juan'
ELSE 'is not called neither Pepe nor Juan'
END
FROM employees;
Both SQL statements will return the same results even though the second statement was written without using the expression option.
The CASE statement admits multiple combinations, for example, more than one field can be used and the conditions that can be built can be as complex as we can think of. Let’s see another example:
SELECT last name, first name,
CASE
WHEN salary> payment_species
AND surname <'P' THEN 'is rich'
WHEN salary <species_payment
AND surname> = 'Q' THEN 'is poor'
ELSE 'is neither rich nor poor'
END
FROM employees;
In our example it would turn out that employees whose salary was greater than what they received in-kind and whose name began with a letter from A to O, would be considered wealthy, employees whose salary was less than what they received in-kind and whose name starts with a letter from Q to Z, they would be considered poor, and the rest of the employees would be considered neither rich nor poor.
Obviously, everything you put into SELECT will slow down its execution, but it does not have to be preferable to remove the CASE from SELECT. If in the end, we are going to have to perform the comparison (or we are going to have to execute the function) for all the records returned by the SELECT, the performance in both cases will be similar.
Conclusion: case in oracle sql is more effective:
The case statement in SQL is especially utilized in a case with equality expressions. The case in oracle sql statement is usually within a get list to change the output. What it does is evaluate an inventory of conditions and return one among the various possible result expressions.
Since this might be a touch out of the scope or comfort level of the essential query type, it’s also good that we become conversant in them because it’s likely that at some point you’ll encounter them, and it’s advisable to understand why they are and see the facility of the case statement in SQL.
This probably still doesn’t appear to be something right out of the box, but the likelihood is that you’re in a situation where you would like to form a choice on when statement. Therefore, the SQL CASE statement may be a great start. We’ve here may be a good analogy. In databases, a standard problem is what value you employ to represent a missing AKA value, Null Value. So here we will not do equalization because we will not say things like for instance “where a field is adequate to Null” since Null isn’t adequate to 0. This is often really an ideal example when using the case statement in SQL.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
