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.

LNPLS1578Topics

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

LNPLS1180function_declaration ::=

LNPLS1181function_heading ::=

See:

• "datatype ::="

• "parameter_declaration ::="

LNPLS1182function_definition ::=

See:

• "body ::="

• "call_spec ::="

• "declare_section ::="

• "call_spec"

LNPLS1183relies_on_clause ::=

Semantics

LNPLS1579function_declaration

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

LNPLS1580DETERMINISTIC

Tells the optimizer that the function returns the same value whenever it is invoked with the same parameter values (if this is not true, then specifying DETERMINISTIC causes unpredictable results). If the function was invoked previously with the same parameter values, the optimizer can 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".

LNPLS1581Restriction on DETERMINISTIC You cannot specify DETERMINISTIC for a nested function.

LNPLS1582PIPELINED

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".

LNPLS1583Restriction on PIPELINED You cannot specify PIPELINED for a nested function.

LNPLS1584PARALLEL_ENABLE

Enables the function for parallel execution, making it safe for use in slave sessions of parallel DML evaluations.

LNPLS1585Restriction on PARALLEL_ENABLE You cannot specify PARALLEL_ENABLE for a nested function.

LNPLS1586RESULT_CACHE

Caches the results of the function. For more information, see "PL/SQL Function Result Cache".

LNPLS1587Restriction on RESULT_CACHE You cannot specify RESULT_CACHE for a nested function.

LNPLS1588function_heading

LNPLS1589function

Name of the function that you are declaring or defining.

LNPLS1590RETURN datatype

Specifies 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").

LNPLS1591Restriction on datatype You cannot constrain this data type (with NOT NULL, for example). If datatype is a constrained subtype, then the returned value does not inherit the constraints of the subtype (see "Formal Parameters of Constrained Subtypes").

LNPLS1592function_definition

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

LNPLS1593declare_section

Declares items that are local to the function, can be referenced in body, and cease to exist when the function completes execution.

LNPLS1594body

Required executable part and optional exception-handling part of the function. In the executable part, at least one execution path must lead to a RETURN statement; otherwise, a runtime error occurs.

LNPLS1595call_spec, EXTERNAL

See"call_spec" and "EXTERNAL".

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

LNPLS1597relies_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"

Related Topics

LNPLS1598In this chapter:

• "Formal Parameter Declaration"

• "PIPE ROW Statement"

• "Procedure Declaration and Definition"

LNPLS1599In other chapters:

• Chapter 8, "PL/SQL Subprograms"

• "Creating Pipelined Table Functions"