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
NUMBERtype with thepandsparameters as the partitioning key and connect to OceanBase Database by using ODP, make sure that the ODP version is V4.2.3 or later to ensure accurate routing. - You cannot modify the
pandsparameters for theNUMBERdata type. - The positive scale of a decimal number is the number of significant digits from the right of the decimal point to the least significant digit, including the least significant digit. Both precision and scale are represented by decimal digits.
- The negative scale of a decimal number is the number of significant digits to the left of the decimal point, excluding the least significant digit. For a negative scale, 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 precision, which specifies the maximum number of significant digits. The leftmost digit is the most significant digit, and the rightmost digit is the least significant digit. |
| s | [-84,127] | The scale, which specifies the number of digits from the decimal point to the least significant digit. The scale range is [-84,127].
Note
|
Considerations
NUMBER is a variable-length, precise numeric data type that occupies 4 to 40 bytes of storage space. The 4 bytes store the metadata of the NUMBER data type, and the remaining 36 bytes store the value of the NUMBER data type. The absolute value of the NUMBER data type ranges from 1.0 × 10-130 to 1.0 × 10126 (excluding 1.0 × 10126). If the absolute value of an arithmetic expression you specify is greater than or equal to 1.0 × 10126, OceanBase Database returns an error.
The NUMBER data type has high data precision, strong portability, and good generality. However, its calculation efficiency is relatively low compared with that of the floating-point data type. In NUMBER [(p[s])], p specifies the precision and s specifies the scale. The NUMBER data type stores data in the following formats:
NUMBER(p)specifies a fixed-point number with a precision ofpand a scale of 0, which is equivalent toNUMBER(p,0).NUMBERspecifies a floating-point number without specifying the precision and scale.
The NUMBER data type stores data in different precisions and scales. To avoid exceeding the precision of data stored in OceanBase Database, you must specify the scale and precision for a fixed-point numeric column and perform additional integrity checks on the input data. However, this does not enforce a fixed length for the fixed-point numeric column. If the actual data exceeds the specified precision, OceanBase Database returns an error. If the actual data exceeds the specified scale, OceanBase Database rounds the data. For more information, see the following examples.
| 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 |