case when else 基本结构:
case n
  when 1 then Action1;
when 2 then Action2;
when 3 then Action3;
else        ActionOther;
end;
示例一：
SELECT col1, col2,  
       CASE  
          WHEN col3 > 1 AND col3 <2  
             THEN '1'  
          WHEN col3 > 2 AND col3 <3  
             THEN '2'  
          WHEN col3 > 3 AND col3 <4  
             THEN '3'  
          ELSE '4'  
       END mylevel  
FROM table_51xit
case when else语法要点说明如下：
1、以CASE开头，以END结尾
2、分支中WHEN 后跟条件，THEN为显示结果
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加
4、END 后跟别名
示例二：更复杂的示例代码：
select d.districtcode,(case when (substr(d.districtcode,3,6) = '0000') then ('100000'||substr(d.districtcode,1,2))
                     when (substr(d.districtcode,5,6) = '00') then ('100000'||substr(d.districtcode,1,2)||'00'||substr(d.districtcode,3,2))
                     else  ('100000'||substr(d.districtcode,1,2)||'00'||substr(d.districtcode,3,2)||'00'||substr(d.districtcode,5,2))
                     end) unitcode From vcmdistrictcode d where
not exists
(
select 1 From vcmunitinfo u where u.unitdistrictcode=d.districtcode
and u.unitcode=(case when (substr(d.districtcode,3,6) = '0000') then ('100000'||substr(d.districtcode,1,2))
                     when (substr(d.districtcode,5,6) = '00') then ('100000'||substr(d.districtcode,1,2)||'00'||substr(d.districtcode,3,2))
                     else  ('100000'||substr(d.districtcode,1,2)||'00'||substr(d.districtcode,3,2)||'00'||substr(d.districtcode,5,2))
                     end)
)
示例二：
===============一个完整的实例简要介绍case函数的用法
1.创建测试表:
DROP SEQUENCE student_sequence;
CREATE SEQUENCE student_sequence  START WITH 10000  INCREMENT BY 1;
DROP TABLE UserInfo;
CREATE TABLE UserInfo (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3),
  grade     varchar2(2));

INSERT INTO UserInfo (id, first_name, last_name, major, current_credits,grade)
  VALUES (student_sequence.NEXTVAL, 'Scott', 'Smith', 'Computer Science', 98,null);
INSERT INTO UserInfo (id, first_name, last_name, major, current_credits,grade)
  VALUES (student_sequence.NEXTVAL, 'Margaret', 'Mason', 'History', 88,null);
INSERT INTO UserInfo (id, first_name, last_name, major, current_credits,grade)
  VALUES (student_sequence.NEXTVAL, 'Joanne', 'Junebug', 'Computer Science', 75,null);
INSERT INTO UserInfo (id, first_name, last_name, major, current_credits,grade)
  VALUES (student_sequence.NEXTVAL, 'Manish', 'Murgratroid', 'Economics', 66,null);
commit;

2.查看相应数据
SQL> select * from UserInfo;
        ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS GR
---------- -------------------- -------------------- ------------------------------ --------------- --
     10000 Scott                Smith                Computer Science                            98
     10001 Margaret             Mason                History                                     88
     10002 Joanne               Junebug              Computer Science                            75
     10003 Manish               Murgratroid          Economics                                   66

3.更新语句
update UserInfo
set grade = (
select grade from
(
select id,
case when current_credits > 90 then 'a'
     when current_credits > 80 then 'b'
     when current_credits > 70 then 'c'
else 'd' end grade
from UserInfo
) a
where a.id = UserInfo.id
)
/
4.更新后结果
SQL> select * from UserInfo;
        ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS GR
---------- -------------------- -------------------- ------------------------------ --------------- --
     10000 Scott                Smith                Computer Science                            98 a
     10001 Margaret             Mason                History                                     88 b
     10002 Joanne               Junebug              Computer Science                            75 c
     10003 Manish               Murgratroid          Economics                                   66 d