有趣的数值溢出（一）

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

原文出自：http://www.itpub.net/thread-984938-1-1.html

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

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

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

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

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

DUMP(1E-130)
!q/nO ti]5v Bv0------------------ITPUB个人空间k gq6w8x'YCU
Typ=2 Len=2: 128,2

SQL> SELECT DUMP(0) FROM DUAL;

DUMP(0)ITPUB个人空间o;V/X"ZQl E
----------------
"a~p-JF.O`0Typ=2 Len=1: 128

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

B
RmJ!b&I;V` V0-------------------------------------------------------------------------------------------ITPUB个人空间\L4y2O%m;YUx4d
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)ITPUB个人空间,B+Gq5C(Ii!HXJ
------------------------
N-Y:zU3Y
J)cm5]0Typ=2 Len=3: 127,100,102

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

B
q5pf[@0---------------------------------------------------------
U.W0F:jo(LT8J-@k,Kf0Typ=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类型的更详细描述，可以参考：http://yangtingkun.itpub.net/post/468/9445

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

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

SQL> SET NUMW 50ITPUB个人空间 a&Cn5iB,|K'c
SQL> SELECT 9.9999999999999999999999999999999999999E125 FROM DUAL;

       9.9999999999999999999999999999999999999E125ITPUB个人空间:A4yV'hc/f
yl
--------------------------------------------------ITPUB个人空间1y xG^CZy"p
9.9999999999999999999999999999999999999000000E+125

SQL> SELECT DUMP(9.9999999999999999999999999999999999999E125) FROM DUAL;

DUMP(9.9999999999999999999999999999999999999E125)ITPUB个人空间%Q{8PS3a+t/RG
--------------------------------------------------------------------------------------------ITPUB个人空间oa,~,N
ioG6r_
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
@/[y,@8CM|'I0--------------------------------------------------
tC.f$C_qDt9l \,J09.9999999999999999999999999999999999999900000E+125

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

B
z
Y6T!z$S!Y1`0------------------------------------------------------------------------------------------------
ZP!|T2M q)A0Typ=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
O
_EF&^+]"c5xD0--------------------------------------------------ITPUB个人空间E6} H*hxU
9.9999999999999999999999999999999999999990000E+125

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

B
&^:q4gU/X:a^4i~0-------------------------------------------------------------------------------------------------
/k B6`jMMZ.}0Typ=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;
R&FV*q7mS0SELECT 9.9999999999999999999999999999999999999999E125 FROM DUALITPUB个人空间l e{HP.C
       *
S$a;Q_@k,?}4v0第1行出现错误:
,M0\^;B&}H-m'kt
^%z,}0ORA-01426:数字溢出

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

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

SQL> SELECT -9.9999999999999999999999999999999999999E125 FROM DUAL;

      -9.9999999999999999999999999999999999999E125
gv| A'aX%sSF0K0--------------------------------------------------ITPUB个人空间7_6X3s%f)]d4e
E5{
-9.999999999999999999999999999999999999900000E+125

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

BITPUB个人空间 W
]Wb,N$w'jk
---------------------------------------------------------
`z:d$_ qP0Typ=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.99999999999999999999999999999999999999E125ITPUB个人空间iT#WE%Y.R5e%~
--------------------------------------------------
O _)z7\1Q"l.{ n+{0L0-9.999999999999999999999999999999999999990000E+125

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

B
S$[,N,gS V0--------------------------------------------------------
[C6VF@1J1WaxyCYt0Typ=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
r$t ^qO-n0--------------------------------------------------ITPUB个人空间8Kn0x
L7tv
-9.999999999999999999999999999999999999999000E+125

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

BITPUB个人空间v0d&NhT
-------------------------------------------------------
1DN0n'?`P0Typ=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;
%GBAZJ:W0SELECT -9.9999999999999999999999999999999999999999E125 FROM DUALITPUB个人空间$`r5j`g$P8S^p%m
        *
:S(X G/e1c*T.I[qs0第1行出现错误:
;pX#kUg2{N#O0ORA-01426:数字溢出

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