Oracle 浮点数与数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/332492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Floats vs Number
提问by BIBD
I'm seeing conflicting references in Oracles documentation. Is there any difference between how decimals are stored in a FLOAT and a NUMBER types in the database?
我在Oracles 文档中看到了相互冲突的引用。小数在数据库中的 FLOAT 和 NUMBER 类型中的存储方式有什么区别吗？
As I recall from C, et al, a float has accuracy limitations that an int doesn't have. R.g., For 'float's, 0.1(Base 10) is approximated as 0.110011001100110011001101(Base 2) which equals roughtly something like 0.100000001490116119384765625 (Base 10). However, for 'int's, 5(Base 10) is exactly 101(Base 2).
我从 C 等人那里回忆起，浮点数具有 int 没有的精度限制。Rg，对于“浮点数”，0.1（基数 10）近似为 0.110011001100110011001101（基数 2），大致等于 0.100000001490116119384765625（基数 10）。但是，对于 'int's，5(Base 10) 正好是 101(Base 2)。
Which is why the following won't terminate as expected in C:
这就是为什么以下不会在 C 中按预期终止的原因：
float i;
i = 0;
for (i=0; i != 10; )
{
i += 0.1
}
However I see elsewhere in Oracle's documentationthat FLOAT has been defined as a NUMBER. And as I understand it, Oracle's implementation of the NUMBER type does not run into the same problem as C's float.
但是我在 Oracle 文档的其他地方看到FLOAT 被定义为 NUMBER。据我了解，Oracle 对 NUMBER 类型的实现不会遇到与 C 的浮点数相同的问题。
So, what's the real story here? Has Oracle deviated from the norm of what I expect to happen with floats/FLOATs?
那么，这里的真实故事是什么？Oracle 是否偏离了我对浮点数/浮点数所期望的规范？
(I'm sure it's a bee-fart-in-a-hurricane of difference for what I'll be using them for, but I know I'm going to have questions if 0.1*10 comes out to 1.00000000000000001)
（我敢肯定，对于我将使用它们的目的而言，这是一场大不同的飓风，但我知道如果 0.1*10 变为 1.00000000000000001，我会有疑问）
回答by Bill Karwin
Oracle's BINARY_FLOATstores the data internally using IEEE 754 floating-point representation, like C and many other languages do. When you fetch them from the database, and typically store them in an IEEE 754 data type in the host language, it's able to copy the value without transforming it.
OracleBINARY_FLOAT使用 IEEE 754 浮点表示在内部存储数据，就像 C 和许多其他语言一样。当您从数据库中获取它们并通常将它们存储在宿主语言中的 IEEE 754 数据类型中时，它能够复制该值而不对其进行转换。
Whereas Oracle's FLOATdata type is a synonym for the ANSI SQL NUMERIC data type, called NUMBER in Oracle. This is an exact numeric, a scaled decimal data type that doesn't have the rounding behavior of IEEE 754. But if you fetch these values from the database and put them into a C or Java float, you can lose precision during this step.
而 Oracle 的FLOAT数据类型是 ANSI SQL NUMERIC 数据类型的同义词，在 Oracle 中称为 NUMBER。这是一个精确的数字，一种不具有 IEEE 754 舍入行为的缩放十进制数据类型。但是如果您从数据库中获取这些值并将它们放入 C 或 Java 浮点数中，则在此步骤中可能会丢失精度。
回答by Dror Harari
The Oracle BINARY_FLOAT and BINARY_DOUBLE are mostly equivalent to the IEEE 754 standard but they are definitely not stored internally in the standard IEEE 754 representation.
Oracle BINARY_FLOAT 和 BINARY_DOUBLE 大部分等同于 IEEE 754 标准，但它们绝对没有存储在标准 IEEE 754 表示中。
For example, a BINARY_DOUBLE takes 9 bytes of storage vs. IEEE's 8. Also the double floating number -3.0 is represented as 3F-F7-FF-FF-FF-FF-FF-FF which if you use real IEEE would be C0-08-00-00-00-00-00-00. Notice that bit 63 is 0 in the Oracle representation while it is 1 in the IEEE one (if 's' is the sign bit, according to IEEE, the sign of the number is (-1)^s). See the very good IEEE 754 calculators at http://babbage.cs.qc.cuny.edu/IEEE-754/
例如，BINARY_DOUBLE 占用 9 个字节的存储空间，而 IEEE 的 8 个字节。此外，双浮点数 -3.0 表示为 3F-F7-FF-FF-FF-FF-FF-FF，如果您使用真正的 IEEE，它将是 C0- 08-00-00-00-00-00-00。请注意，第 63 位在 Oracle 表示中为 0，而在 IEEE 表示中为 1（如果“s”是符号位，根据 IEEE，数字的符号为 (-1)^s）。在http://babbage.cs.qc.cuny.edu/IEEE-754/查看非常好的 IEEE 754 计算器
You can easily find this if you have a BINARY__DOUBLE column BD in table T with the query:
如果您在表 T 中有一个 BINARY__DOUBLE 列 BD 和查询，您可以很容易地找到它：
select BD,DUMP(BD) from T
从 T 中选择 BD,DUMP(BD)
Now all of that is fine and interesting (maybe) but when one works in C and gets a numeric value from Oracle (by binding a variable to a numeric column of any kind), one typically gets the result in a real IEEE double as is supported by C. Now this value is subject to all of the usual IEEE inaccuracies.
现在所有这些都很好而且很有趣（也许）但是当一个人在 C 中工作并从 Oracle 获取一个数值时（通过将变量绑定到任何类型的数字列），通常会得到一个真正的 IEEE double 结果由 C 支持。现在这个值受制于所有常见的 IEEE 错误。
If one wants to do precise arithmetic one can either do it in PL/SQL or using special precise-arithmetic C libraries.
如果想要进行精确算术，可以使用 PL/SQL 或使用特殊的精确算术 C 库来完成。
For Oracle's own explanation of their numeric data types see: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209
有关 Oracle 自己对其数字数据类型的解释，请参见：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209
回答by Charles Bretana
Oracle's Number is in fact a Decimal (base-10) floating point representation... Float is just an alias for Number and does the exact same thing.
Oracle 的 Number 实际上是一个 Decimal (base-10) 浮点表示法... Float 只是 Number 的一个别名，并且执行完全相同的操作。
if you want Binary (base-2) floats, you need to use Oracle's BINARY_FLOAT or BINARY_DOUBLE datatypes.
如果您想要二进制 (base-2) 浮点数，则需要使用 Oracle 的 BINARY_FLOAT 或 BINARY_DOUBLE 数据类型。
回答by skong
Bill's answer about Oracle's FLOAT is only correct to late version(say 11i), in Oracle 8i, the document says:
比尔关于 Oracle 的 FLOAT 的回答仅适用于最新版本（比如 11i），在 Oracle 8i 中，文档说：
You can specify floating-point numbers with the form discussed in "NUMBER Datatype". Oracle also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:
FLOAT specifies a floating-point number with decimal precision 38, or binary precision 126. FLOAT(b) specifies a floating-point number with binary precision b. The precision b can range from 1 to 126. To convert from binary to decimal precision, multiply b by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.
您可以使用“NUMBER 数据类型”中讨论的形式指定浮点数。Oracle 还支持 ANSI 数据类型 FLOAT。您可以使用以下语法形式之一指定此数据类型：
FLOAT 指定十进制精度为 38 的浮点数，或二进制精度为 126。FLOAT(b) 指定二进制精度为 b 的浮点数。精度 b 的范围可以从 1 到 126。要将二进制精度转换为十进制精度，请将 b 乘以 0.30103。要将十进制精度转换为二进制精度，请将十进制精度乘以 3.32193。二进制精度的最大值为 126 位，大致相当于十进制精度的 38 位。
It sounds like a Quadruple precision(126 binary precision). If I am not mistaken, IEEE754 only requires b = 2, p = 24 for single precision and p = 53 for double precision. The differences between 8i an 11i caused a lot of confusion when I was looking into a conversion plan between Oracle and PostgreSQL.
这听起来像是四倍精度（126 二进制精度）。如果我没记错的话，IEEE754 只要求 b = 2，单精度 p = 24，双精度 p = 53。当我在研究 Oracle 和 PostgreSQL 之间的转换计划时，8i 和 11i 之间的差异引起了很多混乱。
回答by kelli smitt
Like the PLS_INTEGER mentioned previously, the BINARY_FLOAT and BINARY_DOUBLE types in Oracle 10g use machine arithmetic and require less storage space, both of which make them more efficient than the NUMBER type
和前面提到的PLS_INTEGER一样，Oracle 10g中的BINARY_FLOAT和BINARY_DOUBLE类型使用机器算术，需要更少的存储空间，这两者都比NUMBER类型更高效
- ONLY BINARY_FLOAT and BINARY_DOUBLE supports NAN values
- 只有 BINARY_FLOAT 和 BINARY_DOUBLE 支持 NAN 值
-not precise calculations
-不精确的计算
