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

96. Examine the structure of the INVOICE table.

name           Null        Type

INV_NO         NOT NULL    NUMBER(3)

INV_DATE                  DATE

INV_AMT                   NUMBER(10,2)

Which two SQL statements  would execute successfully?  (Choose two.)

A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')

FROM invoice;

B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') 

FROM invoice;

C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) 

FROM invoice;

D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')

FROM invoice;

Answer: AC

 

答案解析：

此题考NVL2参数类型是否一致的问题。

 

NVL2参考： http://blog.youkuaiyun.com/rlhua/article/details/11808291

 

搭建环境：

创建表，并插入数据

sys@TEST0910> create table invoice

  2  (inv_no number(3) not null,

  3  inv_date date,

  4  inv_amt number(10,2));

 

Table created.

 

sys@TEST0910> insert into invoice values(1,sysdate-2,100.2);

 

1 row created.

 

sys@TEST0910> select * from invoice;

 

    INV_NO INV_DATE     INV_AMT

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

         1 16-SEP-13      100.2

 

A答案：

sys@TEST0910> SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;

 

    INV_NO NVL2(INV_D

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

         1 Pending

 

inv_date非空，则返回Pending。且'Pending','Incomplete'数据类型一致，故正确。

 

B答案：

sys@TEST0910> SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')  FROM invoice;

SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')  FROM invoice

                                    *

ERROR at line 1:

ORA-01858: a non-numeric character was found where a numeric was expected

 

 

sys@TEST0910> SELECT inv_no,NVL2(inv_amt,to_char(inv_date),'Not Available')  FROM invoice;

 

    INV_NO NVL2(INV_AMT,TO_CH

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

         1 16-SEP-13

 

inv_amt非空，则返回inv_date，但要保证inv_date和'Not Available'数据类型一致，故用to_char显示转换。

 

C答案：

sys@TEST0910> SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)  FROM invoice;

 

    INV_NO NVL2(INV_DATE,SYSDATE-INV_DATE,SYSDATE)

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

         1                              2.00181713

 

sysdate可以隐式转为数字类型。故正确。

 

D答案：

sys@TEST0910> SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')

  2  FROM invoice;

SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')

                                       *

ERROR at line 1:

ORA-01722: invalid number

 

 

sys@TEST0910> SELECT inv_no,NVL2(inv_amt,to_char(inv_amt*.25),'Not Available') FROM invoice;

 

    INV_NO NVL2(INV_AMT,TO_CHAR(INV_AMT*.25),'NOTAV

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

         1 25.05

inv_amt非空，则返回inv_amt*.25，但要保证inv_amt*.25和'Not Available'数据类型一致，故用to_char显示转换。