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;