Oracle何时/为什么将NaN添加到数据库表中的行 [英] When/Why does Oracle adds NaN to a row in a database table

问题描述

我知道NaN代表Not a Number.但是，我很难理解Oracle何时以及为何将其添加到行中.

当它遇到一个小于0的值(如负数)或一个垃圾值时，它就是它.

解决方案

来自文档:

Oracle数据库数字数据类型存储正负定点和浮点数，零，无穷大和值，这些值是操作未定义的结果-非数字"或NAN.

据我所知，您只能在 binary_float中获得NaN或binary_double 列；这些数据类型具有它们自己的NaN字面量，并且也是 is nan条件，并且ORA-01476: divisor is equal to zero.

您不会获得零或负数的NaN.也可能有一个字符串列，并且应用程序在其中输入了"NaN"一词，但是将数字作为字符串存储在许多级别上都不是一个好主意，因此希望不是这种情况.

I know that NaN stands for Not a Number. But, I have trouble understanding when and why Oracle adds this to a row.

Is it when it encounters a value less than 0 like a negative number or when its a garbage value.

解决方案

From the documentaton:

The Oracle Database numeric data types store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation—"not a number" or NAN.

As far as I'm aware you can only get NaN in a binary_float or binary_double column; those data types have their own literals for NaN too, and there's an is nan condition for them too, and the nanvl() function to manipulate them.

An example of a way to get such a value is to divide a zero float/double value by zero:

select 0f/0 from dual;

0F/0
----
NaN  

... so if you're seeing NaNs your application logic or underlying data might be broken. (Note you can't get this with a 'normal' number type; you get ORA-01476: divisor is equal to zero unless the numerator is float or double).

You won't get NaN for zero or negative numbers though. It's also possible you have a string column and an application is putting the word 'NaN' in, but storing numbers as strings is a bad idea on many levels, so hopefully that is not the case.

这篇关于Oracle何时/为什么将NaN添加到数据库表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！