Oracle number数据类型

来源：互联网发布：学java有前途吗编辑：程序博客网时间：2024/05/23 19:47

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.