Oracle CASE语句

CASE语句从一个条件序列中选择并执行相应的语句。CASE语句主要包含两种类型：

• 简单CASE语句，计算单个表达式值，并于几个可能值进行比较。
• 搜索CASE语句，计算多个布尔表达式，并选择第一个值为true。

CASE语句适合于根据每个选择执行不同的动作。

简单CASE语句：

CASE case_operand
WHEN when_operand THEN statement ;
[WHEN when_operand THEN statement ; ]...
[ELSE statement [ statement ]... ;
END CASE;

搜索CASE语句：

CASE
WHEN boolean_expression THEN statement ;
[WHEN boolean_expression THEN statement ; ]...
[ELSE statement [ statement ]... ;
END CASE;

ELSE statement [statement ]...

在简单CASE语句中，当且仅当case_operand条件在when_operand中没有匹配值时，才会执行。

在搜索CASE语句，当且仅当boolean_expression表达式没有true时，才会执行。

如果省略ELSE子句，而且有没有匹配的条件。系统会抛出一个CASE_NOT_FOUND异常。

CASE表达式

使用CASE语句的两种表达式类型：

简单CASE表达式:=CASE case_operand
WHEN when_operand THEN statement ;
[WHEN when_operand THEN statement ; ]...
[ELSE statement [ statement ]... ;
END CASE;

搜索CASE表达式:=CASE
WHEN boolean_expression THEN statement ;
[WHEN boolean_expression THEN statement ; ]...
[ELSE statement [ statement ]... ;
END CASE;

Examples

1.简单CASE语句

declare
grade char(1);
begin
grade := 'B';

case grade
when 'A' then
dbms_output.put_line('Excellent');
when 'B' then
dbms_output.put_line('Very Good');
when 'C' then
dbms_output.put_line('Good');
when 'D' then
dbms_output.put_line('Fair');
when 'F' then
dbms_output.put_line('Poor');
end case;
end;

输出结果：
Very Good

2.搜索CASE语句

declare
grade char(1);
begin
grade := 'B';

case
when grade = 'A' then
dbms_output.put_line('Excellent');
when grade = 'B' then
dbms_output.put_line('Very Good');
when grade = 'C' then
dbms_output.put_line('Good');
when grade = 'D' then
dbms_output.put_line('Fair');
when grade = 'F' then
dbms_output.put_line('Poor');
else
dbms_output.put_line('No such grade');
end case;
end;

输出结果：
Very Good

3.搜索CASE语句(没有else子句)

declare
grade char(1);
begin
grade := 'E';

case
when grade = 'A' then
dbms_output.put_line('Excellent');
when grade = 'B' then
dbms_output.put_line('Very Good');
when grade = 'C' then
dbms_output.put_line('Good');
when grade = 'D' then
dbms_output.put_line('Fair');
when grade = 'F' then
dbms_output.put_line('Poor');
end case;
exception
when case_not_found then
dbms_output.put_line('No such grade');
end;

输出结果：
No such grade

3.简单CASE表达式

declare
grade char(1) := 'B';
appraisal varchar2(20);
begin
appraisal := case grade
when 'A' then
'Excellent'
when 'B' then
'Very Good'
when 'C' then
'Good'
when 'D' then
'Fair'
when 'F' then
'Poor'
else
'No such grade'
end;
dbms_output.put_line('Grade ' || grade || ' is ' || appraisal);
end;

输出结果：

Grade B is Very Good

4.搜索CASE表达式

declare
grade char(1) := 'B';
appraisal varchar2(120);
id number := 8429862;
attendance number := 150;
min_days constant number := 200;

function attends_this_school(id number) return boolean is
begin
return true;
end;

begin
appraisal := case
when attends_this_school(id) = false then
'Student not enrolled'
when grade = 'F' or attendance < min_days then
'Poor (poor performance or bad attendance)'
when grade = 'A' then
'Excellent'
when grade = 'B' then
'Very Good'
when grade = 'C' then
'Good'
when grade = 'D' then
'Fair'
else
'No such grade'
end;
dbms_output.put_line('Result for student ' || id || ' is ' || appraisal);
end;

输出结果：

Result for student 8429862 is Poor (poor performance or bad attendance)

如果是SQLPLUS中运行上面语句，默认是不会显示输出结果的。需要开启显示：

SQL> set serveroutput on;