请问使用number类型时，指定位宽和不指定是什么区别？

number类型众所周知的是其在表示10进制整型时，最大长度是38位。
如果采用number(p,s)的方法定义，证明确实是这样：
SQL> create table test_number2(aa number(38));     <<<<指定38位宽，创建成功Table created.SQL> create table test_number2(aa number(39));
create table test_number2(aa number(39))
                                    *
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)     <<<< 定义超过38位，报错
SQL> insert into test_number2 values(10000000000000000000000000000000000000);1 row created.SQL> select * from test_number2;       AA
----------------------------------------
  10000000000000000000000000000000000000SQL> set numwidth 10;
SQL> / AA
----------
1.0000E+37             <<<< 查出来确实是38位SQL> insert into test_number2 values(100000000000000000000000000000000000000);
insert into test_number2 values(100000000000000000000000000000000000000)
                                *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column      <<<< 多插一位，也报错了
但是，如果采用number定义，但不指定位宽，我发现则完全不受38位长度的限制：
SQL> create table test_number(aa number);     <<<< 不指定位宽Table created.SQL> insert into test_number values(10000000000000000000000000000000000000);   <<<< 插入38位十进制整数，成功1 row created.SQL> insert into test_number values(100000000000000000000000000000000000000);    <<<< 插入39位十进制整数，也成功！1 row created.SQL> select * from test_number;       AA
----------------------------------------
    18446744073709551616
  10000000000000000000000000000000000000
100000000000000000000000000000000000000

SQL> set numwidth  10;
SQL> select * from test_int; AA
----------
1.8447E+19
1.0000E+37
1.0000E+38SQL> insert into test_int values(to_number('ffffffffffffffffffffffffffffffffffffff', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'));     <<<<尝试插的更长，依然成功1 row created.SQL> select * from test_int;       AA
----------------------------------------
    18446744073709551616
  10000000000000000000000000000000000000
100000000000000000000000000000000000000
1000000000000000000000000000000000000000
9999999999999999999999999999999999999999     <<40位
5.7089907708238395242331438777979805E+45     <<46位
看来使用number类型时，指定位宽和不指定位宽是完全不同的效果。
但是它们到底是什么区别呢？
不知道不指定位宽时，oracle对number是怎么处理的？
是不是自动做了隐式类型转换了？

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

NUMBER Datatype
The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision.The following numbers can be stored in a NUMBER column:Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digitsNegative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digitsZeroPositive and negative infinity (generated only by importing from an Oracle Version 5 database)For numeric columns, you can specify the column as:column_name NUMBER Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):column_name NUMBER (precision, scale) If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.-- 如果精度没有指定，那么保存插入的值，如果刻度没有指定，默认是0.Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:column_name NUMBER (*, scale) In this case, the precision is 38, and the specified scale is maintained.When you specify numeric fields, it is a good idea to specify the precision and scale. This provides extra integrity checking on input.
更多信心参考：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716
Number默认情况下，精度为38位，取值范围1~38之间。它实际上是磁盘上的一个变长类型，会占用0~22 字节的存储空间。但定义的一个Number型字段到底要占用多少字节呢？其实有公式可以计算：
number(p,s)占用得空间为：
length = floor((p+1)/2) + 1
备注：如果该数值为负数，需要再加一个字节。----------------
例如：NUMBER(14,4)的类型数值，存储空间为select floor((14+1)/2) + 1 from dual
结果输出为： 8
非常感谢tianlesoftware
不过俺E文比较差，这句话没太看明白：
Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits.
我理解的翻译是：正数的范围是1 x 10-130到9.99...9 x 10125多达38的精度。
我有点晕的是38位精度怎么可以达到10的125次方呢？另外：
Oracle guarantees portability of numbers with a precision equal to or less than 38 digits.
是不是可以理解为，oracle的number类型本身就是没有38位精度限制的，只不过为了提高可移植性，所以提供了限制38位宽的选择？
明白，但是我做了一个实验，似乎感觉number表示正整数时最大精度可以达到40位：
SQL> insert into test_number values(123456789012345678901234567890123456789);1 row created.SQL> insert into test_number values(123456789012345678901234567890123456788);1 row created.SQL> select * from test_number where aa=123456789012345678901234567890123456789;   <<插入两个39位宽的正整数，能够正常存储和比较 AA
--------------------------------------------------
   123456789012345678901234567890123456789SQL> insert into test_number values(1234567890123456789012345678901234567891);1 row created.SQL> insert into test_number values(1234567890123456789012345678901234567892);1 row created.SQL> select * from test_number; AA
--------------------------------------------------
   123456789012345678901234567890123456789
   123456789012345678901234567890123456788
  1234567890123456789012345678901234567891
  1234567890123456789012345678901234567892SQL> select * from test_number where aa=1234567890123456789012345678901234567891; AA
--------------------------------------------------
  1234567890123456789012345678901234567891SQL> select * from test_number where aa=1234567890123456789012345678901234567892;   <<插入两个40位宽的正整数，依然能够成功并精确比较和匹配。 AA
--------------------------------------------------
  1234567890123456789012345678901234567892SQL> select * from test_number where aa<12345678901234567890123456789012345678900 and aa>1234567890123456789012345678901234567891; AA
--------------------------------------------------
  1234567890123456789012345678901234567892SQL> insert into test_number values(12345678901234567890123456789012345678912);1 row created.SQL> insert into test_number values(12345678901234567890123456789012345678921);   <<插入两个41位宽的正整数1 row created.SQL> select * from test_number; AA
--------------------------------------------------
   123456789012345678901234567890123456789
   123456789012345678901234567890123456788
  1234567890123456789012345678901234567891
  1234567890123456789012345678901234567892
12345678901234567890123456789012345678900
123456789012345678901234567890123456789006 rows selected.SQL> select * from test_number where aa=12345678901234567890123456789012345678912;  <<发现已经无法精确匹配了，最后两位的精度丢失 AA
--------------------------------------------------
12345678901234567890123456789012345678900
12345678901234567890123456789012345678900SQL> select * from test_number where aa>12345678901234567890123456789012345678900; no rows selectedSQL> select dump(aa) from test_number where aa=12345678901234567890123456789012345678912;DUMP(AA)
--------------------------------------------------------------------------
Typ=2 Len=21: 213,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90
Typ=2 Len=21: 213,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90SQL> select dump(aa) from test_number where aa<12345678901234567890123456789012345678900 and aa>1234567890123456789012345678901234567890;DUMP(AA)
-----------------------------------------------------------------------------
Typ=2 Len=21: 212,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79,92
Typ=2 Len=21: 212,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79,93