A function is a subprogram that is used to return a single value. You must declare and define a function before invoking it. It can be declared and defined at a same time or can be declared first and defined later in the same block.

CREATE function in Oracle

Syntax

CREATE [OR REPLACE] FUNCTION function_name  
   [ (parameter [,parameter]) ]  
RETURN return_datatype  
IS | AS  
 [declaration_section]  
BEGIN  
   executable_section  
[EXCEPTION  
   exception_section]  

1. END [function_name];  

You must have define some parametrs before creating a procedure or a function. These parameters are

• IN: It is a default parameter. It passes the value to the subprogram.
• OUT: It must be specified. It returns a value to the caller.
• IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.

Oracle Function Example

Let's see a simple example to create a function.

create or replace function adder(n1 in number, n2 in number)    
return number    
is     
n3 number(8);    
begin    
n3 :=n1+n2;    
return n3;    
end;    
/    

Now write another program to call the function.

DECLARE    
   n3 number(2);    
BEGIN    
   n3 := adder(11,22);    
   dbms_output.put_line('Addition is: ' || n3);    
END;    
/    

Output:

Addition is: 33
Statement processed.
0.05 seconds

Another Oracle Function Example

Let's take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL function which will compute and return the maximum of two values.

DECLARE  
   a number;  
   b number;  
   c number;  
FUNCTION findMax(x IN number, y IN number)   
RETURN number  
IS  
    z number;  
BEGIN  
   IF x > y THEN  
      z:= x;  
   ELSE  
      Z:= y;  
   END IF;  
  
   RETURN z;  
END;   
BEGIN  
   a:= 23;  
   b:= 45;  
  
   c := findMax(a, b);  
   dbms_output.put_line(' Maximum of (23,45): ' || c);  
END;  
/  

Output:

Maximum of (23,45): 45
Statement processed.
0.02 seconds

Oracle function example using table

Let's take a customer table. This example illustrates creating and calling a standalone function. This function will return the total number of CUSTOMERS in the customers table.

Create customers table and have records in it.

Create Function:
CREATE OR REPLACE FUNCTION totalCustomers  
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM customers;  
    RETURN total;  
END;  
/  

After the execution of above code, you will get the following result.

Function created.

Calling Oracle Function:

DECLARE  
   c number(2);  
BEGIN  
   c := totalCustomers();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;  
/  

After the execution of above code in SQL prompt, you will get the following result.

Total no. of Customers: 4
PL/SQL procedure successfully completed.

Oracle Recursive Function

You already know that a program or a subprogram can call another subprogram. When a subprogram calls itself, it is called recursive call and the process is known as recursion.

Example to calculate the factorial of a number

Let's take an example to calculate the factorial of a number. This example calculates the factorial of a given number by calling itself recursively.

DECLARE  
   num number;  
   factorial number;  
  
FUNCTION fact(x number)  
RETURN number   
IS  
   f number;  
BEGIN  
   IF x=0 THEN  
      f := 1;  
   ELSE  
      f := x * fact(x-1);  
   END IF;  
RETURN f;  
END;  
  
BEGIN  
   num:= 6;  
   factorial := fact(num);  
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);  
END;  
/  

After the execution of above code at SQL prompt, it produces the following result.

Factorial 6 is 720
PL/SQL procedure successfully completed.

Oracle Drop Function

If you want to remove your created function from the database, you should use the following syntax.

Syntax:

DROP FUNCTION function_name;