Question

oracle如何判断一个数是不是整数呢？比如3.000和3.100，如何筛选出来3.100

Answer 1

select * from table where int-floor(int)>0 --int就是你的表字段，floor(int)就是取整数，等同于3.100-3>0

Answer 2

select decode(instr('1', '.') + sign('1'), 1, 1, 0) from dual;
返回1是整数，否则不是

create or replace function f_isnumeric(cnt varchar2) return number asres number; flag number; beginif cnt is
null thenreturn 0;
end if; select to_number(cnt) into res from dual; select decode(instr(res, '.') + sign(res), 1, 1, 0) into flag from dual; if flag = 1 thenreturn 1; else return 0;
end if;
exception
when others then return 0;
end;

Answer 3

SELECT C_NUM,
CASE
WHEN C_NUM - TRUNC(C_NUM) = 0 THEN
'整数'
ELSE
'非整数'
END
FROM (SELECT 3.000 C_NUM
FROM DUAL
UNION ALL
SELECT 3.100
FROM DUAL
UNION ALL
SELECT 4.400
FROM DUAL
UNION ALL
SELECT 5.400
FROM DUAL
UNION ALL
SELECT 5.000 FROM DUAL)

Answer 4

trunc(3.1)=3.1相等是正数，否则是小数。