Back to: Oracle Tutorials for Beginners and Professionals

User-Defined Functions in Oracle

In this article, I am going to discuss User Defined Functions in Oracle. Please read our previous article where we discussed Boolean Parameters in Oracle Stored Procedure with Examples. The function is a PL/SQL that returns a value. The main purpose of the function is to write a select statement or a complicated select statement in order to calculate a value and return a value.

What is a Function in Oracle?

A function is a named PL/SQL block that returns a value (In general to compute A value) that can be stored in the database as a schema object for repeated execution.

This is called a part of an expression or is used to provide a parameter value. We can use the part of the function as an expression. We can create a function and call the function at any time. This is the difference between procedure and function. The syntax of the function is very simple and easy. The PL/SQL block must have at least one RETURN statement.

Examples of Functions:

• A function can be created to return the salary of an employee.
• Function to retrieve the full name of the employee.
• Function to calculate the GPA for the student.
• Function to compute the tax of the salary.

Syntax to Create User-Defined Function in Oracle:

CREATE OR REPLACE FUNCTION function_name
{(parameter1 {mode1] datatype1, …)]
RETURN datatype IS|AS
[local_variable_declaration;
…]
BEGIN
--actions;
RETURN expression;
END [function_name];

This is the syntax of the creation of the function. We will use the statement create or replace function to create a function and list down the parameters and return the value with the datatype.

So, for example, we can create a function that takes the parameter employee_id and return the salary. In this case, return is a number. We can also take the parameter as employee_id and return the employee_name. In this case, the return is varchar2. In the PL/SQL block, the block should return. It can also return an expression.

Points to Remember while working with User Defined Functions in Oracle:

• Host variables are not allowed and substitute variables are not allowed. This is like procedures.
• It should be at least one return expression in the executable section.
• Return datatype should be without size.
• OUT/IN OUT can be used, but this is not good practice.
• The return datatype and the return in the PL/SQL block should be of the same data type. We cannot return with different datatypes in the function and in the PL/SQL block.

Differences Between Functions and Procedures in Oracle

Procedures	Functions
Execute a PL/SQL statement	Invoke as a part of an expression.
Does not contain a RETURN clause in the header.	Must contain a RETURN clause in the header.
Can pass values using output parameters	Must return a single value.
Can contain a RETURN statement without a value.	Must contain at least one RETURN statement.
To Perform an action	To return a value.
Cannot be used in select statements.	Can be used in the select statements, but it should not include OUT/ IN OUT parameters.

Note: We can create a procedure to upgrade the salary details using insert or update in a table. There are a lot of restrictions while calling function should not include in/out parameters. The function should not include DML operations like insert or update in order to use it in select statements. The main concept of the function is to return a value, so no need to use the DML / DDL operations inside a function. A procedure that has one parameter (OUT) would be better rewritten as a function.

In the next article, I am going to discuss How to Create User Defined Functions in Oracle with Examples. Here, in this article, I try to explain User Defined Functions in Oracle. I hope you enjoy this User Defined Functions in Oracle article.