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参数类型是否一致的问题。
搭建环境：
创建表，并插入数据
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显示转换。