NUMBER长度的误解

野性酷女 2023-05-22 04:40 19阅读 0赞

朋友问了个问题，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(不包括)。

**近期热文：**

《[《decode函数的妙用》网友的两个问题解答][decode]》

《[decode函数的妙用][decode 1]》

《[虚拟内存详解][Link 1]》

《[7号球衣的故事：重要的不是号码，而是穿着它的人][7]》

《[插入"&"特殊字符的几种思考][Link 2]》

《[connect by超乎你想象][connect by]》

《[TOEIC科普一下][TOEIC]》

[decode]: https://blog.csdn.net/bisal/article/details/105445417
[decode 1]: https://blog.csdn.net/bisal/article/details/105424100
[Link 1]: https://blog.csdn.net/bisal/article/details/105383561
[7]: https://blog.csdn.net/bisal/article/details/105283168
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815f05ddf6d613a8fd4f4454278476e27e37675d810d3aa4e74a8e0c8d06b6e168ea9c8ecb&idx=1&mid=2247490294&scene=21&sn=46ac00aeef5cae9c81dcd234c9766853#wechat_redirect
[connect by]: https://blog.csdn.net/bisal/article/details/105236326
[TOEIC]: https://blog.csdn.net/bisal/article/details/105383559