oracle中的条件表达式 case 表达式

r囧r小猫 2022-05-12 14:40 336阅读 0赞

[https://docs.oracle.com/cd/B19306\_01/server.102/b14200/expressions004.htm][https_docs.oracle.com_cd_B19306_01_server.102_b14200_expressions004.htm]  
CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures. The syntax is:  
**CASE表达式可以让你在SQL语句中使用IF... THEN... ELSE的逻辑，而不必调用过程。它的语法是如下**

![case_expression.gif][]

**CASE   
     \{** **simple\_case\_expression****|****searched\_case\_expression****\}  
     \[** **else\_clause** **\]  
END **

其中***simple\_case\_expression*** :: =     是指

![simple_case_expression.gif][]

expr WHEN comparison\_expr THEN return\_expr  
     \[ WHEN comparison\_expr THEN return\_expr \]...

***searching\_case\_expression*** :: =

![searched_case_expression.gif][]

WHEN condition THEN return_expr
    [ WHEN condition THEN return_expr ]...

***else\_clause* :: =**

![else\_clause.gif的描述如下][else_clause.gif]

**ELSE** else\_expr

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. You cannot specify the literal `NULL` for every `return_expr `and the `else_expr`.

**在一个简单的CASE表达式中，oracle数据库首先搜索第一组 `WHEN` ... `THEN` ,如果`expr` 等于`comparison_expr` 则返回`return_expr，`**

**`如果没有匹配到并且存在ELSE 表达式，则返回else_expr，否则返回null。`**

**`因此你不能为每个return_expr和else_expr定义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, Oracle returns `else_expr`. Otherwise, Oracle returns null.

Oracle Database uses short-circuit evaluation. That is, 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 datatype (`CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2`, `NUMBER`, `BINARY_FLOAT`, or `BINARY_DOUBLE`) or must all have a numeric datatype. If all expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

For both simple and searched `CASE` expressions, all of the `return_expr`s must either have the same datatype (`CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2`, `NUMBER`, `BINARY_FLOAT`, or `BINARY_DOUBLE`) or must all have a numeric datatype. If all return expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

The maximum number of arguments in a `CASE` expression is 255. 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.

[https_docs.oracle.com_cd_B19306_01_server.102_b14200_expressions004.htm]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm
[case_expression.gif]: /images/20220504/3e8cbb4bb95b46f28f84f962832c7911.png
[simple_case_expression.gif]: /images/20220504/4bd0e538dc324f02aceb4add0b152563.png
[searched_case_expression.gif]: /images/20220504/a0f6cd94e79d49729ef88afe568f3094.png
[else_clause.gif]: /images/20220504/65355df728834591822f210a575a7b37.png