（98）Which statement is true regarding the COALESCE function?

A. It can have a maximum of five expressions in a list.

B. It returns the highest NOT NULL value in the list for all rows.

C. It requires that all expressions in the list must be of the same data type.

D. It requires that at least one of the expressions in the list must have a NOT NULL value.

答案：（C）

解析：

题目本身有问题，不要求数据类型相同，只要兼容即可

coalesce函数返回一个不为null的表达式. 如果所有的表达式都为null，则函数返回null。

--在SQL*Plus执行出错

scott@TESTDB11>select coalesce(null, '45', 34) from dual;

select coalesce(null, '45', 34) from dual

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

scott@TESTDB11>select coalesce(null, 34, '45') from dual;

select coalesce(null, 34, '45') from dual

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

但在PL/SQL代码中，实际上只要数据类型兼容或能够进行隐式类型转换即可

declare

v_1 NUMBER;

v_2 VARCHAR2(10);

v_3 NUMBER;

v_res NUMBER;

begin

v_1 := 5;

v_2 := '30';

v_3 := NULL;

-- 6个参数没有问题

v_res := coalesce(v_3, v_1, v_2, v_1, v_2, v_3);

end;

ITPub博客

OCP 1Z0-051-98