Numeric data types use numeric literals to specify fixed and floating-point numbers. Some numeric data types also support zero, infinity, and not-a-number (NaN) values. This topic describes the NUMBER type and floating-point numbers.
NUMBER
The NUMBER type stores fixed-point and floating-point numbers. This type can store numbers of virtually any order of magnitude.
Precision
The precision specifies the total number of digits of a number. If the precision is not specified, the original number provided is stored without rounding.
Scale
The scale specifies the number of digits from the decimal point to the least significant digit. A positive scale counts digits to the right of the decimal point up to and including the least significant digit. A negative scale counts digits to the left of the decimal point. If you specify a precision without a scale, for example,
NUMBER(6), the scale is 0.
A numeric literal can store a number with a maximum precision of 38 digits. If the precision of a literal is higher than that supported by the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type, OceanBase Database truncates the literal. If the range of a literal exceeds the boundaries supported by the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type, OceanBase Database returns an error.
For example, NUMBER(8,2) indicates a precision of 8 and a scale of 2. Therefore, the database stores 100,000 as 100000.00.
Sample valid values of the NUMBER type are as follows:
12
+6.87
0.5
25e-03
-9
Sample valid values of the floating-point type are as follows:
25f
+6.34F
0.5d
-1D
Floating-point numbers
OceanBase Database provides two numeric data types exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE.
These types support all basic features provided by the NUMBER type. However, the NUMBER type uses decimal precision, whereas the BINARY_FLOAT and BINARY_DOUBLE types use binary precision. Binary precision enables faster arithmetic calculations and usually reduces storage requirements.
BINARY_FLOAT and BINARY_DOUBLE are approximate numeric data types. They store approximate representations, rather than exact representations, of decimal values. For example, the value 0.1 cannot be exactly represented by the BINARY_DOUBLE or BINARY_FLOAT type. These types are often used in scientific calculations. They function like the FLOAT and DOUBLE types in Java.
Sample valid floating-point numbers are as follows:
1.2
-1.1
0.
In addition to the NUMBER and floating-point types, numeric data types also support special representations such as positive infinity, negative infinity, and NaN representations. The following special representations are supported:
binary_double_infinity
binary_double_nan
binary_float_infinity
binary_float_nan