ORACLE nanvl() 函数的应用

最新推荐文章于 2021-04-04 08:30:37 发布

原创最新推荐文章于 2021-04-04 08:30:37 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

DB学习杂记专栏收录该内容

7 篇文章

订阅专栏

本文介绍了Oracle数据库中NANVL函数的功能与用法。该函数主要用于处理BINARY_FLOAT或BINARY_DOUBLE类型的浮点数，当输入值为NaN时返回替代值，否则返回原值。文章通过示例展示了如何使用NANVL函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[size=x-large]The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. It instructs Oracle Database to return an alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2.

This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.[/size]

Examples

Using table float_point_demo created for TO_BINARY_DOUBLE, insert a second entry into the table:

INSERT INTO float_point_demo
VALUES (0,'NaN','NaN');

SELECT *
FROM float_point_demo;

DEC_NUM BIN_DOUBLE BIN_FLOAT
---------- ---------- ----------
1234.56 1.235E+003 1.235E+003
0 Nan Nan
The following example returns bin_float if it is a number. Otherwise, 0 is returned.

SELECT bin_float, NANVL(bin_float,0)
FROM float_point_demo;

BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
1.235E+003 1.235E+003
Nan 0