case expression and case statement - case表达式和case语句

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/10/21
Subject: case表达式(case expression)和case语句(case statement)
To: xiewenxiewen at gmail.com


case在ANSI标准(SQL92)中被制定，是一种多条件选择的分支结构，也可以用if-then-else结构来实现，但是case比if语句更加简洁，最佳实践(best practice)建议使用case

case分为case表达式(expression)和case语句(statement)两种，再继续划分为simple case和serached case两类

最初8i(8.1.6)的sql语句中支持了case表达式, 到了9i，pl/sql可以同时支持case表达式和case语句



1.case语句(case statement)

case语句是用于pl/sql中的多条件选择的分支结构，对多个条件做判断，然后执行相应的语句
分为simple case语句和searched case语句两种

a.simple case语句
语法:
CASE expression
WHEN result1 THEN
statement;
statement;
...
WHEN result2 THEN
statement;
statement;
...
ELSE
statement;
statement;
...
END CASE;
首先计算expression，然后跟result比较，如果相同则执行后面的statement，不再继续比较，如果不同则继续比较


举例:
set serveroutput on size unlimited
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');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END
CASE;
END;
/


ELSE子句是可选的，如果没写ELSE子句oracle会隐含的加上ELSE RAISE CASE_NOT_FOUND语句，当所有WHEN子句都不满足条件时，就抛出CASE_NOT_FOUND异常

DECLARE
grade CHAR(1);
BEGIN
grade := 'Z';
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;
/


捕获CASE_NOT_FOUND异常
DECLARE
grade CHAR(1);
BEGIN
grade := 'Z';
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;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such grade');
END;
/



b.searched case语句
语法:
CASE
WHEN expression1 THEN
statement;
statement;
...
WHEN expression2 THEN
statement;
statement;
...
ELSE
statement;
statement;
...
END CASE;

跟simple case一样，首先计算expression的值是真或是假，如果是真则执行后面的statement，不再计算后面的expression，如果是假则继续计算

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;
-- rather than using the ELSE in the CASE, could use the following
-- EXCEPTION
-- WHEN CASE_NOT_FOUND THEN
-- DBMS_OUTPUT.PUT_LINE('No such grade');
/



2.case表达式(case expression)

case表达式跟case语句差不多，但它返回的是一个值，case表达式可以用于sql语句中，pl/sql赋值语句等

case表达式可以在一条sql语句中完成if-then-else逻辑结构，从而无需使用复杂的pl/sql，跟decode()作用类似，语义更清晰扩展性更好功能更强

case表达式也分为2种, simple case表达式和searched case表达式

a.simple case表达式
语法是:
CASE expression
WHEN result1 THEN return_expresssion1
WHEN result2 THEN return_expresssion2
...
ELSE else_expression
END

注意，case表达式是以END结尾的，case语句是以END 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;
/


在sql语句中使用case表达式
set pages 9999 line 140
col object_name for a30
select object_name,
case object_type
when 'TABLE' then 'is a table'
when 'INDEX' then 'is a index'
else 'is not a table or index'
end
from user_objects
/


跟case语句不同，如果没有ELSE子句，且不满足任何条件，返回的是空值(NULL)，不会抛出异常
DECLARE
grade CHAR(1) := 'Z';
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'
END;
DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || NVL(appraisal,'Null!'));
END;
/


THEN或ELSE后面返回的结果不能都是空(You cannot specify the literal NULL for every return_expr and the else_expr.)

DECLARE
grade CHAR(1) := 'B';
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN NULL
WHEN 'B' THEN NULL
WHEN 'C' THEN NULL
WHEN 'D' THEN NULL
WHEN 'F' THEN NULL
END;
DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal);
END;
/


但是，用在sql语句里就可以都是空，不知道为什么？
select object_name,
case object_type
when 'TABLE' then NULL
when 'INDEX' then NULL
end
from user_objects;


b.searched case表达式
语法是:
CASE
WHEN expression1 THEN return_expresssion1
WHEN expression2 THEN return_expresssion2
...
ELSE else_expression
END

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 'N/A - Student not enrolled'
-- Have to test this condition early to detect good students with bad attendance
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;
/


用于sql语句
select max(case
when object_type not in ('TABLE','INDEX') then sysdate
else created
end)
from user_objects;


使用case将多条语句合并成一条
比如要统计object_id<16000,<20000和>=20000的数量，只用一条语句，只扫描了一次性能得到了优化
select count(case
when object_id<16000 then 1 end) count1,
count(case when object_id>=16000 and object_id<20000 then 1
end) count2,
count(case
when object_id>=20000 then 1 end) count3
from user_objects;



练习题：
1. 运行如下存储过程，得到什么结果
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
retval VARCHAR2 (100);
BEGIN
RETURN CASE
WHEN letter_in = 'A' THEN 'Apple'
WHEN letter_in = 'B' THEN 'Banana'
END;
END
fruit_translator;
/
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE ('Expression good for you = ' || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/

2. 运行如下存储过程，得到什么结果
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
retval VARCHAR2(100);
BEGIN
CASE
WHEN letter_in = 'A' THEN retval := 'Apple';
WHEN letter_in = 'B' THEN retval := 'Banana';
END CASE;
RETURN retval;
END fruit_translator;
/
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE ('Statement good for you = ' || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/

3. 下面用到的CASE是simple case还是searched case?
DECLARE
salary NUMBER := 20000;
employee_id NUMBER := 36325;
PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_id);
DBMS_OUTPUT.PUT_LINE(bonus_amt);
END;
BEGIN
CASE TRUE
WHEN salary >= 10000 AND salary <=20000 THEN give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000);
WHEN salary > 40000 THEN give_bonus(employee_id, 500);
ELSE give_bonus(employee_id, 0);
END
CASE;
END;
/




参考：
CASE Expressions
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions004.htm#sthref2637

CASE Statement
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/case_statement.htm#LNPLS01304

CASE Expressions
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref520

Using CASE Statements
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/controlstructures.htm#sthref912

Combine Multiples Scans with CASE Statements
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i26629

Oracle PL/SQL Programming By Steven Feuerstein, Bill Pribyl
http://books.google.com/books?id=Sqe-GHk_VJ0C&source=gbs_toc_r&cad=0_0&pg=PA92

What you Ought to Know About CASE in Oracle PL/SQL
http://awads.net/wp/2007/07/25/what-you-ought-to-know-about-case-in-oracle-plsql/




Xie Wen (谢文)
Network & Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box, 8th floor, Motorola Tower,
No. 1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R. China
e-mail wenxie at motorola.com




-fin-