PL/SQL User's Guide and Reference

Functions

Description

A function is a named program unit that takes parameters and returns a computed value. For more information, see "Functions"

A function has two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the result value. Parameter declarations are optional. Functions that take no parameters are written without parentheses.

The function body begins with the keyword IS 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 objects 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 exception handlers, which deal with exceptions raised during execution.

Syntax

function_specification ::=

FUNCTION function_name [(parameter_declaration[, 
  parameter_declaration]...)]
RETURN return_type;

function_body ::=

FUNCTION function_name [(parameter_declaration[, 
  parameter_declaration]...)] 
RETURN return_type IS
  [[object_declaration [object_declaration] ...]
   [subprogram_declaration [subprogram_declaration] ...]]
BEGIN
    seq_of_statements
[EXCEPTION 
    exception_handler [exception_handler] ...]
END [function_name];

parameter_declaration ::=

parameter_name [IN | OUT | IN OUT]
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [{:= | DEFAULT} expression]

return_type ::=

{  cursor_name%ROWTYPE
 | cursor_variable_name%ROWTYPE
 | plsql_table_name%TYPE
 | record_name%TYPE
 | scalar_type_name
 | table_name%ROWTYPE
 | table_name.column_name%TYPE
 | variable_name%TYPE}

object_declaration ::=

{  constant_declaration
 | cursor_declaration
 | cursor_variable_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

subprogram_declaration ::=

{function_declaration | procedure_declaration}

Keyword and Parameter Description

function_name

This identifies a user-defined function. For naming conventions, see "Identifiers"

parameter_name

This identifies a formal parameter, which is a variable declared in a function specification and referenced in the function body.

IN, OUT, IN OUT

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 IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.

:= | DEFAULT

This operator or keyword allows you to initialize IN parameters to default values.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions"

. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.

RETURN

This keyword introduces the RETURN clause, which specifies the datatype of the result value.

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

record_name

This identifies a user-defined record previously declared within the current scope.

scalar_type_name

This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints. For more information, see "Datatypes"

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

table_name.column_name

This identifies a database table and column that must be accessible when the declaration is elaborated.

variable_name

This identifies a PL/SQL variable previously declared within the current scope.

%ROWTYPE

This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor or cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.

%TYPE

This attribute provides the datatype of a previously declared field, record, PL/SQL table, database column, or variable.

constant_declaration

This construct declares a constant. For the syntax of constant_declaration, see "Constants and Variables"

cursor_declaration

This construct declares an explicit cursor. For the syntax of cursor_declaration, see "Cursors"

cursor_variable_ declaration

This construct declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables"

exception_declaration

This construct declares an exception. For the syntax of exception_declaration, see "Exceptions"

plsql_table_declaration

This construct declares a PL/SQL table. For the syntax of plsql_table_declaration, see "PL/SQL Tables"

record_declaration

This construct declares a user-defined record. For the syntax of record_declaration, see "Records"

variable_declaration

This construct declares a variable. For the syntax of variable_declaration, see "Constants and Variables"

function_declaration

This construct declares a nested function.

procedure_declaration

This construct declares a procedure. For the syntax of procedure_declaration, see "Procedures"

exception_handler

This construct associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions"

Usage Notes

Every function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.

A function is called as part of an expression. For example, the function sal_ok might be called as follows:

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

To be callable from SQL expressions, a stored function must obey certain rules meant to control side effects. For standalone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden. So, for packaged functions, you must use the pragma RESTRICT_REFERENCES to enforce the rules. For more information, see "Calling Stored Functions from SQL Expressions" in Oracle7 Server Application Developer's Guide.

You can write the function specification and body as a unit. Or, you can separate the function specification 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 specifications in the package specification. However, such functions can be called only from inside the package.

Inside a function, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. An OUT parameter acts like an uninitialized variable. So, its value cannot be assigned to another variable or reassigned to itself. An IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value, and its value can be assigned to another variable. For summary information about the parameter modes, see Table 7 - 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. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram. Thus, a function should not change the values of its actual parameters.

Functions can be defined using any Oracle tool that supports PL/SQL. However, to become available for general use, functions must be CREATEd and stored in an Oracle database. You can issue the CREATE FUNCTION statement interactively from SQL*Plus or Server Manager. For the full syntax of the CREATE FUNCTION statement, see Oracle7 Server SQL Reference.

Example

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;