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


1.数字保留两位小数

（小数点左侧数字可能比较大，比如10000000.12）
to_char() 函数转换
答案

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

复制代码

写法对比

1. select result,
   
2.        to_char(result, '9999.99') as "位数不足",
   
3.        to_char(result, '9999999999.99') as "位数充足",
   
4.        to_char(result, '0000.00') as "0的位数不足",
   
5.        to_char(result, '0000000000.00') as "0的位数充足",
   
6.        to_char(result, '9999999990.00') as "9的位数充足",
   
7.        trim(to_char(result, '9999999990.00')) as "9的位数充足且去掉左侧"
   
8. from (
   
9.     select 0 result from dual union
   
10.     select 1 result from dual union
    
11.     select 123 result from dual union
    
12.     select 123.4 result from dual union
    
13.     select 12345.67 result from dual union
    
14.     select 12345678.2 result from dual
    
15. )

复制代码

2.百分比保留两位小数

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

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

复制代码

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

复制代码

写法对比

1. select result,
   
2.        to_char(result * 100, '99.99') || '%' as "9位数不足",
   
3.        to_char(result * 100, '00.99') || '%' as "0位数不足",
   
4.        to_char(result * 100, '99990.99') || '%' as "百分比",
   
5.        trim(to_char(result * 100, '99990.99')) || '%' as "去左侧空格的百分比",
   
6.        to_char(result * 100, 'fm99990.99') || '%' as "使用fm前缀的格式百分比",
   
7.        to_char(result * 100, 'fm99990.00') || '%' as "使用fm前缀的格式百分比2"
   
8. from (
   
9.     select 0 result from dual union
   
10.     select 1 result from dual union
    
11.     select 0.12345 result from dual union
    
12.     select 0.2 result from dual union
    
13.     select 0.23 result from dual union
    
14.     select 0.234 result from dual union
    
15.     select 0.2345 result from dual union
    
16.     select 0.23454 result from dual union
    
17.     select 0.23456 result from dual
    
18. )

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源：https://www.jb51.net/database/327660px2.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！