Oracle PL/SQL for DBAs

Functions

A function is a module that returns a value. Unlike a procedure call, which is a standalone executable statement, a call to a function can exist only as part of an executable statement, such as an element in an expression or the value assigned as the default in a declaration of a variable.

Because a function returns a value, it is said to have a datatype. A function can be used in place of an expression in a PL/SQL statement having the same datatype as the function.

Functions are particularly important constructs for building modular code. For example, every business rule or formula in your application should be placed inside a function. Every single-row query should also be defined within a function, so that it can be easily and reliably reused.

Tip

Some people prefer to rely less on functions and more on procedures that return status information through the parameter list. If this is your preference, make sure that your business rules, formulas, and single-row queries are tucked away into your procedures.

An application short on function definition and usage is likely to be difficult to maintain and enhance over time.

Structure of a function

The structure of a function is the same as that of a procedure, except that the function also has a RETURN clause. The general format of a function is as follows:

    FUNCTION [schema.]name [( parameter [, parameter ...] ) ]
       RETURN return_datatype
       [AUTHID DEFINER | CURRENT_USER]
       [DETERMINISTIC]
       [PARALLEL ENABLE ...]
       [PIPELINED]
    IS
       [declaration ...