× PL/SQL - Overview PL/SQL - Basic Syntax PL/SQL - Data Types PL/SQL - Variables PL/SQL - Constants and Literals PL/SQL - Operators PL/SQL - Conditions PL/SQL -ifelse PL/SQL -elsif PL/SQL -nestedif PL/SQL -Case PL/SQL -Searched Case PL/SQL -Basic Loop PL/SQL -For Loop PL/SQL -While Loop PL/SQL - Strings PL/SQL - Arrays PL/SQL - Procedures PL/SQL - Functions PL/SQL - Cursors PL/SQL - Records PL/SQL - Exceptions PL/SQL - Triggers PL/SQL - Packages PL/SQL - Collections PL/SQL - Transactions PL/SQL - Date & Time PL/SQL - DBMS Output PL/SQL - Object Oriented
  • iconPLSQL Online Training In Andhra Pradesh and Telangana
  • icon9010519704

Opening Hours :7AM to 9PM

PL/SQL - Functions

PL/SQL - Functions
In this chapter, we will discuss the functions in PL/SQL. A function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous chapter are true for functions too.

Creating a Function
A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
   < function_body > 
END [function_name];
1.function-name specifies the name of the function.
2.[OR REPLACE] option allows the modification of an existing function.
3.The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
4.The function must contain a return statement.
5.The RETURN clause specifies the data type you are going to return from the function.
6.function-body contains the executable part.
7.The AS keyword is used instead of the IS keyword for creating a standalone function.

Example Program
The following example illustrates how to create and call a standalone function. This function returns the total number of CUSTOMERS in the customers table.
We will use the CUSTOMERS table, which we had created in the PL/SQL Variables chapter
Select * from customers; 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 

RETURN number IS 
   total number(2) := 0; 
   SELECT count(*) into total 
   FROM customers; 
   RETURN total; 
When the above code is executed using the SQL prompt, it will produce the following result
Function created.

Calling a Function
While creating a function, you 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. When a program calls a function, the program control is transferred to the called function.
A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program.
To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block
   c number(2); 
   c := totalCustomers(); 
   dbms_output.put_line('Total no. of Customers: ' || c); 
Total no. of Customers: 6

Example Program
The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values.

   a number; 
   b number; 
   c number; 
FUNCTION findMax(x IN number, y IN number)  
RETURN number 
    z number; 
   IF x > y THEN 
      z:= x; 
      Z:= y; 
   END IF;  
   RETURN z; 
   a:= 23; 
   b:= 45;  
   c := findMax(a, b); 
   dbms_output.put_line(' Maximum of (23,45): ' || c); 
When the above code is executed at the SQL prompt, it produces the following result
Maximum of (23,45): 45

PL/SQL Recursive Functions
We have seen that a program or subprogram may call another subprogram. When a subprogram calls itself, it is referred to as a recursive call and the process is known as recursion.
To illustrate the concept, let us calculate the factorial of a number. Factorial of a number n is defined as
n! = n*(n-1)! 
   = n*(n-1)*(n-2)! 
   = n*(n-1)*(n-2)*(n-3)... 1 
The following program calculates the factorial of a given number by calling itself recursively
   num number; 
   factorial number;  
FUNCTION fact(x number) 
RETURN number  
   f number; 
   IF x=0 THEN 
      f := 1; 
      f := x * fact(x-1); 
   END IF; 

   num:= 6; 
   factorial := fact(num); 
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); 

Factorial 6 is 720

Key Points

  • PL/SQL - Functions
  • Image