函数专题：CASE WHEN

case when 语句

一、概述
1.这个语句使用起来像decode函数加强版，9i之后的多重条件分支语句，decode配合例如sign类似的函数也能达到case when的效果，但是case when效率更高,需要注意的是，每个值返回的类型需要一致。
2.case语句可以实现IF语句的功能。
3.case语句分为单一选择符进行等值比较和多种条件进行非等值比较。
4.为了避免case_not_found异常，在编写case语句时应该带else子句


二、单一选择符进行等值比较
说明：当使用case语句执行多重条件分支时，如果条件选择符完全相同，并且条件表达式为相等条件选择。

declare
v_deptno emp.deptno%type;
begin
v_deptno:=&no;
case v_deptno
when 10 when update emp set comm=100 where deptno=v_deptno;
when 20 when update emp set comm=80 where deptno=v_deptno;
when 30 when update emp set comm=60 where deptno=v_deptno;
else dbms_output.put_line('不存在该部门');
end case;
end;


三、多种条件进行非等值比较
说明：对于包含有多种条件进行不等比较，那么必须在when子句中指定比较条件。

SELECT b.NAME area_name,b.ID area_id,3 TYPE,a.ID tran_id,a.NAME NAME,
CASE
WHEN a.area_id = 0 AND si_id = 0 THEN '省通用'
WHEN a.si_id = 0 THEN '市级用'
ELSE c.company
END company,
c.sp_id
FROM preferential_package a
LEFT JOIN qx_admin_si c ON a.si_id = c.ID
LEFT JOIN area b ON a.area_id = b.id
ORDER BY area_name, a.NAME;

SELECT town.area_id AS area_id,town.ID AS town_id,school.xtown_id,school.ID AS school_id,
COUNT (DISTINCT student.stu_sequence),
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 403 AND family.charge = 1 THEN family.stu_sequence
ELSE NULL
END) pak_403_chg,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 403 AND family.charge = 0 THEN family.stu_sequence
ELSE NULL
END) pak_403_try,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 402 AND family.charge = 1 THEN family.stu_sequence
ELSE NULL
END) pak_402_chg,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 402 AND family.charge = 0 THEN family.stu_sequence
ELSE NULL
END) pak_402_try,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 360 AND family.charge = 1 THEN family.stu_sequence
ELSE NULL
END) pak_360_chg,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 360 AND family.charge = 0 THEN family.stu_sequence
ELSE NULL
END) pak_360_try,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 404 THEN family.stu_sequence
ELSE NULL
END) pak_404,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 440 THEN family.stu_sequence
ELSE NULL
END) pak_440,
COUNT(CASE
WHEN NVL (package.pp_id, 0) = 420 THEN family.stu_sequence
ELSE NULL
END) pak_420
FROM fs_xj_family family LEFT JOIN fs_preferential_packager PACKAGE ON PACKAGE.f_id = family.ID AND PACKAGE.del = 1,
fs_xj_student student,fs_xj_stu_class stuclazz,xj_class clazz,xj_school school,town town
WHERE family.stu_sequence = stuclazz.stu_sequence
AND stuclazz.stu_sequence = student.stu_sequence AND stuclazz.class_id = clazz.ID
AND clazz.school_id = school.ID AND school.town_id = town.ID
AND NVL (town.is_test, 0) = 0 AND clazz.in_school = 1 AND clazz.class_type = 1 AND family.phonetype = 0
GROUP BY town.area_id,town.ID,school.xtown_id,school.ID;