有趣的数值溢出（一）

在论坛看到一个有趣的帖子，是关于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)ITPUB个人空间$qw1^8g?3YJq
------------------
~3j7B2Z/MK3Y"MK0Typ=2 Len=2: 128,2

SQL> SELECT DUMP(0) FROM DUAL;

DUMP(0)ITPUB个人空间5o:g-B j-Ww
----------------
)O,MoZ*d l0Typ=2 Len=1: 128

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

B
$Y:Y-t{I@4Cn0-------------------------------------------------------------------------------------------ITPUB个人空间lX0D)~q
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)
h8\%]&H6InJ{0------------------------ITPUB个人空间+l@$l|!e-WW;Q.C
Typ=2 Len=3: 127,100,102

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

BITPUB个人空间-k(MF8cq Cu
---------------------------------------------------------
?En Bv`r0Typ=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 50
`Er$Oxp\g'x5p`0SQL> SELECT 9.9999999999999999999999999999999999999E125 FROM DUAL;

       9.9999999999999999999999999999999999999E125ITPUB个人空间.]4Mojf B0\
--------------------------------------------------
o$eC!D'V0`09.9999999999999999999999999999999999999000000E+125

SQL> SELECT DUMP(9.9999999999999999999999999999999999999E125) FROM DUAL;

DUMP(9.9999999999999999999999999999999999999E125)ITPUB个人空间0N5|kTKz'y
--------------------------------------------------------------------------------------------
.j'Js,R.F^I(y/J0Typ=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个人空间YY6X+D@;A^d
--------------------------------------------------
+v iB @S5aN.b09.9999999999999999999999999999999999999900000E+125

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

BITPUB个人空间"TQ {;B{-C2@
------------------------------------------------------------------------------------------------ITPUB个人空间@ _]0@nk5f(j
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.999999999999999999999999999999999999999E125ITPUB个人空间 fGo-n V|Mx x
--------------------------------------------------ITPUB个人空间J:dg^0A F|9S#]
9.9999999999999999999999999999999999999990000E+125

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

B
^)@)q7V,AI:@"m\0-------------------------------------------------------------------------------------------------ITPUB个人空间K2]z,_'z!a!`
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;ITPUB个人空间*n9yI8M:]Xx'V
SELECT 9.9999999999999999999999999999999999999999E125 FROM DUAL

I;cJQKNE0       *ITPUB个人空间mm3?o$S"|D
第1行出现错误:
cM%f't;F0ORA-01426:数字溢出

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

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

SQL> SELECT -9.9999999999999999999999999999999999999E125 FROM DUAL;

      -9.9999999999999999999999999999999999999E125
`8W1L5X&Z5I0K0--------------------------------------------------ITPUB个人空间8D'D&Q?D Y!P
?
-9.999999999999999999999999999999999999900000E+125

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

B
JnB;k$LBG m*t0---------------------------------------------------------
@ G}^2mv1{tZ0Typ=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
7Ko1v-k#W/rJ6B0--------------------------------------------------
&e@_.]%[}6t/b0-9.999999999999999999999999999999999999990000E+125

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

BITPUB个人空间#o1p.Wp,I
--------------------------------------------------------ITPUB个人空间l? @z.z^*?+{
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
{._3Ze,h$Hvw7X0--------------------------------------------------
/J%vH+S}0-9.999999999999999999999999999999999999999000E+125

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

BITPUB个人空间,e%X+}6zP
-------------------------------------------------------ITPUB个人空间;{&uQ4[z)hg
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;
i`My\8G*T0SELECT -9.9999999999999999999999999999999999999999E125 FROM DUAL
2hN{B+J7\"J0        *ITPUB个人空间Dt hp.N}7UU
第1行出现错误:ITPUB个人空间6a[a2mU{9Q.j |&a mV
ORA-01426:数字溢出

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