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
You must have define some parametrs before creating a procedure or a function. These parameters are
Oracle Function Example
Let's see a simple example to create a function.
Now write another program to call the function.
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.
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.
After the execution of above code, you will get the following result.
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.