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

Function | Summary |
---|---|

CEIL | Returns the smallest integer that is greater than or equal to the specified value. This integer is the “ceiling” over your value. |

FLOOR | Returns the largest integer that is less than or equal to the specified value. This integer is the “floor” under your value. |

ROUND | 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). |

TRUNC | 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 ...

Start Free Trial

No credit card required