OCP-1Z0-051-V9.02-97题

97. View the Exhibit and evaluate the structure and data in the CUST_STATUS table.

You issue the following SQL statement:

SQL> SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000)"BONUS"     

FROM cust_status;

Which statement is true regarding the execution of the above query? 

A. It produces an error because the  AMT_SPENT column contains a  null value.

B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.

C. It displays a bonus of 1000 for all customers whose AMT_SPENT   equals CREDIT_LIMIT, or

AMT_SPENT is null .

D. It produces an error because the TO_NUMBER function must be used to convert the result of the

NULLIF function before it can be used by the NVL2 function.

Answer: C

 

答案解析：

实验验证：

scott@TEST0924> create table cust_status

  2  (custno number(2) not null,

  3  amt_spent number(10,2),

  4  credit_limit number(10,2)

  5  )

  6  /

Table created.

scott@TEST0924> insert into cust_status values(1,1000,1000);

1 row created.

scott@TEST0924> insert into cust_status values(2,2000,2500);

1 row created.

scott@TEST0924> insert into cust_status values(3,'',3000);

1 row created.

scott@TEST0924> insert into cust_status values(4,3000,2800);

1 row created.

scott@TEST0924> select * from cust_status;

    CUSTNO  AMT_SPENT CREDIT_LIMIT

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

         1       1000         1000

         2       2000         2500

         3                    3000

         4       3000         2800

 

scott@TEST0924> select custno,nullif(amt_spent,credit_limit) from cust_status;

    CUSTNO NULLIF(AMT_SPENT,CREDIT_LIMIT)

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

         1

         2                           2000

         3

         4                           3000

从以上sql语句得知，在amt_spent,credit_limit相等，或者AMT_SPENT为空时，返回空值。

运行题中SQL语句

scott@TEST0924> select custno,nvl2(nullif(amt_spent,credit_limit),0,1000) "BONUS" from cust_status;

    CUSTNO      BONUS

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

         1       1000

         2          0

         3       1000

         4          0

从以上sql得知，当nullif函数返回空值时，"BONUS" 为1000，也即是说在amt_spent,credit_limit相等，或者AMT_SPENT为空时，"BONUS" 为1000。选项C正好符合此意思。

 

 

 

1、NVL2

参考地址：http://blog.csdn.net/rlhua/article/details/11808291

2、NULLIF

 

 

 

Purpose

NULLIF compares expr1 and expr2. If they are equal, then the function returns null.比较expr1 andexpr2，如果相等，则返回null值。

 If they are not equal, then the function returns expr1. You cannot specify the literalNULL for expr1.如果不等，则返回expr1 。不能指定expr1为空。

If both arguments are numeric data types, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

If the arguments are not numeric, then they must be of the same data type, or Oracle returns an error.

如果参数类型不是数字类型，则必须是相同数据类型，否则报错。

The NULLIF function is logically equivalent to the following CASE expression:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

 

官网参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions116.htm#sthref1303