您现在的位置是：首页 >学无止境 >Oracle Database 23c新特性之CASE语句/表达式增强网站首页学无止境

Oracle database 23c 改进了 PL/SQL 程序中的简单 CASE 语句和 CASE 表达式，支持悬空谓词（dangling predicate）和单个 WHEN 分支中的多项匹配。这个增强是为了更加符合 SQL 标准。

示例表

本文将会使用以下简单示例表：

drop table if exists test;
create table test (
  grade number
);

insert into test (grade)
values (-1), (50), (65), (75), (85), (95), (105);
commit;

已知问题

在 Oracle Database 21c 以及之前的版本中，简单 CASE 语句和表达式只能执行等值比较，非等值判断条件需要使用搜索 CASE 语句和表达式。例如：

set serveroutput on;
declare
  l_level varchar2(20);
begin
  for cur in (select grade from test) loop
    case 
      when cur.grade >100 then l_level := '数据错误';
      when cur.grade >= 90 then l_level := '优秀';
      when cur.grade >= 80 then l_level := '良好';
      when cur.grade >= 70 then l_level := '中等';
      when cur.grade >= 60 then l_level := '及格';
      when cur.grade >= 0 then l_level := '不及格';
      else l_level := '数据错误';
    end case;

    dbms_output.put_line(cur.grade || '：' || l_level);
  end loop;
end;
/

-1：数据错误
50：不及格
65：及格
75：中等
85：良好
95：优秀
105：数据错误

PL/SQL procedure successfully completed.

以上示例也可以通过搜索 CASE 表达式实现相同的结果：

set serveroutput on;
declare
  l_level varchar2(20);
begin
  for cur in (select grade from test) loop
    l_level := case 
                 when cur.grade >100 then '数据错误'
                 when cur.grade >= 90 then '优秀'
                 when cur.grade >= 80 then '良好'
                 when cur.grade >= 70 then '中等'
                 when cur.grade >= 60 then '及格'
                 when cur.grade >= 0 then '不及格'
                 else '数据错误'
               end;

    dbms_output.put_line(cur.grade || '：' || l_level);
  end loop;
end;
/

-1：数据错误
50：不及格
65：及格
75：中等
85：良好
95：优秀
105：数据错误

PL/SQL procedure successfully completed.

悬空谓词

悬空谓词是指缺失左操作数的表达式，例如 > 0。Oracle 23c 引入了悬空谓词，我们可以通过简单 CASE 语句和简单 CASE 表达式实现复杂的分支判断条件。

以下示例使用简单 CASE 语句实现了与上文相同的结果：

set serveroutput on;
declare
  l_level varchar2(20);
begin
  for cur in (select grade from test) loop
    case cur.grade
      when >100 then l_level := '数据错误';
      when >= 90 then l_level := '优秀';
      when >= 80 then l_level := '良好';
      when >= 70 then l_level := '中等';
      when >= 60 then l_level := '及格';
      when >= 0 then l_level := '不及格';
      else l_level := '数据错误';
    end case;

    dbms_output.put_line(cur.grade || '：' || l_level);
  end loop;
end;
/

-1：数据错误
50：不及格
65：及格
75：中等
85：良好
95：优秀
105：数据错误

PL/SQL procedure successfully completed.

简单 CASE 表达式实现相同结果的示例如下：

set serveroutput on;
declare
  l_level varchar2(20);
begin
  for cur in (select grade from test) loop
    l_level := case cur.grade
                 when >100 then '数据错误'
                 when >= 90 then '优秀'
                 when >= 80 then '良好'
                 when >= 70 then '中等'
                 when >= 60 then '及格'
                 when >= 0 then '不及格'
                 else '数据错误'
               end;

    dbms_output.put_line(cur.grade || '：' || l_level);
  end loop;
end;
/

-1：数据错误
50：不及格
65：及格
75：中等
85：良好
95：优秀
105：数据错误

PL/SQL procedure successfully completed.

单个 WHEN 分支中的多项匹配

新版本还增加了一个功能，单个 WHEN 子句中可以包含多个等值判断条件或者悬空谓词，使用逗号进行分隔。只要满足其中任何一个条件，就会执行相应的 THEN 分支语句或者返回对应的表达式结果。

以下示例使用简单 CASE 表达式进一步简化的判断条件：

set serveroutput on;
declare
  l_level varchar2(20);
begin
  for cur in (select grade from test) loop
    case cur.grade
      when >100, <0 then l_level := '数据错误';
      when >= 90 then l_level := '优秀';
      when >= 80 then l_level := '良好';
      when >= 70 then l_level := '中等';
      when >= 60 then l_level := '及格';
      when >= 0 then l_level := '不及格';
    end case;

    dbms_output.put_line(cur.grade || '：' || l_level);
  end loop;
end;
/

-1：数据错误
50：不及格
65：及格
75：中等
85：良好
95：优秀
105：数据错误

PL/SQL procedure successfully completed.

第一个 WHEN 分支包含了两个条件，大于 100 或者小于 0 的数据都会输出数据错误。

上面的示例也可以使用简单 CASE 表达式实现：

set serveroutput on;
declare
  l_level varchar2(20);
begin
  for cur in (select grade from test) loop
    l_level := case cur.grade
                 when >100, <0 then '数据错误'
                 when >= 90 then '优秀'
                 when >= 80 then '良好'
                 when >= 70 then '中等'
                 when >= 60 then '及格'
                 when >= 0 then '不及格'
               end;

    dbms_output.put_line(cur.grade || '：' || l_level);
  end loop;
end;
/

-1：数据错误
50：不及格
65：及格
75：中等
85：良好
95：优秀
105：数据错误

PL/SQL procedure successfully completed.

SQL 语句中的 CASE 表达式

目前，新版本增加的 CASE 表达式功能还不能在 SQL 语句中直接使用。

select grade,
       case cur.grade
         when >100, <0 then '数据错误'
         when >= 90 then '优秀'
         when >= 80 then '良好'
         when >= 70 then '中等'
         when >= 60 then '及格'
         when >= 0 then '不及格'
       end as grade
from test;

when >100, <0 then '数据错误'
                 *
ERROR at line 3:
ORA-02000: missing THEN keyword

我们可以利用 WITH 子句定义一个函数，在函数内使用新增的 CASE 表达式功能，然后在 SQL 查询中使用该函数：

with 
  function get_level(grade in number) return varchar2 is
  begin
    return case grade
             when >100, <0 then '数据错误'
             when >= 90 then '优秀'
             when >= 80 then '良好'
             when >= 70 then '中等'
             when >= 60 then '及格'
             when >= 0 then '不及格'
           end;
  end;
select grade, get_level(grade) as lvl
from test;
/

GRADE|LEVEL  |
-----+-------+
   -1|数据错误|
   50|不及格  |
   65|及格   |
   75|中等   |
   85|良好   |
   95|优秀   |
  105|数据错误|