Oracle Function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

  1. CREATE [OR REPLACE] FUNCTION function_name  
  2.    [ (parameter [,parameter]) ]  
  3. RETURN return_datatype  
  4. IS | AS  
  5.  [declaration_section]  
  6. BEGIN  
  7.    executable_section  
  8. [EXCEPTION  
  9.    exception_section]  
  10. 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.

  1. create or replace function adder(n1 in number, n2 in number)    
  2. return number    
  3. is     
  4. n3 number(8);    
  5. begin    
  6. n3 :=n1+n2;    
  7. return n3;    
  8. end;    
  9. /    

Now write another program to call the function.

  1. DECLARE    
  2.    n3 number(2);    
  3. BEGIN    
  4.    n3 := adder(11,22);    
  5.    dbms_output.put_line('Addition is: ' || n3);    
  6. END;    
  7. /    

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.

  1. DECLARE  
  2.    a number;  
  3.    b number;  
  4.    c number;  
  5. FUNCTION findMax(x IN number, y IN number)   
  6. RETURN number  
  7. IS  
  8.     z number;  
  9. BEGIN  
  10.    IF x > y THEN  
  11.       z:= x;  
  12.    ELSE  
  13.       Z:= y;  
  14.    END IF;  
  15.   
  16.    RETURN z;  
  17. END;   
  18. BEGIN  
  19.    a:= 23;  
  20.    b:= 45;  
  21.   
  22.    c := findMax(a, b);  
  23.    dbms_output.put_line(' Maximum of (23,45): ' || c);  
  24. END;  
  25. /  

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.

Customers
Id Name Department Salary
1 alex web developer 35000
2 ricky program developer 45000
3 mohan web designer 35000
4 dilshad database manager 44000

Create Function:

  1. CREATE OR REPLACE FUNCTION totalCustomers  
  2. RETURN number IS  
  3.    total number(2) := 0;  
  4. BEGIN  
  5.    SELECT count(*) into total  
  6.    FROM customers;  
  7.     RETURN total;  
  8. END;  
  9. /  

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

Function created. 

Calling Oracle Function:

  1. DECLARE  
  2.    c number(2);  
  3. BEGIN  
  4.    c := totalCustomers();  
  5.    dbms_output.put_line('Total no. of Customers: ' || c);  
  6. END;  
  7. /  

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.

  1. DECLARE  
  2.    num number;  
  3.    factorial number;  
  4.   
  5. FUNCTION fact(x number)  
  6. RETURN number   
  7. IS  
  8.    f number;  
  9. BEGIN  
  10.    IF x=0 THEN  
  11.       f := 1;  
  12.    ELSE  
  13.       f := x * fact(x-1);  
  14.    END IF;  
  15. RETURN f;  
  16. END;  
  17.   
  18. BEGIN  
  19.    num:= 6;  
  20.    factorial := fact(num);  
  21.    dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);  
  22. END;  
  23. /  

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:

  1. DROP FUNCTION function_name;