OCP-1Z0-051 第93题 NVL函数

最新推荐文章于 2023-08-22 15:06:59 发布

hello_holly

最新推荐文章于 2023-08-22 15:06:59 发布

阅读量5k

点赞数

分类专栏： OCP-1Z0-051 文章标签： OCP-1Z0-051 NVL 函数 11g

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

版权

OCP-1Z0-051 专栏收录该内容

220 篇文章

订阅专栏

本文通过一道OCP-1Z0-051考试中的题目，探讨了如何在SQL中使用NVL函数处理数值类型与字符串类型的转换，特别是在涉及到空值(null)时的正确做法。在生成报表时，需要为所有客户增加15%的信用限制，对于没有信用限制的客户，显示'Not Available'。通过分析错误示例，得出正确的SQL语句应该先将数值乘以15%，再使用TO_CHAR转换为字符串，以确保NVL函数的参数类型匹配。

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

一、原题

View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table,you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT"
        FROM customers;

B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT"
        FROM customers;

C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT"
        FROM customers;

D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT"
        FROM customers;

答案：D

二、题目翻译

下面是CUSTOMERS表的结构：
根据CUSTOMERS表生成一个报表，显示所有客户增长15%的credit limit.如果客户没有credit limit则显示Not Available.
哪个SQL语句给出所需结果？

三、题目解析

A、B、C选项不正确，原因类似，NVL函数第一个参数是number类型，第二个参数是字符类型，无法隐式转换。
D选项正确，先将cust_credit_limit * 0.15，这是数值类型，用to_char转成字符类型，nvl的两个参数类型一致，就正确了。

四、测试

这里，使用oracle已经有的测试表emp，其中comm列有null值,测试如下：

SQL> SELECT NVL(comm,'Not Available')*.15 "NEW CREDIT"
2 FROM emp;
SELECT NVL(comm,'Not Available')*.15 "NEW CREDIT"
                *
ERROR at line 1:
ORA-01722: invalid number

SQL> SELECT NVL(comm*.15,'Not Available') "NEW CREDIT"
2 FROM emp;
SELECT NVL(comm*.15,'Not Available') "NEW CREDIT"
                    *
ERROR at line 1:
ORA-01722: invalid number

SQL> SELECT TO_CHAR(NVL(comm*.15,'Not Available')) "NEW CREDIT"
2 FROM emp;
SELECT TO_CHAR(NVL(comm*.15,'Not Available')) "NEW CREDIT"
                            *
ERROR at line 1:
ORA-01722: invalid number

SQL> SELECT NVL(TO_CHAR(comm*.15),'Not Available') "NEW CREDIT"
2 FROM emp;

NEW CREDIT
----------------------------------------------------------------
Not Available
45
75
Not Available
210
Not Available
Not Available
Not Available
Not Available
0
Not Available
Not Available
Not Available
Not Available

14 rows selected.

NVL函数的详细用法，参考：
http://blog.csdn.net/holly2008/article/details/25251513