User-Defined Functions «Prev 

Primary Advantage of Functions in Oracle

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
cnt number(2) := 0;
select count(*)
into cnt
from employees e
where e.deptno = p_deptno;
return (cnt);