NUMBER (x, y) Data Types in Oracle databases, oraclenumber

NUMBER (x, y) Data Types in Oracle databases, oraclenumber

• Test preparation
• Analyze and Experiment
  • Processing of 1 decimal part
  • 2. Maximum value that can be saved
  • 3. Minimum value that can be saved
• Summary

The NUMBER data type of Oracle indicates a decimal NUMBER, which is limited by the NUMBER of digits and the NUMBER of decimal places. We use NUMBER (8, 2) as an example for analysis.

1. Test preparation

Create a table with only one column. NUMBER (8, 2) indicates that the total NUMBER of digits can be 8 at most, and the decimal part can be 2 digits at most (the integer part can be 8-2 = 6 digits at most ).

create table t1 (c1 number(8,2));

2. Analyze and experiment the processing of the decimal part of 2.1

The fractional part represents the accuracy. NUMBER (8, 2) can store up to two decimal places. What if the NUMBER exceeds two places?

SQL> insert into t1 values(1.234); 1 row created.

SQL> select * from t1; C1 ---------- 1.23

SQL> delete from t1; 1 rows deleted.

SQL> insert into t1 values(1.235); 1 row created.

SQL> select * from t1; C1 ---------- 1.24

It can be seen that the decimal part uses the rounding Algorithm for the part after the decimal point of 2 digits. The 1.234 memory is 1.23, And the 1.235 memory is 1.24.

2.2 maximum value that can be saved

Since the integer part has a maximum of 6 digits and the fractional part has a maximum of 2 digits, the maximum number (8, 2) that can be expressed is999999.99. We insert the maximum value.

SQL> insert into t1 values(999999.99); 1 row created.

SQL> select * from t1; C1 ---------- 999999.99

This number is properly stored.
Insert a larger number below999999.991.

SQL> insert into t1 values(999999.991); 1 row created.

SQL> select * from t1; C1 ---------- 999999.99

It can be inserted, but it is truncated to 999999.99. In fact, the reason is very simple. 0.991 of the decimal part is rounded to 0.99. After rounding, the value is 999999.99, which is within the range.

Let's take a look at 999999.995.

SQL> insert into t1 values(999999.995);
insert into t1 values(999999.995)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Insertion failed! The reason is that after rounding the decimal part of 999999.995, it turns to 1000000.00, which is beyond the 6-digit integer range.

2.3 minimum value that can be saved

The range of negative numbers is symmetric with the range of positive numbers, so the minimum value is-999999.99.

3. Conclusion

NUMBER (8, 2) indicates the NUMBER range is[-999999.99, 999999.99]. Before determining whether a given number can be in this range, round the fractional part and compare it. Judgment steps:

• (1) round a decimal number of 3rd digits to obtain a number with two decimal places;
• (2) Determine whether the number is in[-999999.99, 999999.99].