User Defined Functions in Oracle

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.

Leave a Reply

Your email address will not be published. Required fields are marked *