PL/SQL implements several functions that are useful when working with numbers. You’ve already seen the conversion functions TO_CHAR, TO_NUMBER, TO_BINARY_FLOAT, and TO_BINARY_DOUBLE. The next few subsections briefly describe several other useful functions. For full details on a particular function, consult Oracle’s SQL Reference manual.
There are four different numeric functions that perform rounding and truncation actions: CEIL, FLOOR, ROUND, and TRUNC. It is easy to get confused about which to use in a particular situation. Table 9-7 compares these functions, and Figure 9-5 illustrates their use for different values and decimal place rounding.
Table 9-7. Comparison of functions that perform rounding and truncation actions
Returns the smallest integer that is greater than or equal to the specified value. This integer is the “ceiling” over your value.
Returns the largest integer that is less than or equal to the specified value. This integer is the “floor” under your value.
Performs rounding on a number. You can round with a positive number of decimal places (the number of digits to the right of the decimal point) and also with a negative number of decimal places (the number of digits to the left of the decimal point).
Truncates a number to the specified number of decimal places. TRUNC simply discards all values beyond the decimal places provided in the call.
Figure 9-5. Impact of rounding and truncation ...