| Lesson 2 | What is a function? |
| Objective | Describe Uses of the Oracle Function and identify Correct Syntax |
Uses of Oracle functions and Function 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.
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.
What is a function in PL/SQL?
The primary advantage of a function is to let you, as the designer or developer, create an encapsulated set of code that might otherwise be impossible to accomplish within an SQL command. For example, you might use a function to handle a calculation that varies depending on some related information (such as calculating a long distance phone call charge based on the appropriate daytime, weekend, or evening rate).
It would be challenging to attempt this using a SQL Query.
A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause.
You write (local) functions using the syntax: A
stored function is similar to a stored procedure with one key difference:
A stored function returns a single value.
This makes a stored function available in your SQL SELECT statements, unlike stored procedures that you must call within an anonymous PL/SQL block or another stored procedure.
Although you might argue that Oracle already offers more than enough functions, you may find that you need a specific capability that is not already provided. In that case, you can develop your own functions (using PL/SQL) and add them to the SQL language. PL/SQL is the standard procedural programming language for Oracle databases. PL/SQL is a superset of SQL, adding several procedural capabilities to the nonprocedural SQL language. Here, we will investigate one simple example of PL/SQL language usage in relation to custom functions. For more information about PL/SQL, refer to the User Guide and Reference of Oracle PL/SQL . The listing below shows how to define a function to determine the number of employees for a given department.
Creating a Stored Function Using PL/SQL
create or replace function emp_count(p_deptno in number)
return number
is
cnt number(2) := 0;
begin
select count(*)
into cnt
from employees e
where e.deptno = p_deptno;
return (cnt);
end;
Advantages of using functions in Oracle PL/SQL
- Reusability: Functions can be reused in multiple places, which can save time and effort.
- Efficiency: Functions can be compiled and stored in memory, which can improve performance.
- Maintainability: Functions can be easily modified or updated without affecting other parts of the code.
- Security: Functions can be restricted to certain users or roles, which can help to protect data.
- Portability: Functions can be easily moved to other databases, which can make it easier to develop and maintain applications.
Here are some examples of how functions can be used in Oracle PL/SQL:
- A function can be used to validate data before it is entered into the database.
- A function can be used to calculate a value based on other data in the database.
- A function can be used to perform a complex operation that would be difficult to code in SQL.
- A function can be used to encapsulate a piece of code that is used in multiple places, which can make the code easier to understand and maintain.
- Overall, functions can be a powerful tool for improving the performance, maintainability, and security of Oracle PL/SQL applications.
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.
