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.
