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.csdn.net/rlhua/article/details/11808291   搭建環境： 建立表，并插入資料 [email protected]> create table invoice   2  (inv_no number(3) not null,   3  inv_date date,   4  inv_amt number(10,2));   Table created.   [email protected]> insert into invoice values(1,sysdate-2,100.2);   1 row created.   [email protected]> select * from invoice;       INV_NO INV_DATE     INV_AMT ---------- --------- ----------          1 16-SEP-13      100.2   A答案： [email protected]> SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;       INV_NO NVL2(INV_D ---------- ----------          1 Pending   inv_date非空，則傳回Pending。且'Pending','Incomplete'資料類型一緻，故正确。   B答案： [email protected]> 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     [email protected]> 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答案： [email protected]> 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答案： [email protected]> 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     [email protected]> 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顯示轉換。