FUNCTION in Oracle

ORACLE FUNCTION:
A function in Oracle can be simply understood as a subprogram. It is mainly used to return a single value. There are mainly three subprocesses for a function to execute successfully: Declaration, Definition and Calling. The declaration and definition processes of a function must be done before invoking it. A function can be declared and defined simultaneously or it can be done in two steps within the same block: declared first and defined later.

There are three types of parameters that must be specified while creating a function.

• IN: Being a default parameter, it is used to pass a value to the subprogram.
• OUT: When specified, it is used to return a value to the caller.
• IN OUT: When specified, it is used to get an updated value to the caller by passing an initial value to the subprogram.

Syntax: To create or replace a function.

CREATE OR REPLACE FUNCTION func_name  
(parameter)
RETURN return_datatype  
IS | AS  
declaration_section  
BEGIN  
executable_section  
EXCEPTION  
exception_section 
END func_name; 

Syntax: To drop a function.

DROP FUNCTION func_name;   

Example 1: Creating a simple function and calling it later.
Creating Function:

CREATE OR REPLACE FUNCTION subtractor 
(a in number, b in number)    
RETURN number    
IS     
c number(8);    
BEGIN    
c :=a-b;    
return c;    
END;

Output:

Function created.

Calling the Function:

DECLARE    
   c number(2);    
BEGIN    
   c := subtractor (10, 5);    
   dbms_output.put_line('Subtraction is: ' || c);    
END;

Output:

Subtraction is: 5
Statement processed.
0.03 seconds

Explanation:
Here we are creating a simple function for subtraction. There are three variables a, b and c which are declared and defined along with their data types. The next step is to call the function by passing the values to the variables. The result thus received is the value of c as the result of subtraction for a and b.

Example 2: Creating a function by declaring, defining and calling it in the same block.

DECLARE  
n1 number;  
n2 number;  
n3 number;  
FUNCTION findMin (a IN number, b IN number)   
RETURN number  
IS  
c number;  
BEGIN  
IF a < b THEN  
c:= a;  
ELSE  
c:= b;  
END IF;  
RETURN c;  
END;   
BEGIN  
n1:= 650;  
n2:= 651;   
n3 := findMin (n1, n2);  
dbms_output.put_line(' Minimum of (650, 651): ' || n3);  
END;

Output:

Minimum of (650, 651): 650
Statement processed.
0.03 seconds

Explanation:
Here we are creating a function for finding the minimum value by declaring, defining and calling it in the same block. There are three variables which are declared and defined along with their data types. The next step is to call the function by passing the values to the variables. The result thus received is the value of n3 equal to the minimum of n1 and n2.

Example 3: Creating a function using table.
Students Table:

ID	NAME	AGE
1	Joy	20
2	Smiley	19
3	Happy	21
4	James	22
5	Bond	25

Creating Function:

CREATE OR REPLACE FUNCTION num_students
RETURN number 
IS  num number(2) := 0;  
BEGIN  
SELECT count(*) into num  
FROM students;  
RETURN num;  
END;

Output:

Function created.

Calling Function:

DECLARE  
n number(2);  
BEGIN  
n := num_students();  
dbms_output.put_line('Number of Students: ' || n);  
END;

Output:

Number of Students: 5
PL/SQL procedure successfully completed.

Explanation:
The ‘students’ is an already existing table. Here we are creating a function for finding the total number of students from the ‘students’ table. The next step is to call the function by passing the values to the variables. The result thus received is the value of n equal to the total number of students.

Example 3: Creating a recursive function.

DECLARE  
n number;  
fact number;  
FUNCTION factorial(a number)  
RETURN number   
IS  x number;  
BEGIN  
IF a=0 
THEN  x := 1;  
ELSE  
x := a * fact(a-1);  
END IF;  
RETURN x;  
END;  
BEGIN  
n:= 5;  
fact := factorial (n);  
dbms_output.put_line(' Factorial of '|| n || ' is ' || fact);  
END;

Output:

Factorial of 5 is 120
PL/SQL procedure successfully completed.

Explanation:
The process of calling a subprogram by the subprogram itself is known as Recursion. This type of call is often known as Recursive Call. Here we are creating a recursive function for finding the factorial value by declaring, defining and calling it in the same block. There are two variables which are declared and defined along with their data types. The next step is to call the function by passing the values to the variables. The result thus received is the value of fact equal to the factorial of n.

Example: Eliminating a function.

DROP FUNCTION subtractor;

Explanation:
The ‘subtractor’ is an already created function. The above code will eliminate the ‘subtractor’ function from the Oracle database.