Creating Functions

CREATE FUNCTION Statement

A function is a named PL/SQL block that can accept parameters, be invoked, and return a value. In general, you use a function to compute a value. You create new functions with the CREATE FUNCTION statement, which may declare a list of parameters and must define the actions to be performed by the standard PL/SQL block.

Functions and procedures are structured alike. A function must return a value to the calling environment, whereas a procedure returns zero or more values to its calling environment through OUT parameters. Like a procedure, a function has a header, a declarative section, an executable section, and an optional exception-handling section. A function must have a RETURN clause in the header and at least one RETURN statement in the executable section.

Syntax

CREATE [OR REPLACE] FUNCTION function_name
    [(parameter1 [mode1] datatype1, 
      parameter2 [mode] datatype2, ...)]
RETURN datatype 
IS|AS
    [local_variable_declarations; …]
BEGIN
    -- actions;
    RETURN expression;
END [function_name];