PL/SQL: Functions in Oracle

A Function is a named PL/SQL subprogram, which when called from SQL or PL/SQL statement, mandatorily returns one and only one value. Functions are used to implement computation based logic. They are stored as schema objects in database. They promote reusability of logic and modularity.

REPLACE preserves the object privileges, during change in definition.
Parameters are passed into Function in any of three modes IN, OUT, IN OUT.
RETURN clause specifies the data type of the value returned by the function. A Function may contain multiple RETURN statements in the body, but must logically return a single value

3. Procedures vs Functions

[TABLE]ProcedureFunction
Part of PL/SQL statement Part of an expression
No RETURN in header RETURN keyword specified in header with the data type
May or may not return values to host Must return single value to the host
Executable section may or may not contain RETURN statement Executable section compulsorily contains one logical RETURN statement
Objective is to store series of logical actions for serial execution Objective is to perform computational tasks and return result to the calling environment[/TABLE]

A user defined function can be called from WHERE, HAVING, ORDER BY and GROUP BY clause and in hierarchical query clauses i.e. START WITH and CONNECT BY clauses in a SELECT statement.

Restrictions when calling functions from SQL Expressions:
• Stored function must be in valid state
• All parameter must be of IN mode and should be of SQL supported data type and not a BOOLEAN or COLLECTION type
• Function must return value of SQL supported data type
Below SELECT query lists the employee details whose salary is same as the average salary of the company.

A function called from a SQL must ensure the purity of database state. Below are the database states, which must be respected to ensure the purity level.
• A function cannot make changes in tables, if called from SELECT query
• A function cannot query a table, if called from INSERT, UPDATE or DELETE