PL/SQL FunctionThe PL/SQL Function is very similar to PL/SQL Procedure. The main difference between procedure and a function is, a function must always return a value, and on the other hand a procedure may or may not return a value. Except this, all the other things of PL/SQL procedure are true for PL/SQL function too. Syntax to create a function: Here:
The function must contain a return statement.
PL/SQL Function ExampleLet'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 PL/SQL Function ExampleLet'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 PL/SQL function example using tableLet'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 PL/SQL Function: While creating a function, you have to give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. Once the function is called, the program control is transferred to the called function. After the successful completion of the defined task, the call function returns program control back to the main program. To call a function you have to pass the required parameters along with function name and if function returns a value then you can store returned value. Following program calls the function totalCustomers from an anonymous block: 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. PL/SQL Recursive FunctionYou 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 numberLet'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. PL/SQL Drop FunctionSyntax for removing your created function: If you want to remove your created function from the database, you should use the following syntax. Next TopicPL/SQL Cursor |