oracle查询数字类溢出,有趣的数值溢出（一）

在论坛看到一个有趣的帖子，是关于ORACLE的NUMBER类型溢出的。

Oracle的数值类型NUMBE包括0、正数和负数。

其中正数的范围是从1E-130到9.9999999999999999999999999999999999999E125。

而负数的范围是从-1E-130到-9.9999999999999999999999999999999999999E125。

Oracle的数值范围是由于NUMBER类型的存储结构决定的，下面看一下这些边界数值的DUMP值就会明白：

SQL> SELECT DUMP(1E-130) FROM DUAL;

DUMP(1E-130)

------------------

Typ=2 Len=2: 128,2

SQL> SELECT DUMP(0) FROM DUAL;

DUMP(0)

----------------

Typ=2 Len=1: 128

SQL> SELECT DUMP(9.9999999999999999999999999999999999999E125) B FROM DUAL;

B

-------------------------------------------------------------------------------------------

Typ=2 Len=20: 255,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

SQL> SELECT DUMP(-1E-130) FROM DUAL;

DUMP(-1E-130)

------------------------

Typ=2 Len=3: 127,100,102

SQL> SELECT DUMP(-9.9999999999999999999999999999999999999E125) B FROM DUAL;

B

---------------------------------------------------------

Typ=2 Len=21: 0,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,102

Oracle的NUMBER类型第一位表示的是数值的指数。128也就是一个字段最大值256的一半表示0，如果指数大于等于128，则表示正数，否则指数小于128则表示负数。因此正数的上线指数为255，正数的下线指数为128。

而负数的最大值指数为127，最小值指数为0。了解了这些也就清楚了NUMBER类型范围的由来。关于NUMBER类型的更详细描述，可以参考：

下面就可以看看溢出的情况了。

首先来看看最大的正数和最小的负数溢出情况：

SQL> SET NUMW 50

SQL> SELECT 9.9999999999999999999999999999999999999E125 FROM DUAL;

9.9999999999999999999999999999999999999E125

--------------------------------------------------

9.9999999999999999999999999999999999999000000E+125

SQL> SELECT DUMP(9.9999999999999999999999999999999999999E125) FROM DUAL;

DUMP(9.9999999999999999999999999999999999999E125)

--------------------------------------------------------------------------------------------

Typ=2 Len=20: 255,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

设置NUMWIDTH是为了能在SQLPLUS中输出数位很长的数值。

根据Oracle文档，上面这个值是Oracle可以表示的最大的正数，但是由于负数有一个“排序位”，因此实际上NUMBER类型的长度可以达到21，也就是说，Oracle可以表示的正数最大值可以再增加两个9：

SQL> SELECT 9.99999999999999999999999999999999999999E125 FROM DUAL;

9.99999999999999999999999999999999999999E125

--------------------------------------------------

9.9999999999999999999999999999999999999900000E+125

SQL> SELECT DUMP(9.99999999999999999999999999999999999999E125) B FROM DUAL;

B

------------------------------------------------------------------------------------------------

Typ=2 Len=21: 255,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,91

SQL> SELECT 9.999999999999999999999999999999999999999E125 FROM DUAL;

9.999999999999999999999999999999999999999E125

--------------------------------------------------

9.9999999999999999999999999999999999999990000E+125

SQL> SELECT DUMP(9.999999999999999999999999999999999999999E125) B FROM DUAL;

B

-------------------------------------------------------------------------------------------------

Typ=2 Len=21: 255,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

SQL> SELECT 9.9999999999999999999999999999999999999999E125 FROM DUAL;

SELECT 9.9999999999999999999999999999999999999999E125 FROM DUAL

*第1行出现错误:

ORA-01426:数字溢出

观察SELECT结果的有效数位就可以看到，Oracle实际上确实保存了40位有效数字。而当9的位数超过40，就会导致溢出。

同样的道理，现在来看最小的负数：

SQL> SELECT -9.9999999999999999999999999999999999999E125 FROM DUAL;

-9.9999999999999999999999999999999999999E125

--------------------------------------------------

-9.999999999999999999999999999999999999900000E+125

SQL> SELECT DUMP(-9.9999999999999999999999999999999999999E125) B FROM DUAL;

B

---------------------------------------------------------

Typ=2 Len=21: 0,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,102

由于Oracle存在符号位，因此Oracle仍然可以使用符号位来记录数值：

SQL> SELECT -9.99999999999999999999999999999999999999E125 FROM DUAL;

-9.99999999999999999999999999999999999999E125

--------------------------------------------------

-9.999999999999999999999999999999999999990000E+125

SQL> SELECT DUMP(-9.99999999999999999999999999999999999999E125) B FROM DUAL;

B

--------------------------------------------------------

Typ=2 Len=21: 0,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,11

SQL> SELECT -9.999999999999999999999999999999999999999E125 FROM DUAL;

-9.999999999999999999999999999999999999999E125

--------------------------------------------------

-9.999999999999999999999999999999999999999000E+125

SQL> SELECT DUMP(-9.999999999999999999999999999999999999999E125) B FROM DUAL;

B

-------------------------------------------------------

Typ=2 Len=21: 0,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2

SQL> SELECT -9.9999999999999999999999999999999999999999E125 FROM DUAL;

SELECT -9.9999999999999999999999999999999999999999E125 FROM DUAL

*第1行出现错误:

ORA-01426:数字溢出

从这里看到，Oracle并非是在38位有效数值后溢出，而溢出值上限位40位有效数字。