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

本文链接：https://blog.csdn.net/holly2008/article/details/25335053

博客详细解析了OCP-1Z0-051考试中涉及的Oracle数据库函数COALESCE, DECODE, NVL, NULLIF的用法，并通过实例展示了这些函数在处理LIST_PRICE和MIN_PRICE列数据时的输出结果，帮助理解它们的功能和区别。" 130386397,9640242,Python面向对象编程实战：素数与几何形状,"['Python', '开发语言', '面向对象编程', '类方法', '实例方法']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、原题

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
                                  0

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