Oracle_COALESCE函数

file:///P:/Oracle官方文档/11.2官方文档/server.112/e26088/functions030.htm#SQLRF00617

Oracle® Database SQL Language Reference 11g Release 2 (11.2) E26088-03

Home Book List Contents Index Master Index Contact Us

COALESCE

Syntax

Description of the illustration coalesce.gif

Purpose

COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences ofexpr evaluate to null, then the function returns null.

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

If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

See Also:

Table 3-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence

This function is a generalization of the NVL function.

You can also use COALESCE as a variety of the CASE expression. For example,

COALESCE(expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Similarly,

COALESCE(expr1, expr2, ..., exprn)

where n >= 3, is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END

See Also:

NVL and "CASE Expressions"

Examples

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":

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

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

创建测试表

SQL> create table t1(
  2  a varchar2(10),
  3  b varchar2(10),
  4  c varchar2(10),
  5  d varchar2(10)
  6  );

插入测试数据

insert into t1(a,b,c,d) values('a1','b1','c1','d1');
insert into t1(b,c,d) values('b2','c2','d2');
insert into t1(c,d) values('c3','d3');
insert into t1(d) values('d4');
insert into t1(a,b,c,d) values(null,null,null,null);
insert into t1(a,b,c,d) values('a6','b6','c6','d6');

执行结果