8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
CASE Statement and CASE Expression Enhancements in Oracle Database 23c
In Oracle database 23c the simple CASE
statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN
clause.
This brings the PL/SQL simple CASE
statement and expression in line with the SQL:2003 Standard [ISO03a, ISO03b] standard.
Setup
The examples in this article use the following table.
drop table if exists t1 purge; create table t1 ( pct number ); insert into t1 (pct) values (-1), (0), (10), (40), (70), (80), (90), (100), (101); commit;
The Problem
In previous releases simple CASE
statements and expressions were only capable of performing equality checks. If we needed comparisons other than equality checks we would have to use a searched case statement or expression.
In the following example we use a searched CASE
statement to evaluate a threshold. We are using a searched CASE
statement because most of the comparisons are not simple equality checks.
set serveroutput on declare l_threshold varchar2(20); begin for cur_rec in (select pct from t1) loop case when cur_rec.pct = 40 then l_threshold := 'Optimal'; when cur_rec.pct <= 70 then l_threshold := 'Safe'; when cur_rec.pct <= 80 then l_threshold := 'Check'; when cur_rec.pct <= 90 then l_threshold := 'Warning'; when cur_rec.pct > 90 then l_threshold := 'Critical'; end case; dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold); end loop; end; / -1 : Safe 0 : Safe 10 : Safe 40 : Optimal 70 : Safe 80 : Check 90 : Warning 100 : Critical 101 : Critical PL/SQL procedure successfully completed. SQL>
This example uses a searched CASE
expression to do the same thing.
set serveroutput on declare l_threshold varchar2(20); begin for cur_rec in (select pct from t1) loop l_threshold := case when cur_rec.pct = 40 then 'Optimal' when cur_rec.pct <= 70 then 'Safe' when cur_rec.pct <= 80 then 'Check' when cur_rec.pct <= 90 then 'Warning' when cur_rec.pct > 90 then 'Critical' end; dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold); end loop; end; / -1 : Safe 0 : Safe 10 : Safe 40 : Optimal 70 : Safe 80 : Check 90 : Warning 100 : Critical 101 : Critical PL/SQL procedure successfully completed. SQL>
Dangling Predicates
In Oracle 23c we can do the same thing using a simple CASE
statement or expression using dangling predicates. A dangling predicate is an expression with its left operand missing.
This example uses a simple CASE
statement with dangling predicates to achieve the same result.
set serveroutput on declare l_threshold varchar2(20); begin for cur_rec in (select pct from t1) loop case cur_rec.pct when 40 then l_threshold := 'Optimal'; when <= 70 then l_threshold := 'Safe'; when <= 80 then l_threshold := 'Check'; when <= 90 then l_threshold := 'Warning'; when > 90 then l_threshold := 'Critical'; end case; dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold); end loop; end; / -1 : Safe 0 : Safe 10 : Safe 40 : Optimal 70 : Safe 80 : Check 90 : Warning 100 : Critical 101 : Critical PL/SQL procedure successfully completed. SQL>
Here is the simple CASE
expression equivalent.
set serveroutput on declare l_threshold varchar2(20); begin for cur_rec in (select pct from t1) loop l_threshold := case cur_rec.pct when 40 then 'Optimal' when <= 70 then 'Safe' when <= 80 then 'Check' when <= 90 then 'Warning' when > 90 then 'Critical' end; dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold); end loop; end; / -1 : Safe 0 : Safe 10 : Safe 40 : Optimal 70 : Safe 80 : Check 90 : Warning 100 : Critical 101 : Critical PL/SQL procedure successfully completed. SQL>
Multiple Choices in a Single WHEN Clause
A single WHEN
clause can include multiple equality checks or dangling predicates as a comma-separated list.
In the following example we use a simple CASE
statement to show an error if a value is below 0, exactly 0.5 or greater than 100. We also add values 41 and 42 to the optimal threshold.
set serveroutput on declare l_threshold varchar2(20); begin for cur_rec in (select pct from t1) loop case cur_rec.pct when < 0, 0.5, > 100 then l_threshold := 'Error'; when 40, 41, 42 then l_threshold := 'Optimal'; when <= 70 then l_threshold := 'Safe'; when <= 80 then l_threshold := 'Check'; when <= 90 then l_threshold := 'Warning'; when > 90 then l_threshold := 'Critical'; end case; dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold); end loop; end; / -1 : Error 0 : Safe 10 : Safe 40 : Optimal 70 : Safe 80 : Check 90 : Warning 100 : Critical 101 : Error PL/SQL procedure successfully completed. SQL>
This is the simple CASE
expression equivalent of the previous example.
set serveroutput on declare l_threshold varchar2(20); begin for cur_rec in (select pct from t1) loop l_threshold := case cur_rec.pct when < 0, 0.5, > 100 then 'Error' when 40, 41, 42 then 'Optimal' when <= 70 then 'Safe' when <= 80 then 'Check' when <= 90 then 'Warning' when > 90 then 'Critical' end; dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold); end loop; end; / -1 : Error 0 : Safe 10 : Safe 40 : Optimal 70 : Safe 80 : Check 90 : Warning 100 : Critical 101 : Error PL/SQL procedure successfully completed. SQL>
SQL CASE Expressions
This functionality is not supported directly by SQL CASE
expressions.
select pct, case pct when 0.5, < 0, > 100 then 'Error' when 40, 41, 42 then 'Optimal' when <= 70 then 'Safe' when <= 80 then 'Check' when <= 90 then 'Warning' when > 90 then 'Critical' end as status from t1; when 0.5, < 0, > 100 then 'Error' * ERROR at line 3: ORA-02000: missing THEN keyword SQL>
We can achieve a similar result by defining a function in the WITH
clause to perform the CASE
expression.
with function get_status(p_pct in number) return varchar2 is begin return case p_pct when < 0, 0.5, > 100 then 'Error' when 40, 41, 42 then 'Optimal' when <= 70 then 'Safe' when <= 80 then 'Check' when <= 90 then 'Warning' when > 90 then 'Critical' end; end; select pct, get_status(pct) as status from t1 / PCT STATUS ---------- ---------------------------------------- -1 Error 0 Safe 10 Safe 40 Optimal 70 Safe 80 Check 90 Warning 100 Critical 101 Error 9 rows selected. SQL>
For more information see:
Hope this helps. Regards Tim...