Binary_double datatype 实际存储和显示不一致因为有隐式转换

最新推荐文章于 2025-07-04 12:45:49 发布

原创最新推荐文章于 2025-07-04 12:45:49 发布 · 900 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

91 篇文章

订阅专栏

Oracle中的Binary_double数据类型在存储和显示时可能存在差异，这是因为数据库在操作中进行了隐式转换。当数字与Binary_double交互时，由于Oracle的自动转换机制，可能会导致精度损失。在比较字符和数值类型时，会将字符数据转换为数值。在进行数学运算和比较时，Oracle会根据数值数据类型的优先级进行类型转换，如果转换失败则操作会失败。

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

why binary_double datatype value shows differently

implicit conversion ，把数字转换成 BINARY_DOUBLE，就会存储为科学记数的样子，与实际数字是不一样的。

this data is inexact as when it be inserted, that because Oracle has implicit conversion. Oracle Database automatically converts a value from one data type to another when such a conversion makes sense.

The following rules govern implicit data type conversions:

During INSERT and UPDATE operations, Oracle converts the value to the data type of the affected column.
During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.
When manipulating numeric values, Oracle usually adjusts precision and scale to allow for maximum capacity. In such cases, the numeric data type resulting from such operations can differ from the numeric data type found in the underlying tables.
When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.
Conversions between character values or NUMBER values and floating-point number values can be inexact, because the character types and NUMBER use decimal precision to represent the numeric value, and the floating-point numbers use binary precision.
When converting a CLOB value into a character data type such as VARCHAR2, or converting BLOB to RAW data, if the data to be converted is larger than the target data type, then the database returns an error.
During conversion from a timestamp value to a DATE value, the fractional seconds portion of the timestamp value is truncated. This behavior differs from earlier releases of Oracle Database, when the fractional seconds portion of the timestamp value was rounded.
Conversions from BINARY_FLOAT to BINARY_DOUBLE are exact.
Conversions from BINARY_DOUBLE to BINARY_FLOAT are inexact if the BINARY_DOUBLE value uses more bits of precision that supported by the BINARY_FLOAT.
When comparing a character value with a DATE value, Oracle converts the character data to DATE.
When you use a SQL function or operator with an argument of a data type other than the one it accepts, Oracle converts the argument to the accepted data type.
When making assignments, Oracle converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.
During concatenation operations, Oracle converts from noncharacter data types to CHAR or NCHAR.
During arithmetic operations on and comparisons between character and noncharacter data types, Oracle converts from any character data type to a numeric, date, or rowid, as appropriate. In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a NUMBER.
Most SQL character functions are enabled to accept CLOBs as parameters, and Oracle performs implicit conversions between CLOB and character types. Therefore, functions that are not yet enabled for CLOBs can accept CLOBs through implicit conversion. In such cases, Oracle converts the CLOBs to CHAR or VARCHAR2 before the function is invoked. If the CLOB is larger than 4000 bytes, then Oracle converts only the first 4000 bytes to CHAR.
When converting RAW or LONG RAW data to or from character data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. Refer to "RAW and LONG RAW Data Types" for more information.
Comparisons between CHAR and VARCHAR2 and between NCHAR and NVARCHAR2 types may entail different character sets. The default direction of conversion in such cases is from the database character set to the national character set. Table 2-11 shows the direction of implicit conversions between different character types.

NUMBER use decimal precision to represent the numeric value, and the floating-point numbers use binary precision.

https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF51043

https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF00214

BINARY_DOUBLE

BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes.

In a NUMBER column, floating point numbers have decimal precision. In a BINARY_FLOAT or BINARY_DOUBLE column, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number).

You can specify floating-point numbers within the limits listed in Table 3-3. The format for specifying floating-point numbers is defined in "Numeric Literals".

Table 3-3 Floating Point Number Limits

Value	BINARY_FLOAT	BINARY_DOUBLE
Maximum positive finite value	3.40282E+38F	1.79769313486231E+308
Minimum positive finite value	1.17549E-38F	2.22507485850720E-308

IEEE754 Conformance The Oracle implementation of floating-point data types conforms substantially with the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985 (IEEE754). The floating-point data types conform to IEEE754 in the following areas:

The SQL function SQRT implements square root. See SQRT.
The SQL function REMAINDER implements remainder. See REMAINDER.
Arithmetic operators conform. See "Arithmetic Operators".
Comparison operators conform, except for comparisons with NaN. Oracle orders NaN greatest with respect to all other values, and evaluates NaN equal to NaN. See "Floating-Point Conditions".
Conversion operators conform. See "Conversion Functions".
The default rounding mode is supported.
The default exception handling mode is supported.
The special values INF, -INF, and NaN are supported. See "Floating-Point Conditions".
Rounding of BINARY_FLOAT and BINARY_DOUBLE values to integer-valued BINARY_FLOAT and BINARY_DOUBLE values is provided by the SQL functions ROUND, TRUNC, CEIL, and FLOOR.
Rounding of BINARY_FLOAT/BINARY_DOUBLE to decimal and decimal to BINARY_FLOAT/BINARY_DOUBLE is provided by the SQL functions TO_CHAR, TO_NUMBER, TO_NCHAR, TO_BINARY_FLOAT, TO_BINARY_DOUBLE, and CAST.

The floating-point data types do not conform to IEEE754 in the following areas:

-0 is coerced to +0.
Comparison with NaN is not supported.
All NaN values are coerced to either BINARY_FLOAT_NAN or BINARY_DOUBLE_NAN.
Non-default rounding modes are not supported.
Non-default exception handling mode are not supported.

Numeric Precedence

Numeric precedence determines, for operations that support numeric data types, the data type Oracle uses if the arguments to the operation have different data types. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values:

If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation.
If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation.
Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation.

If any implicit conversion is needed and fails, then the operation fails. Refer to Table 3-10, "Implicit Type Conversion Matrix" for more information on implicit conversion.

In the context of other data types, numeric data types have lower precedence than the datetime/interval data types and higher precedence than character and all other data types.

--Silent rounding of binary doubles to the 17th significant digit

https://community.oracle.com/thread/4172155