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 statements] BEGIN executable statements [EXCEPTION exception handler statements] END [ name ];

where each element is used in the following ways:

schema

Optional name of the schema that will own this function. The default is the current user. If different from the current user, that user will need privileges to create a function in another schema.

name

The name of the procedure comes directly after the keyword FUNCTION.

parameters

An optional list of parameters that you define to both pass information into the procedure and send information out of the procedure back to the calling program.

return_datatype

The datatype of the value returned by the function. This is required in the function header and is explained in more detail in the next section.

AUTHID clause

Determines whether the procedure will execute under the authority of the definer (owner) of the procedure or under the authority of the current user. The former is known as the definer rights model, the latter as the invoker rights model.

DETERMINISTIC clause

An optimization hint that lets the system use a saved copy of the function's return result, if available. The query optimizer can choose whether to use the saved copy or re-call the function.

PARALLEL_ENABLE clause

An optimization hint that enables the function to be executed in parallel when called from within a SELECT statement.

PIPELINED clause

Specifies that the results of this table function should be returned iteratively via the PIPE ROW command.

declaration statements

The declarations of local identifiers for that function. If you do not have any declarations, there will be no statements between the IS and BEGIN statements.

executable statements

The statements the function executes when it is called. You must have at least one executable statement after the BEGIN and before the END or EXCEPTION keywords.

exception handler statements

The optional exception handlers for the function. If you do not explicitly handle any exceptions, then you can leave out the EXCEPTION keyword and simply terminate the execution section with the END keyword.

Figure 16-2 illustrates the PL/SQL function and its different sections. Notice that the tot_sales function does not have an exception section.