Oracle database, numbers Force 2 decimal places to display

In the banking, financial and other sensitive to the digital requirements of the system, the digital display generally have strict requirements.
This is a requirement, title, which requires numbers to be displayed in two decimal places, and if there are no decimals, force the display to 0.
For example:
123.4 Display as 123.40
12 Display as 12.00
0 Display as 0.00

I thought this was a relatively simple question, and Oracle itself provided the TO_CHAR function, with the Format function, to satisfy the condition:

[SQL]View PlainCopy 

2. Select To_char (123.4, ' 9999990.00 ') as AA from dual;
4. Select To_char (n, ' 9999990.00 ') as AA from dual;
6. Select To_char (0, ' 9999990.00 ') as AA from dual;
10. Select To_char (123.4, ' fm9999990.00 ') as AA from dual;
12. Select To_char (n, ' fm9999990.00 ') as AA from dual;
14. Select To_char (0, ' fm9999990.00 ') as AA from dual;

At this point, I thought the problem was solved, but did not notice that the above statement is formatted with a string, not a number!!
requirements, very clear requirements, the number is formatted, the results are still numbers.


Analysis: This requirement is a very common and normal requirement, and since Oracle is so powerful, it should provide relevant methods,
So, to find the relevant documentation for Oracle, finally, find a cast function, which is responsible for the type conversion, try it.
The results show that the method is feasible. Test the SQL statement as follows:

[SQL]View PlainCopy  

1. Select CAST (1234.4 as number (2)) as AA from dual;
2. Select CAST ( as number (2)) as AA from dual;
3. Select CAST (0 as number (2)) as AA from dual;

PS: Append a point, the string can be directly type conversion, without using the To_number () function to do intermediate conversion. The SQL statements are as follows:

[SQL]View PlainCopy 

1. select cast ( ' 1234.4 '  as number  (10, 2)  )   as aa from dual ;  
2. select cast ( ' 12 '  as number  (10, 2)  )  as aa  from dual ;  
Li class= "alt" > select cast ( ' 0 '  as number  (10, 2)  )  as aa  from dual ;  

PS: Add 2nd, online to see if it is said that after the decimal point is full, PL/SQL version is concerned.
I did not do the verification, do not express personal opinion, only in this record, such as later encountered problems, and then verify.

Oracle database, numbers Force 2 decimal places to display