Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:
function(argument, argument, ...)
This chapter contains these sections:
SQL Functions
SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL.
If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle implicitly converts the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT
, NVL
, and REPLACE
.
In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.
|
The syntax showing the categories of functions follows:
function::=
{ single_row_function
| aggregate_function
| analytic_function
| object_reference_function
| user_defined_function
}
single_row_function::=
{ number_function
| character_function
| datetime_function
| conversion_function
| miscellaneous_single_row_function
}
Single-Row Functions
Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE
clauses, START
WITH
and CONNECT
BY
clauses, and HAVING
clauses.
Number Functions
Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS
, COSH
, EXP
, LN
, LOG
, SIN
, SINH
, SQRT
, TAN
, and TANH
are accurate to 36 decimal digits. The transcendental functions ACOS
, ASIN
, ATAN
, and ATAN2
are accurate to 30 decimal digits. The number functions are:
Character Functions Returning Character Values
Character functions that return character values return values of the same datatype as the input argument.
- Functions that return
CHAR
values are limited in length to 2000 bytes. - Functions that return
VARCHAR2
values are limited in length to 4000 bytes.
For both of these types of functions, if the length of the return value exceeds the limit, then Oracle truncates it and returns the result without an error message.
For CLOB
functions, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.
The character functions that return character values are:
Character Functions Returning Number Values
Character functions that return number values can take as their argument any character datatype.
The character functions that return number values are:
Datetime Functions
Datetime functions operate on values of the DATE
datatype. All datetime functions return a datetime or interval value of DATE
datatype, except the MONTHS_BETWEEN
function, which returns a number. The datetime functions are:
Conversion Functions
Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype
TO
datatype
. The first datatype is the input datatype. The second datatype is the output datatype. The SQL conversion functions are:
Miscellaneous Single-Row Functions
The following single-row functions do not fall into any of the other single-row function categories:
Aggregate Functions
Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER
BY
and HAVING
clauses. They are commonly used with the GROUP
BY
clause in a SELECT
statement, where Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP
BY
clause, the elements of the select list can be aggregate functions, GROUP
BY
expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP
BY
clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING
clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
"Using the GROUP BY Clause: Examples" and the "HAVING Clause" for more information on the |
Many (but not all) aggregate functions that take a single argument accept these clauses:
DISTINCT
causes an aggregate function to consider only distinct values of the argument expression.ALL
causes an aggregate function to consider all values, including all duplicates.
For example, the DISTINCT
average of 1, 1, 1, and 3 is 2. The ALL
average is 1.5. If you specify neither, then the default is ALL
.
All aggregate functions except COUNT
(*) and GROUPING
ignore nulls. You can use the NVL
function in the argument to an aggregate function to substitute a value for a null. COUNT
never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr
:
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;
AVG(MAX(SALARY))
----------------
10925
This calculation evaluates the inner aggregate (MAX
(salary
)) for each group defined by the GROUP
BY
clause (department_id
), and aggregates the results again.
REGR_ (Linear Regression) Functions
Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER
BY
clause. All joins and all WHERE
, GROUP
BY
, and HAVING
clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER
BY
clause.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.
Text description of analytic_function
analytic_clause::=
Text description of analytic_clause
Text description of query_partition_clause
Text description of order_by_clause
Text description of windowing_clause
The semantics of this syntax are discussed in the sections that follow.
analytic_function
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).
arguments
Analytic functions take 0 to 3 arguments.
analytic_clause
Use OVER
analytic_clause
to indicate that the function operates on a query result set. That is, it is computed after the FROM
, WHERE
, GROUP
BY
, and HAVING
clauses. You can specify analytic functions with this clause in the select list or ORDER
BY
clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
|
query_partition_clause
Use the PARTITION
BY
clause to partition the query result set into groups based on one or more value_expr
. If you omit this clause, then the function treats all rows of the query result set as a single group.
You can specify multiple analytic functions in the same query, each with the same or different PARTITION
BY
keys.
If the objects being queried have the parallel attribute, and if you specify an analytic function with the
|
Valid values of value_expr
are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
order_by_clause
Use the order_by_clause
to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT
and PERCENTILE_DISC
(which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr
and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the
|
Restriction on the ORDER BY Clause
When used in an analytic function, the order_by_clause
must take an expression (expr
). The SIBLINGS
keyword is not valid (it is relevant only in hierarchical queries). Position (position
) and column aliases (c_alias
) are invalid. Otherwise this order_by_clause
is the same as that used to order the overall query or subquery.
ASC | DESC
Specify the ordering sequence (ascending or descending). ASC
is the default.
NULLS FIRST | NULLS LAST
Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS
LAST
is the default for ascending order, and NULLS
FIRST
is the default for descending order.
|
windowing_clause
Some analytic functions allow the windowing_clause
. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause
are followed by an asterisk (*).
ROWS | RANGE
These keywords define for each row a "window" (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window "slides" through the query result set or partition from top to bottom.
You cannot specify this clause unless you have specified the order_by_clause
.
BETWEEN ... AND
Use the BETWEEN
... AND
clause to specify a start point and end point for the window. The first expression (before AND
) defines the start point and the second expression (after AND
) defines the end point.
If you omit BETWEEN
and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.
UNBOUNDED PRECEDING
Specify UNBOUNDED
PRECEDING
to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
UNBOUNDED FOLLOWING
Specify UNBOUNDED
FOLLOWING
to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.
CURRENT ROW
As a start point, CURRENT
ROW
specifies that the window begins at the current row or value (depending on whether you have specified ROW
or RANGE
, respectively). In this case the end point cannot be value_expr
PRECEDING
.
As an end point, CURRENT
ROW
specifies that the window ends at the current row or value (depending on whether you have specified ROW
or RANGE
, respectively). In this case the start point cannot be value_expr
FOLLOWING
.
value_expr PRECEDING or value_expr FOLLOWING
- If
value_expr
FOLLOWING
is the start point, then the end point must bevalue_expr
FOLLOWING
. - If
value_expr
PRECEDING
is the end point, then the start point must bevalue_expr
PRECEDING
.
If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.
NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals |
value_expr
is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.- If
value_expr
is part of the start point, then it must evaluate to a row before the end point.
value_expr
is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal.
"Literals" for information on interval literals |
- You can specify only one expression in the
order_by_clause
- If
value_expr
evaluates to a numeric value, then theORDER
BY
expr
must be aNUMBER
orDATE
datatype. - If
value_expr
evaluates to an interval value, then theORDER
BY
expr
must be aDATE
datatype.
If you omit the windowing_clause
entirely, then the default is RANGE
BETWEEN
UNBOUNDED
PRECEDING
AND
CURRENT
ROW
.
Analytic functions are commonly used in data warehousing environments. The analytic functions follow. Functions followed by an asterisk (*) allow the full syntax, including the windowing_clause
.
AVG *
CORR *
COUNT *
MAX *
MIN *
REGR_ (Linear Regression) Functions *
STDDEV *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
Oracle9i Data Warehousing Guide for more information on these functions, and for scenarios illustrating their use |
Object Reference Functions
Object reference functions manipulate REFs, which are references to objects of specified object types. The object reference functions are:
Oracle9i Database Concepts and Oracle9i Application Developer's Guide - Fundamentals for more information about REFs |
User-Defined Functions
You can write user-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL built-in functions. User-defined functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.
For example, user-defined functions can be used in the following:
- The select list of a
SELECT
statement - The condition of a
WHERE
clause CONNECT
BY
,START
WITH
,ORDER
BY
, andGROUP
BY
clauses- The
VALUES
clause of anINSERT
statement - The
SET
clause of anUPDATE
statement
user_defined_function::=
Text description of user_defined_function
The optional expression list must match attributes of the function, package, or operator.
Restriction on User-defined Functions
The DISTINCT
and ALL
keywords are valid only with a user-defined aggregate function.
|
Prerequisites
User-defined functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement.
To use a user function in a SQL expression, you must own or have EXECUTE
privilege on the user function. To query a view defined with a user function, you must have SELECT
privileges on the view. No separate EXECUTE
privileges are needed to select from the view.
|
Name Precedence
Within a SQL statement, the names of database columns take precedence over the names of functions with no parameters. For example, if the Human Resources manager creates the following two objects in the hr
schema:
CREATE TABLE new_emps (new_sal NUMBER, ...);
CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;
then in the following two statements, the reference to new_sal
refers to the column new_emps.new_sal
:
SELECT new_sal FROM new_emps;
SELECT new_emps.new_sal FROM new_emps;
To access the function new_sal
, you would enter:
SELECT hr.new_sal FROM new_emps;
Here are some sample calls to user functions that are allowed in SQL expressions:
circle_area (radius)
payroll.tax_rate (empno)
hr.employees.tax_rate (dependent, empno)@remote
Example
To call the tax_rate
user function from schema hr
, execute it against the ss_no
and sal
columns in tax_table
, and place the results in the variable income_tax
, specify the following:
SELECT hr.tax_rate (ss_no, sal)
INTO income_tax
FROM tax_table
WHERE ss_no = tax_id;
Naming Conventions
If only one of the optional schema or package names is given, then the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL
in the reference PAYROLL
.TAX_RATE
is a schema or package name, Oracle proceeds as follows:
- If a
PAYROLL
package is not found, then look for a schema namePAYROLL
that contains a top-levelTAX_RATE
function. If no such function is found, then return an error.
- If the
PAYROLL
package is found in the current schema, then look for aTAX_RATE
function in thePAYROLL
package. If no such function is found, then return an error.
You can also refer to a stored top-level function using any synonym that you have defined for it.