Oracle in a Nutshell

Chapter 8. Functions

Oracle provides a large number of built-in functions that you can use in SQL or in PL/SQL stored procedures. These functions extend the capabilities of the SQL language in order to provide commonly used capabilities. For example, in the following SQL statement you call the built-in AVG function, which returns the average value of the salary column for each department:

SELECT dept, AVG (salary) FROM emp GROUP BY dept

You can also create your own functions, which can be used in the same manner as Oracle’s built-in functions. Chapter 9 includes information about how you can create and call functions with the PL/SQL language.

This chapter provides brief descriptions of all of the Oracle built-in functions. We’ve divided the functions into the following categories; within each category, the functions are listed alphabetically:

Aggregate and analytic functions

Numeric functions

Character functions

Date and time functions

Conversion functions

Object functions

XML functions

Miscellaneous functions

Each function description contains a syntax block showing the parameters that may be passed to a function. In most cases, we describe each parameter. When the parameters have already been described in the “Common Keywords and Clauses” section, we simply provide a reference. In some cases, the parameters are obvious (e.g., an expression), so we won’t provide the details.