SQL> create table test (f1 number(8,-4));Table created.SQL> insert into test values (123456789012);1 row created.SQL> insert into test values (1234567890123); insert into test values (1234567890123) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column SQL> insert into test values (123456789012.123456) ;1 row created.SQL> select * from test; F1 ---------- 1.2346E+11 1.2346E+11SQL> select to_char(f1) from test;TO_CHAR(F1) ---------------------------------------- 123456790000 123456790000number(p,s) p(precision)是指精度,即可以保存多少个数字 s(scale)是指刻度,不好说明,大概可以理解为数值的最小单位。
奇怪,我用pl/sql developer 工具的编辑功能输入1234567890.00013,为什么报错呢?报错内容是1234567890.00013 is not a valid integer value.用语句为什么好使呢?
SQL> insert into test values(1234567890.00013);1 row created.SQL> select to_char(f1) from test;TO_CHAR(F1) ---------------------------------------- 123456790000 123456790000 1234570000你用sqlplus试一下吧,我这边没有pl/sql developer
还是那句话,为什么用pl/sql developer工具编辑模式就不行,用sql语句就可以啊?
SQL> create table test (f1 number(38,76));Table created.SQL> insert into test values (0.00000000000000000000000000000000000000 1234567901234567890123456789012345678);1 row created.SQL> select to_char(f1) from test;TO_CHAR(F1) ---------------------------------------- 1.2345679012345678901234567890123457E-39 要多试验才能理解number(p,s)中p与s的关系
具体我也不知道是谁报的错,反正是从工具里弹出的,内容是ORA-01438: value larger than specified precision allows for this column
我执行了insert into test values (0.00000000000000000000000000000000000000 1234567901234567890123456789012345678); 结果是0.000000000000000000。奇怪! 执行insert into test values (0.123); 报错
按理说执行insert into test values (0.123);结果不就是0.123在后面补零吗?
第二个数字是精度,精度范围是-84到127;精度为正数时很好理解就是小数点后几位,为负数时则刚好相反,比如
数字7456123.89
NUMBER(7,-2) 7456100
NUMBER(9,2) 7456123.89
NUMBER(7,2) 精度溢出第三个精度溢出是因为,2表明需要精确到小数点后2位,所以7456123.89应该是7456123.89,但是总长度已经超过7位,所以报错。
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#sthref83
定义类型
ntest NUMBER(8,-4)
填入的数据是:12345678911,不让存
填入:1234567891,显示1234570000
我测试的结果没问题
SQL> create table test0123(id number(8,-4));Table createdSQL> insert into test0123 values(1234567890);1 row insertedSQL> insert into test0123 values(12345678901);1 row insertedSQL> insert into test0123 values(123456789012);1 row insertedSQL> insert into test0123 values(1234567890123);insert into test0123 values(1234567890123)ORA-01438: value larger than specified precision allowed for this columnSQL> insert into test0123 values(1234567890.0001);1 row insertedSQL> insert into test0123 values(1234567890.00013);1 row insertedSQL> select * from test0123;select * from test0123ORA-01455: converting column overflows integer datatype
SQL> create table test (f1 number(8,-4));Table created.SQL> insert into test values (123456789012);1 row created.SQL> insert into test values (1234567890123);
insert into test values (1234567890123)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> insert into test values (123456789012.123456) ;1 row created.SQL> select * from test; F1
----------
1.2346E+11
1.2346E+11SQL> select to_char(f1) from test;TO_CHAR(F1)
----------------------------------------
123456790000
123456790000number(p,s)
p(precision)是指精度,即可以保存多少个数字
s(scale)是指刻度,不好说明,大概可以理解为数值的最小单位。
SQL> insert into test values(1234567890.00013);1 row created.SQL> select to_char(f1) from test;TO_CHAR(F1)
----------------------------------------
123456790000
123456790000
1234570000你用sqlplus试一下吧,我这边没有pl/sql developer
1234567901234567890123456789012345678);1 row created.SQL> select to_char(f1) from test;TO_CHAR(F1)
----------------------------------------
1.2345679012345678901234567890123457E-39
要多试验才能理解number(p,s)中p与s的关系
pl/sql developer的处理数值的时,可能要进行转化的操作。所以才会报错。另
是oracle的报错,还是pl/sql developer的报错?
1234567901234567890123456789012345678);
结果是0.000000000000000000。奇怪!
执行insert into test values (0.123); 报错
create table test2 (f1 number(3,5));
insert into test2 values (0.123);
对于0.123,最小单位是小数点后5,所以保存0.123时会转化成
0.12300
这没有超过3个有效数字啊。
p:1---38
s:-84---127s>0 && p>s 例number(8,4)精确到小数点右边s位,并四舍五入。然后检验有效数位是否<=p;s>0 && p<s 例number(4,8)小数点右边至少有s-p个0填充,精确到小数点右边s位;s<0 例number(4,-8)/number(8,-4)精确到小数点左边s位,并四舍五入,然后检验有效数位是否<=p+|s|。