All Products
Search
Document Center

NUMBER data type

Last Updated: Jun 18, 2021

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 × 10126 (excluding 1.0 × 10126). 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

Parameter

Value range

Description

p

1~38

Specifies the precision. The value of this parameter is the maximum number of significant decimal digits. The most significant digit is the leftmost non-zero digit and the least significant digit is the rightmost known digit.

s

-84~127

Specifies the number of digits in the fractional part. The value of this parameter is the number of digits from the decimal point to the least significant digit. The scale range is from -84 to 127.

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.

Actual data

Specified As

Stored As

123.89

NUMBER

123.89

123.89

NUMBER(3)

124

123.89

NUMBER(3,2)

Exceeds the specified precision

123.89

NUMBER(4,2)

Exceeds the specified precision

123.89

NUMBER(5,2)

123.89

123.89

NUMBER(6,1)

123.9

123.89

NUMBER(6,-2)

100

.01234

NUMBER(4,5)

.01234

.00012

NUMBER(4,5)

.00012

.000127

NUMBER(4,5)

.00013

.000012

NUMBER(2,7)

.000012

.0000123

NUMBER(2,7)

.000012

1.2e-4

NUMBER(2,5)

.00012

1.2e-5

NUMBER(2,5)

.00001