oracle 的if...else 和 case

declare i varchar2(10);
begin
i:='0';
[color=red]if i='0' then [/color] dbms_output.put_line('正常');
[color=red] elsif i='1' then [/color] dbms_output.put_line('不可思议');
else
dbms_output.put_line('不知道');
end if;
end;


case when else 基本结构:
[color=red]case n
when 1 then Action1;
when 2 then Action2;
when 3 then Action3;
else ActionOther;
end;[/color]示例一：
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)
)