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.
Registration Open For New Online Training
Enhance Your Professional Journey with Our Upcoming Live Session. For complete information on Registration, Course Details, Syllabus, and to get the Zoom Credentials to attend the free live Demo Sessions, please click on the below links.