The NUMBER data type stores zeros, floating-point numbers, positive fixed-point numbers, and negative fixed-point numbers. This topic describes the syntax, parameters, and considerations of the NUMBER data type.
Syntax
NUMBER[(p[s])]
Parameters
Notice
When you use the specified p and s parameters for the NUMBER type as the partition key and connect to OceanBase Database using ODP, it is essential to ensure that the ODP version is V4.2.3 or higher to guarantee accurate routing.
| Parameter | Value range | Description |
|---|---|---|
| p | [1,38] | The precision of a number, which specifies the maximum number of significant decimal digits. The most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. |
| s | [-84,127] | The scale of a number, which specifies the number of digits from the decimal point to the least significant digit. Value range for the scale: [-84,127]. |
Note
If the value of s is greater than 0, the numeric value is accurate to
sdecimal places after the decimal point and is rounded. Verify whether the number of significant digits is less than or equal to the value ofp.If the value of s is smaller than 0, the numeric value is accurate to
sdecimal places after the decimal point and is rounded. Verify whether the number of significant digits is less than or equal to the value ofp + |s|.If the value of s is 0, the value is an integer.
Notice
Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit. Both the precision and decimal digits are expressed with decimal numbers.
Negative scale is the number of significant digits to the left of the decimal point to but not including the least significant digit. For negative scale, the least significant digit is on the left side of the decimal point, because the number is rounded to the specified number of places to the left of the decimal point.
Considerations
NUMBER specifies a variable-length and precise numeric value that occupies a space of 4 to 40 bytes, where four bytes store the metadata of the value and the other 36 bytes store the value itself. The absolute value range is [1.0 × 10-130, 1.0 × 10126). If the specified absolute value of an arithmetic expression is greater than or equal to 1.0 × 10126, OceanBase Database returns an error.
The NUMBER data type features higher data precision, versatility, and portability. However, it has a lower operational efficiency when compared with the floating-point data type. In NUMBER [(p[s])], p specifies the precision and s specifies the scale. You can use the following formats to specify integers of the NUMBER data type:
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 precision and scale.
The NUMBER data type stores data by using different precisions and scales. To prevent the data stored by OceanBase Database from exceeding the specified precision, you must specify the scale and precision for fixed-point number columns and perform integrity check on input. However, this does not fix the length of fixed-point number columns. If the stored data exceeds the specified precision, OceanBase Database returns an error. If the stored data exceeds the specified scale, OceanBase Database rounds the value. For more information, see the following table.
| Actual number | Specified as | Stored as |
|---|---|---|
| 135.79 | NUMBER |
135.79 |
| 135.79 | NUMBER(3) |
136 |
| 135.79 | NUMBER(3,2) |
Exceeds the specified precision. |
| 135.79 | NUMBER(4,2) |
Exceeds the specified 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 |