Function Declaration and Definition

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.

Note:

This topic applies to functions that you declare and define inside a PL/SQL block or package, which are different from standalone stored functions that you create with the CREATE FUNCTION Statement.

Syntax

function_declaration ::=

function_declaration
Description of the illustration function_declaration.gif

function_heading ::=

function_heading
Description of the illustration function_heading.gif

(parameter_declaration ::=, datatype ::=)

function_definition ::=

function_definition
Description of the illustration function_definition.gif

(body ::=, declare_section ::=)

result_cache_clause ::=

result_cache_clause
Description of the illustration result_cache_clause.gif

Keyword and Parameter Descriptions

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; otherwise, you get a run-time error.

data_source

The name of either a database table or a database view.

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.

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

See Also:

CREATE INDEX statement in Oracle Database SQL Language Reference

function_declaration

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

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

function_definition

Either defines a function that was declared earlier in the same block or subprogram, or declares and defines a function.

function_name

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

IN, OUT, IN OUT

Parameter modes that define the action of formal parameters. For summary information about parameter modes, see Table 8-1.

NOCOPY

Specify NOCOPY to instruct the database to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an OUT or IN OUT parameter. IN parameter values are always passed NOCOPY.

  • When you specify NOCOPY, assignments made to a package variable may show immediately in this parameter, or assignments made to this parameter may show immediately in a package variable, if the package variable is passed as the actual assignment corresponding to this parameter.

  • Similarly, changes made either to this parameter or to another parameter may be visible immediately through both names if the same variable is passed to both.

  • If the function is exited with an unhandled exception, then any assignment made to this parameter may be visible in the caller's variable.

These effects may or may not occur on any particular call. You should use NOCOPY only when these effects would not matter.

parameter_name

The name of the formal parameter that you are declaring, which you can reference in body.

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.

RELIES_ON

Specifies the data sources on which the results of a function depend. For more information, see Using the PL/SQL Function Result Cache.

RESULT_CACHE

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

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. You cannot constrain this data type (with NOT NULL, for example).

{ := | DEFAULT } expression

Specifies a default value for an IN parameter. If the invoker of the function specifies a value for the parameter, then expression is not evaluated for that invocation (see Example 8-7). Otherwise, the parameter is initialized to the value of expression. The value and the parameter must have compatible data types.

Examples

Related Topics

See Also:

Oracle Database Advanced Application Developer's Guide for information about restrictions on user-defined functions that are called from SQL statements and expressions