Oracle coalesce 函数学习

语法

COALESCE(expr [, expr ]...)
复制

用途

1. 返回参数中第一个非空值；如果所有参数都为null，则函数返回null；
2. oracle采用最小化计算原则，逐一计算每个表达式的值，如果非空则返回该表达式的值，如果为null，则继续计算下一个参数表达式的值；并不会提前计算所有表达式的值；

Oracle Database uses short-circuit evaluation. That is, the database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

COALESCE (expr1, expr2) 相当于以下case表达式：
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
复制

COALESCE (expr1, expr2, ..., exprn), for n>=3
相当于：
CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END
复制

例子

The following example uses the sample oe.product_information table to organize a clearance sale of products. It gives a 10% discount to all products with a list price. If there is no list price, then the sale price is the minimum price. If there is no minimum price, then the sale price is “5”:

COALESCE(0.9*list_price, min_price, 5)
复制

• 如果list_price不为空，函数返回0.9*list_price的值，
• 如果list_price为空，则继续判断min_price的值，不为空则返回min_price的值，
• 如果list_price和min_price都为空则返回5；

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050;

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      2382        850        731        765
      3355                                5
      1770                    73         73
      2378        305        247      274.5
      1769         48                  43.2
复制

参考文档

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm