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.
