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
- Select To_char (123.4, ' 9999990.00 ') as AA from dual;
- Select To_char (n, ' 9999990.00 ') as AA from dual;
- Select To_char (0, ' 9999990.00 ') as AA from dual;
- Select To_char (123.4, ' fm9999990.00 ') as AA from dual;
- Select To_char (n, ' fm9999990.00 ') as AA from dual;
- 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
- Select CAST (1234.4 as number (2)) as AA from dual;
- Select CAST ( as number (2)) as AA from dual;
- 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
- select cast ( ' 1234.4 ' as number (10, 2) ) as aa from dual ;
- 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