Number of Oracle Data Types

Transferred from: bytes. In 9ir2 and earlier versions, only one inherent data type suitable for storing numeric data is supported. After 10 Gb, two new numeric types are available, native floating-point data types are introduced: binary_float (32-bit single precision) and binary_double (64-bit double precision ). these new data types are based on the IEEE binary floating point algorithm standard, ANSI/IEEE Std 754-1985 [IEEE 754], and contain the text F (binary_float) or D (binary_double), such as 2.07f and 3.20.94d.

Number Data Type

The number type syntax is simple: Number (P, S ):
P: precision, which is the total number of valid data digits. The value range is 38. The default value is 38. The character * can be used to represent 38.
S: decimal place, scale, which is the right digit of the decimal point. The value range is-84 ~ 127. The default value depends on p. If P is not specified, S is the maximum range. If P is specified, S = 0.
P: Is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
S: Is the scale, or the number of digits to the right of the decimal point. The scale can range from-84 to 127.

The P and S of the number type have nothing to do with the underlying storage, and it does not affect how the data is stored on the disk. It only affects what values are allowed and how the values are rounded up, you can think of it as "editing" the data ". To put it simply, the precision bit P indicates the maximum number of valid numbers for a value, and the decimal point S indicates the maximum number of decimal places. In other words, P indicates the total number of valid digits (that is, the maximum number of valid digits is P-S on the left of the decimal point), and s indicates that there are valid digits on the right of the decimal point. For example, data of the number (5, 2) type indicates that there are at most three valid numbers on the left of the decimal point, and at most two valid numbers on the right. In addition, there are up to five valid numbers, numbers that exceed this range cannot be properly stored. Note that the numbers cannot be properly stored, but cannot be stored.

Maximum integer digit = p-S
S is a positive number, and the specified position on the right of the decimal point starts rounding.
Negative Number of S. The specified position on the left of the decimal point starts rounding.
S is 0 or unspecified, rounded to the nearest integer
When P is less than S, it indicates that the number is a number with an absolute value less than 1, and the first s-p digit from the right of the decimal point must be 0, and the second decimal point is reserved.

P> 0, S is divided into two cases:
1. S> 0
Accurate to the second place to the right of the decimal point, and rounded. Then, check whether the valid digit is <= P; if S> P, at least S-p is filled with 0 at the right of the decimal point.
2. S <0
It is accurate to the second place on the left of the decimal point, and rounded up. Then, check whether the valid digit is <= P + | S |

The specific data can be found in the table below.

Value	Datatype	Stored value
123.2564	Number	123.2564
1234.9876	Number (6, 2)	1234.99
12345.12345	Number (6, 2)	Error
1234.9876	Number (6)	1235
12345.345	Number (5,-2)	12300
1234567	Number (5,-2)	1234600
12345678	Number (5,-2)	Error
123456789	Number (5,-4)	123460000
1234567890	Number (5,-4)	Error
12345.58	Number (*, 1)	12345.6
0.1	Number (4, 5)	Error
0.01234567	Number (4, 5)	0.01235
0.09999	Number (4, 5)	0.09999
0.099996	Number (4, 5)	Error

Some of the rows in which the error occurs are because the source data exceeds the value range that can be expressed, and some are because the number of decimal places after rounding exceeds the value range that can be expressed.