Oracle数据库中保留小数点后两位的问题解读

目录

• oracle数据库中保留小数点后两位的问题
• 1.数字保留两位小数
• 2.百分比保留两位小数
• 总结

Oracle数据库中保留小数点后两位的问题

1.数字编程保留两位小数

（小数点左侧数字可能比较大，比如10000000.12）

to_char() 函数转换

答案

select trim(to_char(85.7323232, '9999999990.00')) as 两位小数 from dual;

写法对比

select result,
       to_char(result, '9999.99') as "位数不足",
       to_char(result, '9999999999.99') as "位数充足",
       to_char(result, '0000.00') as "0的位数不足",
       to_char(result, '0000000000.00') as "0的位数充足",
       to_char(rphpesult, '9999999990.00') as "9的位数充足",
       trim(to_char(result, '9999999990.00')编程客栈) as "9的位数充足且去掉左侧"
from (
    select 0 result from dual union
    select 1 result from dnAWIkual union
    select 123 result from dual union
    select 123.4 result from dual union
    select 12345.67 result from dual union
    select 12345678.2 result from dual
)

2.百分比保留两位小数

（小数点左侧数字数字小于等于100，比如86.63%）

答案

select trim(to_char(0.7323232 * 100, '99990.99'))||'%' as 百分比 from dual;

select trim(to_char(0.7323232 * 100, '99990.99'))||'%' as 百分比 from dual;

写法对比

select result,
       to_char(result * 100, '99.99') || '%' as "9位数不足",
       to_char(result * 100, '00.99') || '%' as "0位数不足",nAWIk
       to_char(result * 100, '99990.99') || '%' as "百分比",
       trim(to_char(result * 100, '99990.99')) || '%' as "去左侧空格的百分比",
       to_char(result * 100, 'fm99990.99') || '%' as "使用fm前缀的格式百分比",
       to_char(result * 100, 'fm99990.00') || '%' as "使用fm前缀的格式百分比2"
from (
    select 0 result from dual union
    select 1 result from dual union
    select 0.12345 result from dual union
    select 0.2 result from dual union
    select 0.23 result from dual union
    select 0.234 result from dual union
    select 0.2345 result from dual union
    select 0.23454 result from dual union
    select 0.23456 result from dual
)

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。