调试经验——使用Oracle CASE WHEN表达式实现对查询结果的二次处理（Customization of dataset retrieved using CASE WHEN function）

在开发Excel报表时，通常要根据用户需求对从数据库中提取出的数据集进行二次加工，如，将独立文件（flat file）中的数据通过vlookup合并到数据集，对个别字段的特殊处理等。这些手动操作耗时耗力不说，还很容易出错，导致返工，甚至破坏用户的信任感，所以应该尽量避免。有些操作可以在获取数据集的同时直接将处理逻辑写到SQL中。如，

/* Formatted on 2018/5/20 23:11:17 (QP5 v5.256.13226.35538) */
SELECT CASE
          WHEN TO_DATE ('2018-05-20', 'yyyy-mm-dd') < TO_DATE (TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd')
          THEN
             '往日不可追！'
          WHEN TO_DATE ('2018-05-20', 'yyyy-mm-dd') > TO_DATE (TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd')
          THEN
             '来日方长！'
          ELSE
             '活在当下！'
       END
          AS CASEWHEN
  FROM DUAL;

以下内容摘自《Oracle Database SQL Language Reference_11.2g》。

----------------------------------------------------------------------------------------------------------------

CASE Expressions

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having
to invoke procedures.

In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for
which expr is equal to comparison_expr and returns return_expr. If none of the WHEN
... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_
expr. Otherwise, Oracle returns null.
In a searched CASE expression, Oracle searches from left to right until it finds an
occurrence of condition that is true, and then returns return_expr. If no condition is
found to be true, and an ELSE clause exists, then Oracle returns else_expr. Otherwise,
Oracle returns null.
Oracle Database uses short-circuit evaluation. For a simple CASE expression, the
database evaluates each comparison_expr value only before comparing it to expr,
rather than evaluating all comparison_expr values before comparing any of them with
expr. Consequently, Oracle never evaluates a comparison_expr if a previous
comparison_expr is equal to expr. For a searched CASE expression, the database
evaluates each condition to determine whether it is true, and never evaluates a
condition if the previous condition was true.
For a simple CASE expression, the expr and all comparison_expr values must either
have the same data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT,
or BINARY_DOUBLE) or must all have a numeric data type. If all expressions have a
numeric data type, then Oracle determines the argument with the highest numeric
precedence, implicitly converts the remaining arguments to that data type, and returns
that data type.
For both simple and searched CASE expressions, all of the return_exprs must either
have the same data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT,
or BINARY_DOUBLE) or must all have a numeric data type. If all return expressions have
a numeric data type, then Oracle determines the argument with the highest numeric
precedence, implicitly converts the remaining arguments to that data type, and returns
that data type.
The maximum number of arguments in a CASE expression is 65535. All expressions
count toward this limit, including the initial expression of a simple CASE expression
and the optional ELSE expression. Each WHEN ... THEN pair counts as two arguments. To

avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself
is a CASE expression.

Simple CASE Example For each customer in the sample oe.customers table, the
following statement lists the credit limit as "Low" if it equals $100, "High" if it equals
$5000, and "Medium" if it equals anything else.
SELECT cust_last_name,
CASE credit_limit WHEN 100 THEN 'Low'
WHEN 5000 THEN 'High'
ELSE 'Medium' END AS credit
FROM customers
ORDER BY cust_last_name, credit;
CUST_LAST_NAME CREDIT
-------------------- ------
Adjani Medium
Adjani Medium
Alexander Medium
Alexander Medium
Altman High
Altman Medium
. . .

Searched CASE Example The following statement finds the average salary of the
employees in the sample table oe.employees, using $2000 as the lowest salary
possible:
SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
ELSE 2000 END) "Average Salary" FROM employees e;
Average Salary
--------------
6461.68224