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

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

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

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

Output:

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.

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

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.

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

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

Output
Function created.

Calling Oracle Function:

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

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

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

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

snippet
DROP FUNCTION function_name;
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents