NUMBER data type

NUMBER is a numeric data type that stores variable-length and exact values. Each value of this data type occupies 4 to 40 bytes of storage space. The storage space of 4 bytes is used to store NUMBER metadata and the storage space of 36 bytes is used to store specific NUMBER values. The NUMBER data type can store zeros, floating-point numbers, positive fixed-point numbers, and negative fixed-point numbers. The absolute values that can be stored range from 1.0 × 10^-130^ to 1.0 × 10^126^ (excluding 1.0 × 10^126^). ApsaraDB for OceanBase returns an error if the absolute value of the specified arithmetic expression is greater than or equal to 1.0 × 10^-130^.

The NUMBER data type provides higher data accuracy and is more general and portable than the floating-point data type. The floating-point data type enables more efficient arithmetic operations than the NUMBER data type.

Syntax

NUMBER [(p[s])]

Parameters

Note

• If s is greater than 0, the value is rounded to s digits on the right of the decimal point. Then, the system checks whether the number of significant digits is smaller than or equal to p.

• If s is smaller than 0, the value is rounded to s digits on the left of the decimal point. Then, the system checks whether the number of significant digits is smaller than or equal to p + |s|.

• If s is equal to 0, the value is an integer.

Notice

• The positive scale for decimal places is the number of significant digits from the first digit on the right of the decimal point to the least significant digit. Precisions and decimal places are represented by decimal digits.

• The negative scale for decimal places is the number of significant digits on the left of the decimal point, excluding the least significant digit. For the negative scale, the least significant digit is on the left of the decimal point because the actual data is rounded to the specified number of digits on the left of the decimal point.

Examples

• Example 1: Use the following format to specify an integer:

NUMBER(p) specifies a fixed-point number with a precision of p and a scale of 0. NUMBER(p) is equivalent to NUMBER(p,0).

NUMBER specifies a floating-point number. In this format, the designators for precisions and decimal places are absent.

• Example 2: Store data that has different precisions and different numbers of decimal places. To prevent the precisions of the data stored in ApsaraDB for OceanBase from exceeding the specified precisions, specify the precisions and the number of decimal places for fixed-point number columns. You must also perform integrity checks on the input data. However, this does not enforce a fixed length of the values in the fixed-point number columns. If the precisions of the actual data to be stored exceed the specified precisions, ApsaraDB for OceanBase returns an error. If the number of decimal places of the actual data to be stored exceeds the specified value, ApsaraDB for OceanBase rounds the data.