OCP 1Z0 051 95

来源：互联网发布：python 日志回滚编辑：程序博客网时间：2024/05/18 21:09

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,'')
FROM products;
SQL>SELECT prod_id, NVL(prod_expiry_date, prod_expiry_date + 15)
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.

nvl2用法，参数1为空时返回参数3，否则返回参数2

SQL> select nvl2(null,1,2) from dual;NVL2(NULL,1,2)--------------             21 row selectedSQL> select nvl2('not null',1,2) from dual;NVL2('NOTNULL',1,2)-------------------                  11 row selected

nvl() 返回两个参数中不为空的第一个，要求类型一致，或可以隐式转换

nvl2 要求后两个参数类型一致，或可以隐式转换

SQL> select nvl2('not null',sysdate,2) from dual;select nvl2('not null',sysdate,2) from dualORA-00932: 数据类型不一致: 应为 DATE, 但却获得 NUMBERSQL> select nvl2('not null',sysdate,sysdate + 1) from dual;NVL2('NOTNULL',SYSDATE,SYSDATE------------------------------2014-5-23 11:52:371 row selected

一、'' 在当前oracle相当于null

二、日期加减数值后仍然是日期
所以两个语句都正确

SQL> SELECT prod_id, nvl2(prod_expiry_date, prod_expiry_date + 15, '')  2    FROM (SELECT p.prod_id, p.prod_eff_to AS prod_expiry_date  3            FROM sh.products p  4           WHERE rownum <= 5);PROD_ID NVL2(PROD_EXPIRY_DATE,PROD_EXP------- ------------------------------     13      14      15      16      17 5 rows selected

SQL> SELECT prod_id, nvl(prod_expiry_date, prod_expiry_date + 15)  2    FROM (SELECT p.prod_id, p.prod_eff_to AS prod_expiry_date  3            FROM sh.products p  4           WHERE rownum <= 5);PROD_ID NVL(PROD_EXPIRY_DATE,PROD_EXPI------- ------------------------------     13      14      15      16      17 5 rows selected

Answer: A

0 0