赞
踩
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
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都无法使用
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.
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
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。