OCP 1Z0 051 96

来源：互联网发布：农村淘宝进驻地区查询编辑：程序博客网时间：2024/05/16 16:15

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;

C 中参数2与参数3类型不一致。但这儿做了隐式转换
见 http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions120.htm#SQLRF00685
NVL2(expr1, expr2, expr3)
If the data types of expr2 and expr3 are different, then Oracle Database implicitly converts one to the other.
If expr2 is character data, then Oracle Database converts expr3 to the data type of expr2 before returning a value unless expr3 is a null constant.

可转换的类型见

见 http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements002.htm#SQLRF51048 中 Table 3-10 Implicit Type Conversion Matrix

其它函数如DEOCDE,NVL,COALESCE等，隐式转换都是同样的规则

SQL> alter table invoice add inv_no2 number;Table alteredSQL> update invoice set inv_no2 = 102 where inv_no = 102;1 row updatedSQL> SELECT inv_no2,  2         SYSDATE - inv_date AS n,  3         SYSDATE,  4         nvl2(inv_no2, SYSDATE - inv_date, SYSDATE) AS "nvl2"  5    FROM invoice;   INV_NO2          N SYSDATE           nvl2---------- ---------- ----------- ----------           2289.55943 2014-5-23 1 2456801.55       102 2260.55943 2014-5-23 1 2260.559432 rows selected

我个人认为这种考题有点无聊如A选项，改下位置就能转换了

SQL> SELECT inv_no2, nvl2(inv_no2, 'Not Available', inv_date) FROM invoice;   INV_NO2 NVL2(INV_NO2,'NOTAVAILABLE',IN---------- ------------------------------           15-feb-08       102 Not Available2 rows selected

平时写语句还是直接写成显示转换好。隐式转换难以记忆且不好控制

Answer: AC

0 0