number类型有些困惑,google之后,摘录如下：
number(p, s):
1、绝对是 p > s, or error;
2、所存储的数据整数位数肯定小于p;
可能有理解误差 ^_^


Digest below:
------------------------------
number(p,s) p:1~38? s:-84~127


最高整数位数＝p-s
s正数，小数点右边指定位置开始四舍五入
s负数，小数点左边指定位置开始四舍五入
s是0或者未指定，四舍五入到最近整数
当p小于s时候，表示数字是绝对值小于1的数字，且从小数点右边开始的前s-p 位必须是0，保留s位小数。

p>0，对s分2种情况：
1. s>0
精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；如果s>p，小数点右边至少有s-p个0填充。
2. s<0
精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s|
比如
Value???? Datatype???? Stored Value
123.2564??? NUMBER??? 123.2564
1234.9876??? NUMBER(6,2)??? 1234.99
12345.12345??? NUMBER(6,2)??? Error
1234.9876??? NUMBER(6)??? 1235
12345.345??? NUMBER(5,-2)??? 12300
1234567??? NUMBER(5,-2)??? 1234600
12345678??? NUMBER(5,-2)??? Error
123456789??? NUMBER(5,-4)??? 123460000
1234567890??? NUMBER(5,-4)??? Error
12345.58??? NUMBER(*, 1)??? 12345.6
0.1??? NUMBER(4,5)??? Error
0.01234567??? NUMBER(4,5)??? 0.01235
0.09999??? NUMBER(4,5)??? 0.09999
0.099996??? NUMBER(4,5)??? <>

from: http://www.blogjava.net/leekiang/archive/2007/07/03/127956.aspx
------------------------

ORACLE NUMBER数据类型！


网上关于number的资料很多了，学习总结了下，如果问题及不足，欢迎指正。

一、oracle的number类型精度、刻度范围

number(p,s)

p:1---38
s:-84---127

有效数位：从左边第一个不为0的数算起，小数点和负号不计入有效位数。

p>0，对s分2种情况：

1. s>0

精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；

ZWF.YUDONG>create table t_n(id number(5,2));

Table created.

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>select * from t_n;

??????? ID
----------
??? 123.45
??? 123.46

2 rows selected.

ZWF.YUDONG>insert into t_n values(1.234);

1 row created.

ZWF.YUDONG>select * from t_n;

??????? ID
----------
??? 123.45
??? 123.46
????? 1.23

3 rows selected.

ZWF.YUDONG>insert into t_n values(.001);

1 row created.

ZWF.YUDONG>select * from t_n;

??????? ID
----------
??? 123.45
??? 123.46
????? 1.23
???????? 0

4 rows selected.

ZWF.YUDONG>insert into t_n values(1234.56);
insert into t_n values(1234.56)
?????????????????????? *
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);
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(.01);

1 row created.

ZWF.YUDONG>commit;

Commit complete.

ZWF.YUDONG>select * from t_n;

??????? ID
----------
?????? .01

1 row selected.

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(.000001);?? --超过刻度存储0

1 row created.

ZWF.YUDONG>select * from t_n;

??????? ID
----------
?????? .01
????? .001
???? .0001
??? .00001
???????? 0


10 rows selected.

ZWF.YUDONG>c