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
- The
NUMBERdata type does not support theporsparameters. - The positive scale of decimal places is the number of significant digits from the decimal point to the least significant digit, including the least significant digit. Both precision and decimal places are represented in base 10.
- The negative scale of decimal places 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 | Value range | Description |
|---|---|---|
| p | [1,38] | The precision, which is the maximum number of significant digits, where the leftmost digit is the most significant digit and the rightmost digit is the least significant digit. |
| s | [-84,127] | The scale, which is the number of digits from the decimal point to the least significant digit. The scale range is [-84,127].
Note
|
Considerations
The NUMBER data type is a variable-length, precise numeric type. It occupies 4 to 40 bytes of storage space, where 4 bytes store metadata information about NUMBER and 36 bytes store the specific numeric value. The absolute value range is 1.0 × 10-130 ~ 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 has high data precision, strong portability, and good compatibility. However, its computational efficiency is relatively low compared to floating-point types. In NUMBER [(p[s])], p usually indicates the precision and s usually indicates the scale. The NUMBER data type uses the following format to specify integers:
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 indicators.
The NUMBER data type stores data with different precision and scale. To prevent OceanBase Database from storing data exceeding the specified precision, you must specify the scale and precision for fixed-point numeric columns and perform additional integrity checks on the input. However, this does not enforce a fixed length for fixed-point numeric 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 data. For more information, see the following table.
| 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 |