Oracle Function

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]
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:

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:

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 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.

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.

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.

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;