NUMBER长度的误解

朋友问了个问题，Oracle中NUMBER最大长度是多少？

我们看下官方文档对NUMBER类型的介绍，

The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to but not including 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes. The precision p can range from 1 to 38. A NUMBER value requires from 1 to 22 bytes.

NUMBER类型是Oracle的一种变长数值类型，他的取值范围是10^(-130)-10^126(不包括)，精度是38位，存储空间是1-22字节。

轻描淡写，说了好几个数字，虽然很多人都知道这个NUMBER类型，但是这些数字，就未必能一下就能说清楚。

1. 精度是38，是说允许38个数字？

NUMBER类型的定义格式，NUMBER(P,S)，其中P是Precison的英文缩写，即精度缩写，表示有效数字的位数。S是Scale的英文缩写，表示从小数点到最低有效数字的位数，他为负数时，表示从最大有效数字到小数点的位数。

因此精度38，表示的是最多不能超过38个有效数字，如果超过了，就会提示错误，如下所示，

SQL> create table t (id number(39));
create table t (id number(39))
                            *
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)

如果字段定义的时候未指定精度，则默认是22，

SQL> create table t (id number);
Table Created.


SQL> SELECT table_name, column_name, data_type, data_length 
     FROM user_tab_columns WHERE table_name='TT';
TABLE_NAME COLUMN_NAM DATA_TYPE  DATA_LENGTH
---------- ---------- ---------- -----------
        T         ID    NUMBER           22

精度(p)和刻度(s)遵循以下规则：

• 当一个数的整数部分的长度 > p-s 时，Oracle就会报错
• 当一个数的小数部分的长度 > s 时，Oracle就会舍入。
• 当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。
• 当s > p 时, p表示小数点后第s位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入

但是有个奇怪的现象，向表插入如下的数据，设置显示格式，

SQL> create table t (id number(38));
Table Created.




SQL> col id for 99999999999.9999999999999999999999999999999999999;


SQL> insert into t values(12.12345678912345678912345678912345678912,'');
1 row created.


SQL> select * from test;
                                        ID
-------------------------------------------
   12.123456789123456789123456789123456789  - 38位有效数字

但是改下显示的格式，发现有效数字，就是40个，超过了定义精度，难道存在bug？

SQL> col id  for 999999999999.99999999999999999999999999999999999999999
SQL> select * from t;
                                              ID
--------------------------------------------------
     12.12345678912345678912345678912345678912000

官方文档，给出了这个解释，p是精度，或是有效十进制数的总位数，最大的有效数字是最左边的非零数字，而最小有效位是最右边的数字。Oracle保证数字的可移植性，精度高达20 base-100 digits，相当于39位或40位十进制数字，取决于小数点的位置，因此上述例子中，40个有效数字，其实是合理的，

p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.

2.取值范围是10^(-130)-10^126(不包括)

可以看到，当插入10^126的数据时，就会提示溢出，这是能存入NUMBER类型字段的正数最大值，

SQL> insert into t values(1e+125);
1 row created.


SQL> insert into t values(1e+126);
insert into t values(1e+126)
                      *
ERROR at line 1:
ORA-01426: numeric overflow

因为最大值是10^126(不包括)，相当于<10^126，最近的值就是9.9...9 * 10^125，即38个9后边带88个0。

针对这个问题，“Oracle中NUMBER最大长度是多少？”，他的迷惑性，就在于对他和字符串类型的惯性思维。因为字符串类型，VARCHAR2(X)，X就是字符个数，而NUMBER(X)，X是精度，X是5，未必就是5个数字，其实深究起来，有效位数的含义，和VARCHAR2(X)中的X是相近的，只是除了有效位数外，还可以用0补位，数字个数，就会超过X。准确地说，NUMBER取值范围就是10^(-130)-10^126(不包括)。