Oracle數(shù)據(jù)庫中保留小數(shù)點后兩位的問題解讀

更新時間：2024年09月20日 10:19:59 作者：無間行者

在Oracle數(shù)據(jù)庫中,對數(shù)字和百分比進行格式化,以保留兩位小數(shù),主要使用to_char()函數(shù),對于大數(shù)字如10000000.12,使用to_char(字段名, 'FM99999999999990.00')可確保保留兩位小數(shù)而無額外空格,對于百分比如86.63%

Oracle數(shù)據(jù)庫中保留小數(shù)點后兩位的問題

1.數(shù)字保留兩位小數(shù)

（小數(shù)點左側數(shù)字可能比較大，比如10000000.12）

to_char() 函數(shù)轉換

答案

select trim(to_char(85.7323232, '9999999990.00')) as 兩位小數(shù) from dual;

寫法對比

select result,
       to_char(result, '9999.99') as "位數(shù)不足",
       to_char(result, '9999999999.99') as "位數(shù)充足",
       to_char(result, '0000.00') as "0的位數(shù)不足",
       to_char(result, '0000000000.00') as "0的位數(shù)充足",
       to_char(result, '9999999990.00') as "9的位數(shù)充足",
       trim(to_char(result, '9999999990.00')) as "9的位數(shù)充足且去掉左側"
from (
    select 0 result from dual union
    select 1 result from dual 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.百分比保留兩位小數(shù)

（小數(shù)點左側數(shù)字數(shù)字小于等于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位數(shù)不足",
       to_char(result * 100, '00.99') || '%' as "0位數(shù)不足",
       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
)