Oracle retain two decimal places Solution

The company needs to process some reports and use percentages to keep 2 decimal places. Only the round and trunc functions can be used to implement (round (_ data, 2), but the format is not neat, you can use round if the format is not rigorous.

I personally think it is more convenient
Select decode (n_jg, 0, '0. 00', trim (to_char (n_jg, '192. 99') from tbl
If it is only for retrieval, use:
1. select trunc (CUR_SUM, 2) from data_record;
Convert decimals into percentages => round (zcbj/zs * 100) | '%' = trunc (zcbj/zs), 2) * 100 | '%'
2. to update data, you can use:
Update data_record set CUR_SUM = trunc (CUR_SUM, 2) where REC_NO = 123

Method 1: Use the fm format of to_char
To_char (round (data. amount, 2), 'fm999999999999999999. 00') as amount
The disadvantage is that if the value is 0, it is displayed as. 00 instead of 0.00.
Note that the number of digits on the left of the decimal point must be enough in the format. Otherwise, the number to be queried is displayed as n symbols "#".
The solution is as follows:
Select decode (salary, 0, '0. 00', (to_char (round (salary, 2), 'fm99999999999999. 00') from can_do;

Method 2: Use case when then else end for various situations
Case
When instr (to_char (data. amount), '.') <1 then
Data. amount | '. 00'
When instr (to_char (data. amount), '.') + 1 = length (data. amount) then
Data. amount | '0'
Else
To_char (round (data. amount, 2 ))
End as amount_format

Method 3: Use the parameter settings provided by Oracle.
Column amount format l9999999999.99
The disadvantage of this method is that the number of 9 decimal points on the left of the format must be known; otherwise, the number exceeding the number is displayed.
Another problem is whether the setting takes effect at the session or system level when column is used.
Maybe the numeric column of a table does not always require that the data is displayed in the format of two digits after the decimal point. At this time, only session-level data can be used, but there is a problem of database connection session Timeout, we do not recommend that you use this method if it is not at the system level.

Method 4: Use to_char + trim
Select trim (to_char (1234, '192. 99') from dual;
Or
Select ltrim (trim (to_char (1234.525, '2014. 00'), '0') from dual;
14 9 or 14 0 formats are used here. We recommend that you use 14 9 for convenience. The disadvantage of Method 4 is:
If the value is 0, it is converted to. 00 instead of 0.00. The remedy is to decode it.
Note that the number of digits to the left of the decimal point is enough in the format, and the number to be queried is displayed as n symbols "#".
As follows:
Select decode (salary, 0, '0. 00', trim (to_char (salary, '192. 99') from can_do;
Or
Select decode (salary, 0, '0. 00', ltrim (trim (to_char (salary, '192. 00'), '0') from can_do;
Conclusion: We recommend that you use the trim + to_char method in Method 4 or the remedy method after method 1. It is also recommended that you use the n-9 decimal place instead of the 0 decimal place. Otherwise, trim processing is required.
That is: select decode (salary, 0, '0. 00', trim (to_char (salary, '192. 99') from can_do;
Or
Select decode (salary, 0, '0. 00', (to_char (round (salary, 2), 'fm99999999999999. 00') from can_do;