p:精度位,precision,是总有效数据位数,取值范围是38,默认是38,可以用字符*表示38。
s:小数位,scale,是小数点右边的位数,取值范围是-84~127,默认值取决于p,如果没有指定p,那么s是最大范围,如果指定了p,那么s=0。
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;
----------
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;
----------
Elapsed: 00:00:00.01
可以看到,当s<0时,其就表示小数点左边最多能存入s=-2的绝对值2位有效数字,其实还是右边s位(s=-2<0时反向变反),s对存入的数据“123.45”和“12345.45”都做了四舍五入的“编辑裁剪”得到number(5,-2)形式的“100”“12300”。
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位故出错。