Oracle provides us with many functions that allow us to analyze and aggregate the data, returning results that differ greatly from the result sets you’ve seen so far. A function manipulates the contents of a column in a SQL statement. You can find what the largest credit limit is in the CUSTOMERS table, and you can round numbers or pad results with characters. In fact, when you ran a count of customers that were in the province of Delhi before and after deleting these records, you took a sneak peek ahead at functions.
This article introduces you to three different types of functions: string (or character), numeric, and aggregate.
String functions, also known as character functions, can be categorized into two types: those that return character values and those that return numeric values.
Table 1 represents the most common functions you will perform with the character data type; it’s only a partial list. The examples that follow all use the dual table, which is an internal Oracle table that is useful in SQL and PL/SQL for performing functions that return a single row. It can be used to return the current system date and time, to perform arithmetic functions, or to obtain a generated sequential number (more on this later in the chapter).
Table 2 illustrates some common numeric functions, their syntax, and the results they produce. These are only a few of the many functions available.
Unlike the character or numeric functions, which act on a single row, aggregate functions act on an entire column of data. Aggregate functions save the developer from having to write a lot of code to determine the maximum column value in a set of records or an average, for example. A single result row is returned by aggregate functions based on the group of rows. Table 3-5 illustrates the more commonly used aggregate functions but is only a partial list. As simple as these are, we’re sure you’ll agree that they are indeed quite powerful.