---

Function Declaration and Definition

A function is a subprogram that returns a value. The data type of the value is the data type of the function. A function invocation (or call) is an expression, whose data type is that of the function.

Before invoking a function, you must declare and define it. You can either declare it first (with function_declaration) and then define it later in the same block, subprogram, or package (with function_definition) or declare and define it at the same time (with function_definition).

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

Note:

This topic applies to nested functions. For information about standalone stored functions, see "CREATE FUNCTION Statement". For information about package functions, see "CREATE PACKAGE Statement".

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

function_declaration ::=

Description of the illustration function_declaration.gif

function_heading ::=

Description of the illustration function_heading.gif

See:

• "datatype ::="

• "parameter_declaration ::="

function_definition ::=

Description of the illustration function_definition.gif

See:

• "declare_section ::="

• "body ::="

• "call_spec ::="

relies_on_clause ::=

Description of the illustration relies_on_clause.gif

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

Tells the optimizer that the function returns the same result value whenever it is invoked with the same parameter values. If the function was invoked previously with the same parameter values, the optimizer can elect to use the previous result instead of invoking the function again.

Do not specify DETERMINISTIC for a function whose result depends on the state of session variables or schema objects, because results might vary across invocations. 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".

See Also:

• "Subprogram Side Effects"

• CREATE INDEX statement in Oracle Database SQL Language Reference

PIPELINED

Use only with a table function, to specify that it is pipelined. A pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. To return a row (but not control) to the invoker, the function uses the "PIPE ROW Statement".

See Also:

• "Overview of Table Functions"

• "Creating Pipelined Table Functions"

Note:

You cannot run a pipelined table function over a database link. The reason is that the return type of a pipelined table function is a SQL user-defined type, which can be used only in a single database (as explained in Oracle Database Object-Relational Developer's Guide). Although the return type of a pipelined table function might appear to be a PL/SQL type, the database actually converts that PL/SQL type to a corresponding SQL user-defined type.

PARALLEL_ENABLE

Makes the function safe for use in slave sessions of parallel DML evaluations.

RESULT_CACHE

Caches the results of the function. 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

The data type of the value that the function returns, which can be any PL/SQL data type (see Chapter 3, "PL/SQL Data Types"). The data type of the return value is the data type of the function.

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.

call_spec, EXTERNAL

See "call_spec" and "EXTERNAL".

Restriction on call_spec, EXTERNAL These clauses can appear only in a package specification or package body.

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.

Note:

• This clause is deprecated. As of Release 11.2, the database detects all data sources that are queried while a result-cached function is running, and relies_on_clause does nothing.

• You cannot use relies_on_clause in a function declared in an anonymous block.

Examples

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

Related Topics

In this chapter:

• "Formal Parameter Declaration"

• "PIPE ROW Statement"

• "Procedure Declaration and Definition"

In other chapters:

• Chapter 8, "PL/SQL Subprograms"

• "Creating Pipelined Table Functions"