The NUMBER data type can store zero, floating-point numbers, positive fixed-point numbers, and negative fixed-point numbers. This topic describes the syntax, parameters, and usage of the NUMBER data type.
Syntax
NUMBER [(p[, s])]
Parameters
Notice
- When you use the
NUMBERdata type with thepandsparameters as the partitioning key and connect to OceanBase Database by using ODP, to ensure accurate routing, make sure that the version of ODP is V4.2.3 or later. - The
NUMBERdata type does not support modifying theporsparameter. - The number of digits to the right of the decimal point, including the least significant digit, is the number of significant digits. Precision and scale are both represented in decimal digits.
- The number of digits to the left of the decimal point, excluding the least significant digit, is the scale. For negative scale values, the least significant digit is to the left of the decimal point, because the actual data is rounded to the specified number of digits to the left of the decimal point.
| Parameter | Range | Description | ||||
|---|---|---|---|---|---|---|
| p | [1,38] | The number of significant digits, the maximum number of decimal digits, where the most significant digit is the leftmost nonzero digit, and the least significant digit is the rightmost known digit. | ||||
| s | [-84,127] | The number of digits to the right of the decimal point, from the decimal point to the least significant digit. The range of the scale is [-84,127].
Note
|
s | digits to the left of the decimal point. Then, check whether the number of significant digits is less than or equal to p + |
s | . |
Considerations
The NUMBER data type is a variable-length, precise numeric type that occupies 4 to 40 bytes of storage space. Of this, 4 bytes are used for metadata and 36 bytes are used for the actual numeric value. The absolute value can range from 1.0 × 10-130 to 1.0 × 10126 (excluding 1.0 × 10126). If the absolute value of the arithmetic expression you specify is greater than or equal to 1.0 × 10126, OceanBase Database returns an error.
The NUMBER data type offers high precision and strong portability, with excellent compatibility, but it has relatively low computational efficiency compared to floating-point types. In the NUMBER [(p[s])] data type, p generally indicates the precision, and s generally indicates the scale. Integers are specified in the following format in the NUMBER data type:
NUMBER(p)represents a fixed-point number with precisionpand scale 0, which is equivalent toNUMBER(p,0).NUMBERspecifies a floating-point number without precision or decimal places indicators.
The NUMBER data type stores data with varying precision and scale. To prevent data stored in OceanBase Database from exceeding the specified precision, you must define both the scale and precision for fixed-point numeric columns, so that additional integrity checks can be performed on the input. However, this does not enforce a fixed length for fixed-point numeric columns. If the actual data stored exceeds the specified precision, OceanBase Database returns an error. If the data exceeds the specified scale, OceanBase Database rounds the value. See the examples in the table below.
| Actual data | Specified as | Stored as |
|---|---|---|
| 135.79 | NUMBER |
135.79 |
| 135.79 | NUMBER(3) |
136 |
| 135.79 | NUMBER(3,2) |
Exceeds precision |
| 135.79 | NUMBER(4,2) |
Exceeds precision |
| 135.79 | NUMBER(5,2) |
135.79 |
| 135.79 | NUMBER(6,1) |
135.8 |
| 135.79 | NUMBER(6,-2) |
100 |
| .01234 | NUMBER(4,5) |
.01234 |
| .00012 | NUMBER(4,5) |
.00012 |
| .000127 | NUMBER(4,5) |
.00013 |
| .0000012 | NUMBER(2,7) |
.0000012 |
| .00000123 | NUMBER(2,7) |
.0000012 |
| 1.2e-4 | NUMBER(2,5) |
.00012 |
| 1.2e-5 | NUMBER(2,5) |
.00001 |
