Oracle retains two-bit decimal solution _oracle

Companies need to deal with a number of reports, the need to use the percentage, to retain 2 decimal places, only with round and TRUNC functions can be implemented (round (_data,2), but the format is not very neat, the format is not strictly required under the circumstances of the use of round can be.

Personally think it is more convenient for a
Select Decode (n_jg,0, ' 0.00 ', Trim (To_char (N_JG, ' 9999999.99 ')) from TBL
If you are only retrieving, use:
1, select Trunc (cur_sum,2) from Data_record;
Convert decimal to Percent => round (zcbj/zs*100) | | % ' ==trunc ((Zcbj/zs), 2) *100| | ' %'
2, if you want to update the data, you can use:
Update Data_record set Cur_sum=trunc (cur_sum,2) where rec_no=123

method One: Use the FM format of To_char
To_char (Round (data.amount,2), ' FM9999999999999999.00 ') as Amount
The disadvantage is that if the value is 0, it will appear as. 00 instead of 0.00.
Another note is that the number of decimal points on the left 9 of the format is enough, otherwise the number of queries will appear as n symbol "#".
The solution is as follows:
Select Decode (salary,0, ' 0.00 ', (To_char (Round (salary,2), ' fm99999999999999.00 ')) from Can_do;

method Two: Use case as then else end to do all kinds of judgment processing
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 Three: You can use the parameters set up with Oracle
Column Amount Format l9999999999.99
The disadvantage of this method is that the number of 9 on the left of the decimal point in format is known, or more than the number appears as ####### #的情况.
The other question is whether the setting takes effect at the session or system level when using column, and requires attention.
It is possible that a table's numeric column does not always require all places to be displayed, but is the second-decimal-digit format, where only the session level is used, but there is a problem with database connection sessions timeout, which is not recommended if you are not using the system level.

Method IV: Using the To_char+trim method
Select Trim (to_char (1234, ' 99999999999999.99 ')) from dual;
or
Select LTrim (Trim (To_char (1234.525, ' 00000000000000.00 ')), ' 0 ' from dual;
There are 14 9 or 14 0 formats used here, and it is recommended to use 14 9 to make it easier. The disadvantages of method four are:
If the value is 0, after conversion to. 00 instead of 0.00, the remedial measure is to decode.
Another note is that the number of decimal points on the left 9 or 0 is enough, the number responsible for the query will be displayed as n symbol "#".
As follows:
Select Decode (salary,0, ' 0.00 ', Trim (to_char (Salary, ' 99999999999999.99 ')) from Can_do;
or
Select Decode (salary,0, ' 0.00 ', LTrim (Trim (to_char (Salary, ' 00000000000000.00 ')), ' 0 ') from Can_do;
Conclusions: It is recommended to use the method of Trim+to_char or method of a remedy after the way, and it is best to use the left of the decimal point of N 9 way, do not use 0 of the way, otherwise, to more than one step trim processing.
namely: Select Decode (salary,0, ' 0.00 ', Trim (to_char (Salary, ' 99999999999999.99 ')) from Can_do;
or
Select Decode (salary,0, ' 0.00 ', (To_char (Round (salary,2), ' fm99999999999999.00 ')) from Can_do;