Oracle23ai新特性case when子句增加多条件判断

Oracle23ai新特性case when子句增加多条件判断，语句更加灵活易用，更好支持SQL标准。

参考官方文档 CASE Expressions

https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-language-fundamentals.html#GUID-216F1B33-493F-4CDE-93BB-096BACA8523E

1、本文内容

• 新特性在Oracle23ai中简单使用
• 通用所有版本的基础写法

2、Oracle23ai新特性case when子句多条件判断

2.1、测试实例

TESTUSER@FREEPDB1> select EMPLOYEE_ID,FIRST_NAME,SALARY from employees where rownum<=10;

EMPLOYEE_ID FIRST_NAME                                                       SALARY
----------- ------------------------------------------------------------ ----------
        100 Steven                                                            24000
        101 Neena                                                             17000
        102 Lex                                                               17000
        103 Alexander                                                          9000
        104 Bruce                                                              6000
        105 David                                                              4800
        106 Valli                                                              4800
        107 Diana                                                              4200
        108 Nancy                                                             12008
        109 Daniel                                                             9000
-- 第一种使用游标
declare ISFlag varchar2(32);
begin
  for cur_case_when_new  in ( select EMPLOYEE_ID,FIRST_NAME,SALARY from employees where rownum<=10) 
  loop
    ISFlag :=
	case cur_case_when_new.SALARY
		WHEN >13000 THEN 'high'
		WHEN 9000, 12008 THEN 'normal'
		WHEN 4800,4200,6000 THEN 'low'
		ELSE 'executive pay'
	 end;
    dbms_output.put_line('员工编号:'||cur_case_when_new.EMPLOYEE_ID || '姓名：' ||cur_case_when_new.FIRST_NAME || ' 工资级别：' ||ISFlag);
  end loop;  
end;
/
-- Result:
员工编号:100 姓名：Steven 工资级别：high
员工编号:101 姓名：Neena 工资级别：high
员工编号:102 姓名：Lex 工资级别：high
员工编号:103 姓名：Alexander 工资级别：normal
员工编号:104 姓名：Bruce 工资级别：low
员工编号:105 姓名：David 工资级别：low
员工编号:106 姓名：Valli 工资级别：low
员工编号:107 姓名：Diana 工资级别：low
员工编号:108 姓名：Nancy 工资级别：normal
员工编号:109 姓名：Daniel 工资级别：normal

-- 第二种使用with function 子句
with function get_level(P_SALARY in number) return varchar2
is  
  begin
     return 
	 case P_SALARY
		WHEN >13000 THEN 'high'
		WHEN 9000, 12008 THEN 'normal'
		WHEN 4800,4200,6000 THEN 'low'
		ELSE 'executive pay'
	 end;
  end;
 select EMPLOYEE_ID,FIRST_NAME,SALARY,get_level(SALARY) as ISFlag from employees where rownum<=10;
 /
 
EMPLOYEE_ID FIRST_NAME                         SALARY ISFLAG
----------- ------------------------------ ---------- ------------------------------
        100 Steven                              24000 high
        101 Neena                               17000 high
        102 Lex                                 17000 high
        103 Alexander                            9000 normal
        104 Bruce                                6000 low
        105 David                                4800 low
        106 Valli                                4800 low
        107 Diana                                4200 low
        108 Nancy                               12008 normal
        109 Daniel                               9000 normal
-- 以上两种PL/SQL写法都是Oracle23ai新特性支持,之前版本Oracle19c/12c/11g都无法使用

2.2、Oracle23ai/19c/12c/11g同时支持语句

 select EMPLOYEE_ID,FIRST_NAME,SALARY,
        CASE 
	    WHEN SALARY >13000 THEN 'high'
		WHEN SALARY>=9000 and SALARY<=12008 THEN 'normal'
		WHEN SALARY>=4200 and SALARY<=6000 THEN 'low'
		ELSE 'executive pay' end as ISFlag 
 from employees where rownum<=10;

-- Result:
EMPLOYEE_ID FIRST_NAME                         SALARY ISFLAG
----------- ------------------------------ ---------- ------------------------------
        100 Steven                              24000 high
        101 Neena                               17000 high
        102 Lex                                 17000 high
        103 Alexander                            9000 normal
        104 Bruce                                6000 low
        105 David                                4800 low
        106 Valli                                4800 low
        107 Diana                                4200 low
        108 Nancy                               12008 normal
        109 Daniel                               9000 normal

10 rows selected.

2.2、官方示例

DECLARE
    salary NUMBER := 7000;
    salary_level VARCHAR2(20);
BEGIN
    salary_level :=
	CASE salary
		WHEN 1000, 2000 THEN 'low'
		WHEN 3000, 4000, 5000 THEN 'normal'
		WHEN 6000, 7000, 8000 THEN 'high'
		ELSE 'executive pay'
	END;
	DBMS_OUTPUT.PUT_LINE('Salary level is: ' || salary_level);
END;
/

-- Result:
Salary level is: high

DECLARE
    data_val NUMBER := 30;
    status VARCHAR2(20);
BEGIN
    status :=
	CASE data_val/2
		WHEN < 0, > 50 THEN 'outlier'
		WHEN BETWEEN 10 AND 30 THEN 'good'
		ELSE 'bad'
	END;
	DBMS_OUTPUT.PUT_LINE('The data status is: ' || status);
END;
/

-- Result:
The data status is: good