转：oracle number(p,s)数据类型

太过爱你忘了你带给我的痛 2022-08-03 13:52 236阅读 0赞

oracle的number类型是oracle的内置类型之一，是oracle的最基础数值数据类型。在9iR2及其以前的版本中只支持一种适合存储数值数据的固有数据类型，在10g以后，才出现了两种新的数值类型。

oraclenumber类型能以极大的精度存储数值，具体来说，精度可达38位。其底层数据格式类似于一种“封包小数”表示，这个在[我的上篇文章][Link 1]中已经做了阐述。oraclenumber类型是一种变长格式，长度0~22个字节，是目前最为常用的数值数据类型。

number类型的语法很简单：number(p,s)：  
p：精度位，precision，是总有效数据位数，取值范围是38，默认是38，可以用字符\*表示38。  
s：小数位，scale，是小数点右边的位数，取值范围是-84~127，默认值取决于p，如果没有指定p，那么s是最大范围，如果指定了p，那么s=0。

很多人对这里p和s的含义搞不清楚，以为可以存储p个整数，s个小数。其实完全不是这样的，number类型的p和s，与其底层存储完全没有关系，根本不会影响数据在磁盘上如何存储，它只会影响允许哪些值以及数值如何舍入，你可以认为其是对数据的“编辑”。

简单的说，**精度位p表示数值最多能有多少个有效数字，而小数位s表示最多能有多少位小数。**换句话说，**p表示一共有多少位有效数字（即小数点左边最多有p-s位有效数字），s表示小数点右边有s位有效数字。**如number(5,2)类型的数据，就表示小数点左边最多有3位有效数字，右边最多有2位有效数字，加起来就是最多有5位有效数字，超过这个范围的数字就不能正确的存储下来。

我们先模拟，精度位p完全正确的情况。  
15:28:54 SQL> create tablet (a number(5,2));

Table created.

Elapsed: 00:00:00.01  
15:28:57 SQL> insert into tvalues(123.456);

1 row created.

Elapsed: 00:00:00.00  
15:29:00 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01  
15:29:06 SQL> select \* from t;

A  
\----------  
   123.46

Elapsed: 00:00:00.00

可以看到数字被四舍五入了，一共只能有p=5位有效数字，小数点右边最多只能存入s=2位有效数字，s对要存入的数据“123.456”做了四舍五入的“编辑裁剪”。

15:32:20 SQL> createtable t1 (a number(5,-2));

Table created.

Elapsed: 00:00:00.01  
15:34:01 SQL> insert into t1values(123.45);

1 row created.

Elapsed: 00:00:00.00  
15:34:19 SQL> insert into t1values(12345.45);

1 row created.

Elapsed: 00:00:00.00  
15:34:30 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00  
15:34:32 SQL> select \* from t1;

A  
\----------  
      100  
    12300

Elapsed: 00:00:00.01

可以看到，当s<0时，其就表示小数点左边最多能存入s=-2的绝对值2位有效数字，其实还是右边s位（s=-2<0时反向变反），s对存入的数据“123.45”和“12345.45”都做了四舍五入的“编辑裁剪”得到number(5,-2)形式的“100”“12300”。

下面我们接着演示精度位p为不正确的情况。

15:38:28 SQL> createtable t2(a number(5,2));

Table created.

Elapsed: 00:00:00.01  
15:39:21 SQL> insert into t2 values(1234.56);  
insert into t2 values(1234.56)  
                     \*  
ERROR at line 1:  
ORA-01438: value larger than specified precision allowed for thiscolumn

Elapsed: 00:00:00.01  
15:39:35 SQL> create table t3(anumber(5,-2));

Table created.

Elapsed: 00:00:00.01  
15:40:16 SQL> insert into t3values(12345678.9);  
insert into t3 values(12345678.9)  
                     \*  
ERROR at line 1:  
ORA-01438: value larger than specified precision allowed for thiscolumn

Elapsed: 00:00:00.01

可以看到，上面的2种插入都失败了，因为精度为p=5，s=2时，表示小数点左边最多有p-s位，那么第一种情况最多有是5-2=3位，而实际上有4位故出错，第二种情况是最多有5-(-2)=7位，而实际上有8位故出错。

通过上面的解释，相信已经很明白number(p,s)中p和s的含义了，**它们不影响底层数据的存储，而只影响某个数字能否正确存入number(p,s)类型的列中（oracle会根据s来裁剪此数据，保证有效数字有p位，精确到小数点右边s位）。**

[Link 1]: http://ilonng.blog.sohu.com/130335741.html