[Oracle 9i] Case Expression and Case Statement in 9i
Adrian Billington 有一篇介绍Case Expression 和 Case Statement 的文章,写得很好,参见这里。
用过Case 很多次了,但是并没有很系统地学习过,看了他的这篇文章终于明白Case Expression和Case Statement的区别:
1. Case Expression
可以用在SQL和PL/SQL中,关于Case Expression最重要的一点就是,这个Case语句最后是返回一个值的,不管是在SQL语句的Select, Order By, Where 字句中,归根结底每个Case Expression都是要返回一个值。
而且,用法上面,Case Expression 的结束标志是END, 不是END CASE!这点很重要,经常搞混淆了!
-- Simple Case ExpressionCASE {value or expression} WHEN {value} THEN {something} [WHEN...] [THEN...] [ELSE...] --<-- NULL if not specified and no WHEN tests satisfied END
-- Searched Case Expression
CASE WHEN {test or tests} THEN {something} [WHEN {test or tests}] [THEN...] [ELSE...] END
-- Case Expression in pl/sql
variable := CASE something WHEN something THEN value ELSE alternative_value END;
除了最常见的CASE Expression用在Select语句中,Case Expression还可以用在Order By字句中来实现“非一般”的排序效果!
SQL> SELECT ename 2 , job 3 FROM emp 4 ORDER BY CASE job 5 WHEN 'PRESIDENT' 6 THEN 1 7 WHEN 'MANAGER' 8 THEN 2 9 WHEN 'ANALYST' 10 THEN 3 11 WHEN 'SALESMAN' 12 THEN 4 13 ELSE 5 14 END; ENAME JOB ---------- --------- KING PRESIDENT JONES MANAGER BLAKE MANAGER CLARK MANAGER SCOTT ANALYST FORD ANALYST ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN SMITH CLERK MILLER CLERK ADAMS CLERK JAMES CLERK 14 rows selected.
2. Case Statement
只可以用在pl/sql中,不能用在SQL中,结束标志是END CASE!Case Statement 相当于一组IF…THEN…ELSE…END IF语句,不需要返回值,只是在条件匹配的时候执行某种操作而已,所以在每个WHEN…THEN之后需要有一个分号,表示一条执行语句!
CASE {variable or expression} WHEN {value} THEN {one or more operations}; [WHEN..THEN] ELSE {default operation}; END CASE;
CASE WHEN {expression test or tests} THEN {one or more operations}; [WHEN..THEN] ELSE {default operation}; END CASE;
--------------------------------------
Regards,
FangwenYu
![](https://pic.cnblogs.com/avatar/93867/20191016181427.png)