Function Declaration

A function is a subprogram that can take parameters and return a single value. A function has two parts: the specification and the body. The specification (spec for short) begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the return value. Parameter declarations are optional. Functions that take no parameters are written without parentheses. The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name.

The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate data. The exception-handling part contains handlers that deal with exceptions raised during execution. For more information, see "Understanding PL/SQL Functions". For an example of a function declaration, see Example 9-3.

Note that the function declaration in a PL/SQL block or package is not the same as creating a function in SQL. For information on the CREATE FUNCTION SQL statement, see Oracle Database SQL Reference.

Syntax

function specification ::=

Description of the illustration function_specification.gif

function declaration ::=

Description of the illustration function_declaration.gif

function body ::=

Description of the illustration function_body.gif

parameter declaration ::=

Description of function_parameter.gif follows

Description of the illustration function_parameter.gif

Keyword and Parameter Description

datatype

A type specifier. For the syntax of datatype, see "Constant and Variable Declaration".

DETERMINISTIC

A hint that helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called 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 the CREATE FUNCTION statement in the Oracle Database SQL Reference. See also the CREATE INDEX statement in Oracle Database SQL Reference.

exception_handler

Associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exception Definition".

expression

An arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.

function_name

Specifies the name you choose for the function.

IN, OUT, IN OUT

Parameter modes that define the behavior of formal parameters. An IN parameter passes values to the subprogram being called. An OUT parameter returns values to the caller of the subprogram. An IN OUT parameter passes initial values to the subprogram being called, and returns updated values to the caller.

item_declaration

Declares a program object. For its syntax, see "Block Declaration".

NOCOPY

A compiler hint (not directive) that allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value (the default). The function can run faster, because it does not have to make temporary copies of these parameters, but the results can be different if the function ends with an unhandled exception. For more information, see "Using Default Values for Subprogram Parameters".

PARALLEL_ENABLE

Declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) variables. Otherwise, results might vary across sessions. For information on the PARALLEL_ENABLE option, see the CREATE FUNCTION statement in the Oracle Database SQL Reference.

parameter_name

A formal parameter, a variable declared in a function spec and referenced in the function 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 datatypes. 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 "Setting Up Transformations with Pipelined Functions".

PRAGMA AUTONOMOUS_TRANSACTION

Marks a function as autonomous. An autonomous transaction is an independent transaction started by the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. For more information, see "Doing Independent Units of Work with Autonomous Transactions".

procedure_declaration

Declares a procedure. For the syntax of procedure_declaration, see "Procedure Declaration".

RETURN

Introduces the RETURN clause, which specifies the datatype of the return value.

type_definition

Specifies a user-defined datatype. For its syntax, see "Block Declaration".

:= | DEFAULT

Initializes IN parameters to default values.

Usage Notes

A function is called as part of an expression:

promotable := sal_ok(new_sal, new_title) AND (rating > 3);

To be callable from SQL statements, a stored function must obey certain rules that control side effects. See "Controlling Side Effects of PL/SQL Subprograms".

In a function, at least one execution path must lead to a RETURN statement. Otherwise, you get a function returned without value error at run time. The RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable.

You can write the function spec and body as a unit. Or, you can separate the function spec from its body. That way, you can hide implementation details by placing the function in a package. You can define functions in a package body without declaring their specs in the package spec. However, such functions can be called only from inside the package.

Inside a function, an IN parameter acts like a constant; you cannot assign it a value. An OUT parameter acts like a local variable; you can change its value and reference the value in any way. An IN OUT parameter acts like an initialized variable; you can assign it a value, which can be assigned to another variable. For information about the parameter modes, see Table 8-1.

Avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. Functions should be free from side effects, which change the values of variables not local to the subprogram.

Examples

For examples, see the following:

Example 1-13, "Creating a Package and Package Body"
Example 2-15, "Using a Subprogram Name for Name Resolution"
Example 2-27, "Using a Search Condition With a CASE Statement"
Example 5-44, "Returning a Record from a Function"
Example 6-43, "Declaring an Autonomous Function in a Package"
Example 6-48, "Calling an Autonomous Function"
Example 9-3, "Creating the emp_admin Package"