Oracle Database Employ Functions: String, Numeric, Aggregate (No Grouping)

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

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

Numeric Functions

Table 2 illustrates some common numeric functions, their syntax, and the results they produce. These are only a few of the many functions available.

Aggregate Functions

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.