Updated March 28, 2023
Introduction to Function in Oracle
A function is a subprogram stored in an Oracle database that returns a value. It is a stored PL/SQL block just like procedures but there is a difference. A function always returns a value whereas a procedure may or may not return a value. We have to declare as well as define the procedures before we use them in our operations. A function is very important in PL/SQL whenever we want to do a certain task which returns some value after execution. We will further discuss functions in this article.
Syntax:
To understand functions better Let us go through the syntax of the functions in oracle
Code:
CREATE OR REPLACE FUNCTION function_name(parameter) ]
RETURN datatype
IS | AS
variable_name
BEGIN
Executable_section
EXCEPTION
[exception_section]
END [function_name];
Parameters:
- function_name: The name of the function.
- parameters: The arguments which we are passing into the function.
- variable_name: The variable name to be used in the executable section.
- executable_section: This section is used as the body of the function.
- Exception_section: This section is used to catch the exception if any.
- END: This marks the end of the function.
How do Functions work in Oracle?
- Functions are a very useful PL/SQL block. They pretty much work like functions in other languages where we generally used to execute certain business logic but also except an output or response in return. We first create a function using the PL/SQL procedural language. In case we want to create a parameterized function than we pass arguments with the function.
- After that, the control goes to the DECLARE section in which the function local variables are declared and after that, we come to the execution section, Execution section starts with the BEGIN keyword. In this section, we write the body of the function which means the logic of the function. In case there is an exception that occurs in the execution section while executing the function. We can have an EXCEPTION section to handle the exceptions thrown by the control. The END keyword suggests the end of the function and execution ends.
Example:
So, let us see a practical example to have a better understanding of how functions work. In this example, we will have two steps. The first step is going to be the creation of the function in the database and the second step is going to be the execution of the function by calling the function and passing input parameters with the function.
So, in this example, we are going to create a function that is going to return the employee name of the employee when we pass the employee id in the function. Let us look at the below query for the creation of the function.
PL/SQL block to create a function:
Code:
CREATE OR REPLACE Function employee_find
( empid_in IN varchar2 )
RETURN varchar2
IS
cname varchar2(20);
cursor emp is
SELECT name
FROM employee
WHERE employee_id = empid_in;
BEGIN
open emp;
fetch emp into cname;
if emp%notfound then
cname := 'NO RECORDS';
end if;
close emp;
RETURN cname;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
If we look at the query we can see that the function takes an argument as the variable. The return type of the function is set as varchar2 since we are returning the name of the employee. We then create a cursor so that we can store the record/records that we retrieve from the SELECT statement into a memory location. The program then puts the value which in our case will be the employee name returned into the variable cname that we had declared in the declaration section. If the cursor is empty then the function will return ‘NO RECORDS’ and if it is not empty then the function returns the local variable. In case there is any runtime exception during execution, then there is a separate EXCEPTION section to handle the exception. The END statement represents the end of the function.
Let us run the PL/SQL program in SQL developer and see the output.
As you can see the function has been successfully compiled which means we can now use this function. So let us now execute the function. To do that we will create a PL/SQL block and declare a variable to store the value returned by the function. Then the variable is printed as an output.
The PL/SQL block for the execution of the function is shown below.
Code:
SET SERVEROUTPUT ON
declare
result varchar2(20);
begin
-- Call the function
result := employee_find('AD002');
Dbms_Output.Put_Line('The employee name is' ||result);
end;
Let us now execute the above PL/SQL block in SQL developer. And check the output.
As you can see the output shows the name of the employee.
How to Drop Function in Oracle?
Once we create a function it is saved in the database. To delete the function from the database we need to drop the function. We need to use the DROP keyword to drop the function. Let us look at the query for the same as shown below.
Query:
DROP function employee_find;
Let us now run the above SQL query in SQL developer and check the output.
As we can see the output shows that the PL/SQL function has been dropped from the database.
Advantages of Using Function
Let us look at some of the advantages of using stored FUNCTIONS in PL/SQL
- It improves database performance as compilation is automatically done by the Oracle engine.
- Whenever the stored function is called, the oracle engine loads the function into a memory area SGA due to which the execution becomes very fast.
- It provides reusability as the user is able to reuse the block of code whenever required.
- It maintains integrity as they are stored as Oracle database objects by the Oracle engine.
Recommended Articles
This is a guide to Function in Oracle. Here we discuss the introduction, how do Functions work in Oracle, how to use drop function along with example, syntax. You can also go through our other suggested articles to learn more –
