CASE 语句从一序列的条件里选择并执行相应的语句。

简单 CASE 语句

最简单的 CASE 语句会计算一个单一表达式值并跟多个潜在值进行比较。语法格式如下：

CASE expression
WHEN value THEN statement
[ WHEN value THEN statement ]...
[ ELSE statement [, statement ]... ]
END CASE;

示例：使用简单 CASE 语句

delimiter /
CREATE OR REPLACE PROCEDURE sp_guess_abbr(p_abbr IN varchar)
IS
BEGIN
    CASE p_abbr
    WHEN 'CN' THEN 
        dbms_output.put_line('CHINA');
    WHEN 'UK' THEN
        dbms_output.put_line('ENGLISH');
    WHEN 'US' THEN   
        dbms_output.put_line('AMERICA');
    ELSE
        dbms_output.put_line('UNKOWN!');
    END CASE;   
END;
/
delimiter ;
obclient>
obclient> call sp_guess_abbr('UK');
Query OK, 0 rows affected (0.00 sec)
ENGLISH
obclient> call sp_guess_abbr('RU');
Query OK, 0 rows affected (0.01 sec)
UNKOWN!
obclient>

搜索的 CASE 语句

搜索的 CASE 语句中 CASE 后没有表达式要计算，而是 WHEN 后面分别有不同的布尔表达式。搜索的CASE会计算多个布尔表达式并选择第一个结果为 TRUE 的分支。语法格式如下：

CASE 
WHEN expression1 THEN statement
[ WHEN expressN THEN statement ]...
[ ELSE statement [, statement ]... ]
END CASE;

示例：使用搜索的 CASE 语句

delimiter /
CREATE OR REPLACE PROCEDURE sp_guess(p_value IN number)
IS
BEGIN
    CASE  
    WHEN p_value > 0 THEN 
        dbms_output.put_line('The value is a positive number. ');
    WHEN p_value < 0 THEN
        dbms_output.put_line('The value is a negative number. ');
    WHEN p_value = 0 THEN 
        dbms_output.put_line('The value is zero. ');
    ELSE
        dbms_output.put_line('The value is NULL. ');
    END CASE;   
END;
/
delimiter ;
obclient>
obclient> call sp_guess(2020);
Query OK, 0 rows affected (0.04 sec)
The value is a positive number.
obclient> call sp_guess(-2000);
Query OK, 0 rows affected (0.00 sec)
The value is a negative number.
obclient> call sp_guess(NULL);
Query OK, 0 rows affected (0.00 sec)
The value is NULL.
obclient>