Oracle: Functions

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

RETURNreturn_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;    

returnn3;    

end;    

/    

Now write another program to call the function.

DECLARE

n3 number(2);    

BEGIN

n3 := adder(11,22);    

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;  

ENDIF;  

RETURNz;  

END;   

BEGIN

a:= 23;  

b:= 45;  

c := findMax(a, b);  

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:

CREATEOR REPLACE FUNCTION totalCustomers  

RETURNnumber 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();  

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;  

FUNCTIONfact(x number)  

RETURNnumber   

IS

f number;  

BEGIN

IF x=0 THEN

f := 1;  

ELSE

f := x * fact(x-1);  

ENDIF;  

RETURNf;  

END;  

BEGIN

num:= 6;  

factorial := fact(num);  

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;