Oracle decimal point retention

Oracle decimal point retention problem if you need to use Oracle decimal point retention problem in actual application, in fact, the method is very simple, we only use round to implement (round (_ data, 2 )), however, if the format is not neat, you can use the round. The following is the solution to the search on the Network: www.2cto.com Method 1: Use the fm format of to_char, that is: 1. to_char (round (data. amount, 2), 'fm9999999999999999. 00 ') the disadvantage of as amount is that if the value is 0, it is displayed. 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 "#". Solution: www.2cto.com 1. select decode (salary, 0, '0. 00', (to_char (round (salary, 2), 'fm99999999999999. 00 ') from can_do; Method 2: Use case when then else end to judge and handle various situations: 1. case 2. when instr (to_char (data. amount ),'. ') <1 then 3. data. amount | '. 00 '4. when instr (to_char (data. amount ),'. ') + 1 = length (data. amount) then 5. data. amount | '0' 6. else 7. to_char (round (data. amount, 2) 8.end as amount_format method 3: You can use Oracle parameter settings, that is, 1. column amount format l9999999999.99 this method is insufficient because the number of 9 on the left of the decimal point in the format must be known; otherwise, the number exceeding the number is displayed. Another Oracle decimal point retention problem is that when column is used, the setting takes effect at the session level or system level. Pay attention to this. Maybe the numeric column of a table does not always require that the data is displayed in the format of two decimal places. At this time, only session-level data can be used, however, an Oracle decimal point reservation problem occurs when the database connection session times out. If the system level is not used, this method is not recommended. Method 4: Use to_char + trim as follows: 1. select trim (to_char (1234, '123. 99 ') from dual; or www.2cto.com 1. select ltrim (trim (to_char (1234.525, '2017. 00 '), '0') from dual; 14 9 or 14 0 formats are used here. It is recommended to use 14 9 formats for convenience. The disadvantage of Method 4 is that 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 "#". 1. select decode (salary, 0, '0. 00', trim (to_char (salary, '2017. 99 ') from can_do; or 1. select decode (salary, 0, '0. 00', ltrim (trim (to_char (salary, '2017. 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, in addition, it is best to use n 9 decimal places on the left. Do not use 0. Otherwise, trim processing is required. That is: 1. select decode (salary, 0, '0. 00', trim (to_char (salary, '2017. 99 ') from can_do; or 1. select decode (salary, 0, '0. 00', (to_char (round (salary, 2), 'fm99999999999999. 00 ') from can_do; The above content is an introduction to the Oracle decimal point retention problem. I hope you will get something.