Lesson 2	What is a function?
Objective	Describe Uses of the Oracle Function and identify Correct Syntax

Uses of Oracle functions

Identify Correct Syntax

Question: What are the uses of a function and how do you identify the correct syntax?
A function is a set of PL/SQL code that is stored with a name inside the database. Like other objects created in the database, a function is created and owned by an Oracle user. Once created, it can be executed the same way that Oracle's built-in functions are executed: by calling it by name within a query or some other SQL or PL/SQL statement. A function usually accepts one or more parameters and always returns a value.

Advantages of PL/SQL Functions

A stored function is one that is stored as a named object in the database. One advantage of using a stored function is that the function is executed within the server as opposed to executed within the client. The function can take advantage of higher processing power and of local access to additional data within the server.

Advantage of Functions

Functions can be defined independently within a PL/SQL block without being stored inside the database. These kinds of functions are used in applications and might be executed on the client side. The stored functions we are discussing in this module, however, are much more common since they are stored within the database itself, which makes them reusable and shareable.

Uses of Oracle Functions

Functions are commonly used to:

Perform complex calculations and return the results
Accept an unformatted data field and return it in a standard format, such as a Social Security Number
Accept a key field and return summarized data from related tables

Functions can be used in SQL commands just about anywhere you can use an expression. For example, you can use them within the SET clause of an INSERT command, or within the SELECT or WHERE clauses of a query.

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]
[RESULT_CACHE ...]
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 function.
parameters: An optional list of parameters that you define to both pass information into the function and send information out of the function 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 function will execute with the privileges of the definer (owner) of the procedure or 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.
RESULT_CACHE clause New to Oracle Database 11g. Specifies that the input values and result of this function should be stored in the new function result cache.
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.

Stored function restrictions

There are some restrictions in using and coding stored functions. The following table shows the capabilities and restrictions of stored functions.

Capability of stored function	Restriction of stored function
Can be used within SQL commands (such as queries) and within a PL/SQL block so long as the function returns a standard Oracle datatype (number, date, varchar2, char)	Cannot modify data directly, so they cannot include INSERT, UPDATE, or DELETE commands
Can only be used within a PL/SQL block if the function returns a Boolean datatype, or other PL/SQL datatype (other than the standard Oracle datatypes listed above)	Cannot be used inside a CHECK constraint

The next lesson explains the proper syntax for creating a stored function.