|
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.iyunv.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.05inv_amt非空,则返回inv_amt*.25,但要保证inv_amt*.25和'Not Available'数据类型一致,故用to_char显示转换。 版权声明:本文为博主原创文章,未经博主允许不得转载。 |
|