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>
Posts
0 comments:
Post a Comment