Database Reference
In-Depth Information
9.1
Types of Functions
Oracle divides all functions into the following categories:
Single-Row Functions
. Functions that operate on a single row at a
time. This chapter examines this type of function. For example, the
UPPER() function converts characters to uppercase.
Grouping Functions
. Chapter 11 covers grouping functions in
detail.
Aggregate Functions
. Functions that operate on a group of rows
at one time and return a single row. For example, the COUNT()
function counts the number of rows in a table.
Analytical Functions
. Functions that operate on groups of rows
and return one or more summary rows. For example, the STD-
DEV() OVER() function returns the standard deviation rows
based on values in one or more columns.
Object Reference Functions
Functions that manipulate the value in
columns with the REF datatype in object tables. For example, the
DEREF() function returns the value of an attribute in the referenced
object table (see Chapter 16).
.
User-Defined Functions
. Functions that are built by you and per-
form whatever data manipulations you program them to do. Exam-
ples of user-defined functions are given throughout this topic, with
syntactical details in Chapter 24.
This chapter covers many of the dozens of single-row functions available
for your use in queries.
9.2
Single-Row Functions
Single-row functions add a great deal of power to queries. Use functions in
the SELECT clause to modify the appearance of dates, for example. Add
functions to the WHERE clause to help determine which rows to include in
query results. Place functions in the ORDER BY clause to fine-tune sorting.
