|PL/SQL User's Guide and Reference
Part Number A89856-01
PL/SQL Language Elements, 23 of 52
A function is a subprogram that can take parameters and be invoked. Generally, you use a function to compute a 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
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 Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution. For more information, see "Understanding PL/SQL Functions".
This clause determines whether a stored function executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying
CURRENT_USER. For more information, see "Invoker Rights Versus Definer Rights".
CREATE clause lets you create standalone functions, which are stored in the Oracle database. You can execute the
CREATE statement interactively from SQL*Plus or from a program using native dynamic SQL.
This is a type specifier. For the syntax of
datatype, see "Constants and Variables".
This hint 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, see the statements
VIEW in Oracle9i SQL Reference.
This associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of
exception_handler, see "Exceptions".
This is 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.
This identifies a user-defined function.
These parameter modes define the behavior of formal parameters. An
IN parameter lets you pass values to the subprogram being called. An
OUT parameter lets you return values to the caller of the subprogram. An
OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.
This declares a program object. For the syntax of
item_declaration, see "Blocks".
This is a compiler hint (not directive), which allows the PL/SQL compiler to pass
OUT parameters by reference instead of by value (the default). For more information, see "Passing Large Data Structures with the NOCOPY Compiler Hint".
This option 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.
This identifies a formal parameter, which is a variable declared in a function spec and referenced in the function body.
This pragma instructs the PL/SQL compiler to mark a function as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, 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".
This declares a procedure. For the syntax of
procedure_declaration, see "Procedures".
This keyword introduces the
RETURN clause, which specifies the datatype of the return value.
This specifies a user-defined datatype. For the syntax of
type_definition, see "Blocks".
This operator or keyword lets you initialize
IN parameters to default values.
As the following example shows, a function is called as part of an expression:
To be callable from SQL statements, a stored function must obey certain rules meant to control side effects. See "Controlling Side Effects of PL/SQL Subprograms".
In a function, there must be at least one execution path that leads to a
RETURN statement. Otherwise, you get a function returned without value error at run time. Also, 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. So, you cannot assign it a value. An
OUT parameter acts like a local variable. So, you can change its value and reference the value in any way. An
OUT parameter acts like an initialized variable. So, you can assign it a value, which can be assigned to another variable. For summary information about the parameter modes, see Table 8-1.
Avoid using the
OUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.
The following function returns the balance of a specified bank account:
FUNCTION balance (acct_id INTEGER) RETURN REAL IS acct_bal REAL; BEGIN SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id; RETURN acct_bal; END balance;
Collection Methods, Packages, Procedures