📋Steps in this guide1/5

CASE Statement and CASE Expression Enhancements in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai the simple CASE statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN clause.

oracle 23configurationintermediate

by OracleDba

15 views

Setup

The examples in this article use the following table.

Code/Command (click line numbers to comment):

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 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 statement to evaluate a threshold. We are using a searched statement because most of the comparisons are not simple equality checks. This example uses a searched expression to do the same thing.

Code/Command (click line numbers to comment):

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>

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 23ai/26ai we can do the same thing using a simple statement or expression using dangling predicates. A dangling predicate is an expression with its left operand missing. This example uses a simple statement with dangling predicates to achieve the same result. Here is the simple expression equivalent.

Code/Command (click line numbers to comment):

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>

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 clause can include multiple equality checks or dangling predicates as a comma-separated list. In the following example we use a simple 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. This is the simple expression equivalent of the previous example.

Code/Command (click line numbers to comment):

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>

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 expressions. We can achieve a similar result by defining a function in the clause to perform the expression. For more information see: - CASE Statement - CASE Expressions - CASE Expressions And Statements in Oracle Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!