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

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.

Now write another program to call the function.

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.

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:

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

Function created. 

Calling Oracle Function:

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.

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: