Function Declaration and Definition

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

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


This topic applies to nested functions.

For information about standalone functions, see "CREATE FUNCTION Statement".

For information about package functions, see "CREATE PACKAGE Statement".





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


The function heading specifies the function name and its parameter list.


Name of the function that you are declaring or defining.

RETURN datatype

Specifies the data type of the value that the function returns, which can be any PL/SQL data type (see PL/SQL Data Types).

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


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


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


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.


  • Example 8-2, "Declaring, Defining, and Invoking a Simple PL/SQL Function"