NUMBER (x, y) Data Types in Oracle databases

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.

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.

Test preparation

Analyze and Experiment

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 decimal part 2.1

The fractional part represents the precision. The 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 & gt; select * from t1; C1----------1.23SQL & gt; delete from t1; 1 rows deleted. SQL> insert into t1 values (1.235); 1 row created. SQL & gt; 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 is 999999.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 value of 999999.991 below.

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 within the range of [-999999.99, 999999.99.

This article permanently updates the link address: