Function

A function is a subprogram that returns a single value. You must declare and define a function before invoking it. You can either declare and define it at the same time, or you can declare it first and then define it later in the same block, subprogram, or package. A function invocation, or function call, is an expression.

A function declaration is also called a function specification, or function spec.

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

function_declaration ::=

function_heading ::=

See:

• datatype ::=

• parameter_declaration ::=

function_definition ::=

See:

• body ::=

• declare_section ::=

relies_on_clause ::=

Semantics

function_declaration

Declares a function, but does not define it. The definition must appear later in the same block, subprogram, or package as the declaration.

DETERMINISTIC

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is invoked with the same values for its parameters. This helps the optimizer avoid redundant function calls: If a stored function was invoked previously with the same arguments, the optimizer can elect to use the previous result.

Do not specify DETERMINISTIC for a function whose result depends on the state of session variables or schema objects, because results might vary across calls. Instead, consider making the function result-cached (see "Making Result-Cached Functions Handle Session-Specific Settings" and "Making Result-Cached Functions Handle Session-Specific Application Contexts").

Only DETERMINISTIC functions can be invoked from a function-based index or a materialized view that has query-rewrite enabled. For more information and possible limitations of the DETERMINISTIC option, see "CREATE FUNCTION Statement".

PIPELINED

PIPELINED specifies to return the results of a table function iteratively. A table function returns a collection type (a nested table or varray) with elements that are SQL data types. You can query table functions using the TABLE keyword before the function name in the FROM clause of a SQL query. For more information, see "Performing Multiple Transformations with Pipelined Table Functions".

PARALLEL_ENABLE

Enables the function to be used safely in slave sessions of parallel DML evaluations.

RESULT_CACHE

Causes the results of the function to be cached. For more information, see "PL/SQL Function Result Cache".

function_heading

function_name

The name that you give to the function that you are declaring or defining.

RETURN datatype

For datatype, specify the data type of the return value of the function. The return value can have any data type supported by PL/SQL.

Restriction on datatype You cannot constrain this data type (with NOT NULL, for example).

function_definition

Either defines a function that was declared earlier or both declares and defines a function.

declare_section

The optional declarative part of the function. Declarations are local to the function, can be referenced in body, and cease to exist when the function completes execution.

body

The required executable part of the function and, optionally, the exception-handling part of the function.

At least one execution path must lead to a RETURN statement in the executable part of the function; otherwise, a run-time error occurs.

relies_on_clause

Specifies the data sources on which the results of the function depend. Each data_source is the name of either a database table or view.

Examples

• Example 8-2, "Declaring, Defining, and Invoking a Simple PL/SQL Function"

• Example 5-44, "Returning a Record from a Function"

Related Topics

In this chapter:

• "Parameter"

• "Procedure"

In other chapters:

• Chapter 8, "PL/SQL Subprograms"