我申请这个blog是为了督促自己,把自己平时的一些想法和思考结果保留下来。 本博客所有内容均为原创,如有转载请注明作者和出处

有趣的数值溢出(一)

上一篇 / 下一篇  2008-05-11 23:58:26 / 个人分类:ORACLE

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

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

 

 

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

其中正数的范围是从1E-1309.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\%]&H6I nJ{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

OracleNUMBER类型第一位表示的是数值的指数。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个人空间 fG o-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:]X x'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位有效数字。

 


TAG:

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar