Most single-value functions in Oracle are pretty straightforward. This article gives short examples of the major functions, and it shows both the results of the functions and how they correspond to columns, rows, and lists. After the examples, you’ll learn how to combine these functions.
A table named MATH was created to show the calculation effects of the many math functions. It has only four rows and four columns, as shown here:
This table is useful because it has values with a variety of characteristics, which are spelled out by the names of the rows. WHOLE NUMBER contains no decimal parts. LOW DECIMAL has decimals that are less than 0.5, MID DECIMAL has decimals equal to 0.5, and HIGH DECIMAL has decimals greater than 0.5. This range is particularly important when using the ROUND (rounding) and TRUNC (truncate) functions and in understanding how they affect the value of a number.
To the right of the NAME column are three other columns: ABOVE, which contains only numbers above, or greater than, zero (positive numbers); BELOW, which contains only numbers below, or less than, zero; and EMPTY, which is NULL.
NOTE
In Oracle, a number column may have no value in it at all. When the column’s value is NULL, it is not zero; it is simply empty. This has important implications in making computations.
Not all the rows in this MATH table are needed to demonstrate how most math functions work, so the examples primarily use the last row, HIGH DECIMAL. In addition, the SQL*Plus column command has been used to show explicitly the precision of the calculation so the results of functions that affect a number’s precision can be clearly seen.
Leave a Reply