可通过添加check约束来满足你的需求 SQL> create table test(id number check(instr(to_char(id),'.') = 0));----------------------------- -------测试如下------------ ----------------------------- SQL> insert into test values(5.1); insert into test values(5.1) * ERROR at line 1: ORA-02290: check constraint (XIONGWENHUA.SYS_C006977) violated SQL> select * from test; ID ---------- 5SQL>
number(p,s):表示可容纳的数字位数为p位,包括整数部分和小数部分,但不包括小数点。整数部分只能容纳(p-s)位。
SQL> create table testnum(num1 number(5), num2 number(5,2));表已创建。SQL> insert into testnum(num1, num2) values(123.1, 123.12);已创建 1 行。SQL> insert into testnum(num1, num2) values(12312, 123.12);已创建 1 行。SQL> insert into testnum(num1, num2) values(123121, 123.12);
insert into testnum(num1, num2) values(123121, 123.12)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into testnum(num1, num2) values(12312, 123.121);已创建 1 行。SQL> insert into testnum(num1, num2) values(12312, 123.1211);已创建 1 行。SQL> insert into testnum(num1, num2) values(12312, 1213.1211);
insert into testnum(num1, num2) values(12312, 1213.1211)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into testnum(num1, num2) values(12312.1, 1213.1211);
insert into testnum(num1, num2) values(12312.1, 1213.1211)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度好吧,我也纳闷了。Oracle在这方面应该有一定的机制。
number(5) 只保证它存储的是整数,你输入小数自动四舍五入。
SQL> create table test(id number check(instr(to_char(id),'.') = 0));-----------------------------
-------测试如下------------
-----------------------------
SQL> insert into test values(5.1);
insert into test values(5.1)
*
ERROR at line 1:
ORA-02290: check constraint (XIONGWENHUA.SYS_C006977) violated
SQL> select * from test; ID
----------
5SQL>