一、oracle的number类型精度、刻度范围
number(p,s)
p: 1---38
s: -84---127
有效数位:从左边第一个不为0的数算起,到末位数字为止的所有数字,小数点和负号不计入有效位数。
p>0,对s分3种情况:
1. s>0
精确到小数点右边s位,并四舍五入。然后检验有效数位是否<= p;
ZWF.YUDONG>create table t_n(id number(5,2));
Table created.
-- 小数点前面最多只能有3位,小数点后面位数可以任意多
ZWF.YUDONG>insert into t_n values(123.45);
1 row created.
ZWF.YUDONG>insert into t_n values(123.455);
1 row created.
ZWF.YUDONG>insert into t_n values(12.345);
1 row created.
ZWF.YUDONG>insert into t_n values(1.234);
1 row created.
ZWF.YUDONG>insert into t_n values(.001);
1 row created.
ZWF.YUDONG>select * from t_n;
ID
----------
123.45
123.46
12.35
1.23
0.00
5 rows selected.
ZWF.YUDONG>insert into t_n values(1234.5678); -- 有效位为4 + 2 > 5
insert into t_n values(1234.5678)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ZWF.YUDONG>insert into t_n values(12345); -- 有效位为5 + 2 > 5
insert into t_n values(12345)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
如果s > p,小数点右边至少有s - p个0填充。
ZWF.YUDONG>create table t_n(id number(4,5));
Table created.
ZWF.YUDONG>insert into t_n values(1);
insert into t_n values(1)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ZWF.YUDONG>insert into t_n values(.1); -- 0.10000,有效位为5 > 4
insert into t_n values(.1)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ZWF.YUDONG>insert into t_n values(1.01); -- 1.01000,有效位为6 > 4
insert into t_n values(1.01)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ZWF.YUDONG>insert into t_n values(.01);
1 row created.
ZWF.YUDONG>insert into t_n values(.001);
1 row created.
ZWF.YUDONG>insert into t_n values(.0001);
1 row created.
ZWF.YUDONG>insert into t_n values(.00001);
1 row created.
ZWF.YUDONG>insert into t_n values(.000006);
1 row created.
ZWF.YUDONG>insert into t_n values(.0000001); -- 超过刻度存储0
1 row created.
ZWF.YUDONG>select * from t_n;
ID
-------------
0.01000
0.00100
0.00010
0.00001
0.00001
0.00000
6 rows selected.
2. s<0
精确到小数点左边s位,并四舍五入。然后检验有效数位是否 <= p + |s|
ZWF.YUDONG>create table t_n(id number(5,-2));
Table created.
ZWF.YUDONG>insert into t_n values(123);
1 row created.
ZWF.YUDONG>insert into t_n values(1234);
1 row created.
ZWF.YUDONG>insert into t_n values(12345);
1 row created.
ZWF.YUDONG>insert into t_n values(123456);
1 row created.
ZWF.YUDONG>insert into t_n values(1234567);
1 row created.
ZWF.YUDONG>insert into t_n values(12);
1 row created.
ZWF.YUDONG>insert into t_n values(1);
1 row created.
ZWF.YUDONG>insert into t_n values(.1);
1 row created.
ZWF.YUDONG>insert into t_n values(1234567.6789);
1 row created.
ZWF.YUDONG>select * from t_n;
ID
------------
100
1200
12300
123500
1234600
0
0
0
1234600
9 rows selected.
ZWF.YUDONG>insert into t_n values(12345678);
insert into t_n values(12345678)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
3. s=0 表示整数
number(p) : 相当于 number(p,0),用于指定整数
number :不指定p、s的number,用于表示浮点数,其precision和scale都是oracle所能支持的最大值
总结:
在p< s 这种情况下
1. 只能用来存放大于0小于1的小数。
2. 小数点后紧接着的0的数目至少有s-p个,不然无法正常插入。
3. p用来指定小数点之后的最大有效数字位数。当然不包括小数点后紧接着的0的个数。
4. s是用来限制小数点后的数字位数【当然也就包括小数点后紧接的0】。
在 p > s 这种情况下
小数点前最多能插入:p - s 个数字,但是小数点后的数字可以任意长度
再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.youkuaiyun.com/jiangjunshow