OCP-1Z0-051-V9.02-95题

转载

mob604756f692f5 2013-09-18 14:57:00

文章标签 数据类型 sql oracle 隐式转换显示转换 文章分类 代码人生

95. The PRODUCTS table has the following structure:

name Null Type

PROD_ID NOT NULL NUMBER(4)

PROD_NAME VARCHAR2(25)

PROD_EXPIRY_DATE DATE

Evaluate the following two SQL statements:

SQL>SELECT prod_id, NVL2(prod_expiry_date, prod_expiry_date + 15,'') 如果prod_expiry_date为null，则返回空，否则返回prod_expiry_date + 15

FROM products;

SQL>SELECT prod_id, NVL(prod_expiry_date, prod_expiry_date + 15) 如果prod_expiry_date为null，则返回prod_expiry_date + 15，否则返回prod_expiry_date

FROM products;

Which statement is true regarding the outcome?

A. Both the statements execute and give different results.

B. Both the statements execute and give the same result.

C. Only the first SQL statement executes successfully.

D. Only the second SQL statement executes successfully.

Answer: A

答案解析：

此题考的本意是NVL和NVL2之间逻辑计算的区别。

1、NVL

参考地址：javascript:void(0)

2、NVL2

OCP-1Z0-051-V9.02-95题_隐式转换

Purpose

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null.

If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

如果expr1非空，则返回expr2，如果expr1是空值，则返回expr3

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.

expr1 可以是任意数据类型， expr2 and expr3 可以是任意数据类型，但不能是LONG类型，且数据类型要一致，或者隐式转换为一致，或者显示转换为一致。

If the data types of expr2 and expr3 are different, then Oracle Database implicitly converts one to the other.

如果expr2 and expr3 数据类型不同，则隐式转为相同

If they cannot be converted implicitly, then the database returns an error.

如果不能隐式转换，则报错。

If expr2 is character or numeric data, then the implicit conversion is implemented as follows:

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.
In that case, a data type conversion is not necessary, and the database returns VARCHAR2 in the character set of expr2.
如果expr2 是字符类型，则将expr3 转换为expr2相同的数据类型。
If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions120.htm#sthref1315

搭建环境：

1、创建表，插入数据。

sys@TEST0910> create table products

2 (prod_id number(4) not null,

3 prod_name varchar2(25),

4 prod_expiry_date date);

Table created.

sys@TEST0910> insert into products values(1,'tomato',sysdate);

1 row created.

sys@TEST0910> select * from products;

PROD_ID PROD_NAME PROD_EXPI