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 24000101 Neena 17000102 Lex 17000103 Alexander 9000104 Bruce 6000105 David 4800106 Valli 4800107 Diana 4200108 Nancy 12008109 Daniel 9000
-- 第一种使用游标
declare ISFlag varchar2(32);
beginfor cur_case_when_new in ( select EMPLOYEE_ID,FIRST_NAME,SALARY from employees where rownum<=10) loopISFlag :=case cur_case_when_new.SALARYWHEN >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 beginreturn case P_SALARYWHEN >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 high101 Neena 17000 high102 Lex 17000 high103 Alexander 9000 normal104 Bruce 6000 low105 David 4800 low106 Valli 4800 low107 Diana 4200 low108 Nancy 12008 normal109 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 high101 Neena 17000 high102 Lex 17000 high103 Alexander 9000 normal104 Bruce 6000 low105 David 4800 low106 Valli 4800 low107 Diana 4200 low108 Nancy 12008 normal109 Daniel 9000 normal10 rows selected.2.2、官方示例
DECLAREsalary NUMBER := 7000;salary_level VARCHAR2(20);
BEGINsalary_level :=CASE salaryWHEN 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
DECLAREdata_val NUMBER := 30;status VARCHAR2(20);
BEGINstatus :=CASE data_val/2WHEN < 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