有趣的数值溢出（一）

在论坛看到一个有趣的帖子，是关于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)
'UO~5?(Yf&E~0------------------
m DvS:{#v.c+r0T S0Typ=2 Len=2: 128,2

SQL> SELECT DUMP(0) FROM DUAL;

DUMP(0)
R5iHrq:@}0----------------
E#@i6Q(ur*cyJ0Typ=2 Len=1: 128

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

B
w6m6rHS*xN/{0-------------------------------------------------------------------------------------------
7GWO/qR9e0Typ=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个人空间+T a)BO&UR
------------------------ITPUB个人空间+rk,X;Ka]!ICjg
Typ=2 Len=3: 127,100,102

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

B
1\8B p/gr0---------------------------------------------------------ITPUB个人空间9a3Gg\Vjh.A p7c._!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类型的更详细描述，可以参考：http://yangtingkun.itpub.net/post/468/9445

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

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

SQL> SET NUMW 50ITPUB个人空间J#TLwo5i%O+p Z
SQL> SELECT 9.9999999999999999999999999999999999999E125 FROM DUAL;

       9.9999999999999999999999999999999999999E125
2h&M8mM7Z$W-`0--------------------------------------------------
3h+V&S:b6LC09.9999999999999999999999999999999999999000000E+125

SQL> SELECT DUMP(9.9999999999999999999999999999999999999E125) FROM DUAL;

DUMP(9.9999999999999999999999999999999999999E125)
piM;Yy(W0--------------------------------------------------------------------------------------------ITPUB个人空间&UG2RZT5Nbvy
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.99999999999999999999999999999999999999E125ITPUB个人空间7S nV6x/rTU7Q:J
--------------------------------------------------
.C]S[suRAm09.9999999999999999999999999999999999999900000E+125

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

B
u'PEV]"p0------------------------------------------------------------------------------------------------
#c9t F!J;~Y!b?/R*H0Typ=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.999999999999999999999999999999999999999E125ITPUB个人空间pk%hlU:A
--------------------------------------------------
#l6s1xhPX09.9999999999999999999999999999999999999990000E+125

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

B
czWd,NC2u0-------------------------------------------------------------------------------------------------ITPUB个人空间O#G']0} K,s6jO
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;
H,n
r gepz5Y&b Dp0SELECT 9.9999999999999999999999999999999999999999E125 FROM DUALITPUB个人空间8gzl_} JZ
       *ITPUB个人空间co.?8F'P'eL'r
第1行出现错误:
?;QfvT'X6}&T0ORA-01426:数字溢出

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

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

SQL> SELECT -9.9999999999999999999999999999999999999E125 FROM DUAL;

      -9.9999999999999999999999999999999999999E125
/sB;L
d+IP1\6d0--------------------------------------------------
R"N)J Rgq0-9.999999999999999999999999999999999999900000E+125

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

B
z;j-Rst?wL8s0---------------------------------------------------------
'h^_/tf'm0Typ=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个人空间^V&C
~v
--------------------------------------------------
R
g;Yq Tje0-9.999999999999999999999999999999999999990000E+125

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

B
9p.V6Mj G|0--------------------------------------------------------
W,wY%lp2q/P1gV0Typ=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
D
DU9`W
|+|0--------------------------------------------------ITPUB个人空间_IYb,B d
`et]
-9.999999999999999999999999999999999999999000E+125

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

BITPUB个人空间j8ly3]'dI3Hh:kp
-------------------------------------------------------
0H*qOAZDt b-E w9x0Typ=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;ITPUB个人空间%_9Oo1r1\Y!DkU%G
SELECT -9.9999999999999999999999999999999999999999E125 FROM DUAL
E$G#d+NPn0{0        *
+B"\9DPe7r0第1行出现错误:ITPUB个人空间t1@7QtoQ4jV9_
ORA-01426:数字溢出

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