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 statementswould 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 invoice2(inv_no number(3) not null,3inv_date date,4inv_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 expectedsys@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') 2FROM invoice; SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') *ERROR at line 1:ORA-01722: invalid numbersys@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显示转换。 版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]