Oracle number数据类型
Oracle number数据类型
number( p, s )
p：总的位数
s：小数位数
如：number（5，2），表示整数位数为3位，小数位数为2位
1. number(5,2) :整数位数最多只能是三位数，而小数位数可以超过2位数，插入时四舍五入取值
scott@ORA10G> create table tt (msg varchar2(10),num_col number(5,2));
Table created.
scott@ORA10G> insert into tt values('123.45',123.45);
1 row created.
小数位数四舍五入：
scott@ORA10G> insert into tt values('123.456',123.456);
1 row created.
scott@ORA10G> select * from tt;
MSG NUM_COL
---------- ----------
123.45 123.45
123.456 123.46
整数位数最多为3位数：
scott@ORA10G> insert into tt values('1234',1234);
insert into tt values('1234',1234)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
2. number（5，-2）：整数位数会舍入为最接近的100，小数位数不插入值
scott@ORA10G> create table t
2 (msg varchar(10),
3 num_col number(5,-2)
4 );
Table created.
scott@ORA10G> insert into t values('123.45',123.45);
1 row created.
scott@ORA10G> insert into t values('123.456',123.456);
1 row created.
scott@ORA10G> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
scott@ORA10G> insert into t values('123.4567',123.4567);
1 row created.
scott@ORA10G> insert into t values('223.4567',223.4567);
1 row created.
scott@ORA10G> insert into t values('323.4567',323.4567);
1 row created.
scott@ORA10G> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
123.4567 100
223.4567 200
323.4567 300
scott@ORA10G> commit;
Commit complete.
scott@ORA10G>
scott@ORA10G>
scott@ORA10G> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
123.4567 100
223.4567 200
323.4567 300
整数位数最大可以为7位数，超过7位数就无法插入
scott@ORA10G> insert into t values('1234567',1234567);
1 row created.
scott@ORA10G> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
123.4567 100
223.4567 200
323.4567 300
1234567 1234600
6 rows selected.
scott@ORA10G> insert into t values('12345678',12345678);
insert into t values('12345678',12345678)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
scott@ORA10G> insert into t values('1234567.23',1234567.23);
1 row created.
scott@ORA10G> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
123.4567 100
223.4567 200
323.4567 300
1234567 1234600
1234567.23 1234600
7 rows selected.
