oracle COALESCE 函数

Term: COALESCE

Definition:
The Oracle COALESCE function returns the first non-NULL expression in the list. If all expressions in the list evaluate toNULL, then the COALESCE function will returnNULL. The Oracle COALESCE function makes use of "short-circuit evaluation". The database evaluates each expression's value and determines whether it isNULL, rather than evaluating all of the expressions before determining if any of them areNULL.

Example Syntax:

COALESCE( expression_1, expression_2, ... expression_n )

Using COALESCE Instead of CASE, courtesy of Bob Watkins
The COALESCE function can be used instead of the longer CASE statement when testing for NULL results in multiple expressions. The key to using the COALESCE function as a more concise form of aCASE statement is that most expressions involving NULL data will return NULL as a result. (Note that the concatenation operator, '||' is an exception.) For example, NULL plus or minus anything is NULL; NULL multiplied by anything is NULL, and so on.

For example, assume a 'Parts' table that is designed to hold both purchased and built parts. The part_type column will contain 'P' for purchased, and 'B' for parts built or assembled in-house. In addition, for purchased parts, there is a purchase_cost column that tells how much we pay for the part; this is NULL for built parts. On the other hand, built parts have material_qty and material_cost columns; these areNULL in the case of purchased parts.

You could use a CASE statement that tests the value of the part_type column and return either purchase_cost or material_qty times the material_cost, but COALESCE can do this for you in one step:

COALESCE(purchase_cost, material_qty * material_cost)

If a row describes a purchased part, purchase_cost is not NULL, and purchase_cost will be returned. However, if the part is built in-house, purchase_cost will beNULL and COALESCE will skip over it. The material_qty is then multiplied by the material_cost; since neither is NULL, the result is non-null and is returned.

SELECT part_id "Part", part_type "Type",   COALESCE(purchase_cost, material_qty * material_cost) "Cost"   FROM parts;

You can repeat this pattern for as many expressions as needed, using COALESCE as a shortcut to evaluate multiple entity types in the same table.

---

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

使用COALESCE的秘密在于大部分包含空值的表达式最终将返回空值(连接操作符"||"是一个值得注意的例外).

例如,空值加任何值都是空值,空值乘任何值也都是空值,依此类推.

 

SQL> select null from dual;

N
-

SQL> select null||'a' from dual;

NUL
---
a

SQL> select null+'a' from dual;

  NULL+'A'

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  返回其参数中第一个非空表达式。  
   
  语法  
  COALESCE   (   expression   [   ,...n   ]   )    
   
  参数  
  expression  
   
  任何类型的表达式。  
   
  n  
   
  表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。  
   
  返回类型  
  将相同的值作为   expression   返回。  
   
  注释  
  如果所有自变量均为   NULL，则   COALESCE   返回   NULL   值。  
   
  COALESCE(expression1,...n)   与此   CASE   函数等价：  
   
  CASE  
        WHEN   (expression1   IS   NOT   NULL)   THEN   expression1  
        ...  
        WHEN   (expressionN   IS   NOT   NULL)   THEN   expressionN  
        ELSE   NULL