OCP-1Z0-051 第106题 COALESCE,DECODE,NVL,NULLIF函数的使用

一、原题

Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:

LIST_PRICE    MIN_PRICE

10000          8000

20000

30000          30000

Which two expressions give the same output? (Choose two.)

A. NVL(NULLIF(list_price, min_price), 0)

B. NVL(COALESCE(list_price, min_price), 0)

C. NVL2(COALESCE(list_price, min_price), min_price, 0)

D. COALESCE(NVL2(list_price, list_price, min_price), 0)

答案：BD

二、题目翻译

下面是PRODUCTS表的LIST_PRICE和 MIN_PRICE两列的数据：

哪两个表达式结果相同？

三、题目解析

NVL和NULLIF的用法，详见：

         http://blog.csdn.net/holly2008/article/details/25251513

DECODE的用法，详见：

          http://blog.csdn.net/holly2008/article/details/23140591

四、测试

      下面依次是ABCD四个选项的测试结果：

SQL> with PRODUCTS as

  2  (

  3    select 10000 LIST_PRICE,8000 MIN_PRICE from dual

  4    union all

  5    select 20000,null from dual

  6    union all

  7    select 30000,30000 from dual

  8  )

  9  select NVL(NULLIF(list_price, min_price), 0) from products;

NVL(NULLIF(LIST_PRICE,MIN_PRICE),0)

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

                              10000

                              20000

SQL> with PRODUCTS as

  2  (

  3    select 10000 LIST_PRICE,8000 MIN_PRICE from dual

  4    union all

  5    select 20000,null from dual

  6    union all

  7    select 30000,30000 from dual

  8  )

  9  select NVL(COALESCE(list_price, min_price), 0) from products;

NVL(COALESCE(LIST_PRICE,MIN_PRICE),0)

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

                                10000

                                20000

                                30000

SQL> with PRODUCTS as

  2  (

  3    select 10000 LIST_PRICE,8000 MIN_PRICE from dual

  4    union all

  5    select 20000,null from dual

  6    union all

  7    select 30000,30000 from dual

  8  )

  9  select NVL2(COALESCE(list_price, min_price), min_price, 0) from products;

NVL2(COALESCE(LIST_PRICE,MIN_PRICE),MIN_PRICE,0)

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

                                            8000

                                           30000

SQL> with PRODUCTS as

  2  (

  3    select 10000 LIST_PRICE,8000 MIN_PRICE from dual

  4    union all

  5    select 20000,null from dual

  6    union all

  7    select 30000,30000 from dual

  8  )

  9  select COALESCE(NVL2(list_price, list_price, min_price), 0) from products;

COALESCE(NVL2(LIST_PRICE,LIST_PRICE,MIN_PRICE),0)

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

                                            10000

                                            20000

                                            30000