In this part, we will examine the capacities in PL/SQL. A capacity is same as a methodology aside from that it restores a worth. Hence, all the conversations of the past part are valid for capacities as well.
Creating a Function
An independent capacity is made utilizing the CREATE FUNCTION explanation. The improved grammar for the CREATE OR REPLACE PROCEDURE explanation is as per the following −
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Where,
- work name determines the name of the capacity.
- [OR REPLACE] alternative permits the alteration of a current capacity.
- The discretionary boundary list contains name, mode and sorts of the boundaries. IN addresses the worth that will be passed from outside and OUT addresses the boundary that will be utilized to restore an incentive outside of the methodology.
- The capacity should contain a bring proclamation back.
- The RETURN proviso determines the information type you will get back from the capacity.
- work body contains the executable part.
- The AS catchphrase is utilized rather than the IS watchword for making an independent capacity.
Model
The accompanying model outlines how to make and call an independent capacity. This capacity restores the all out number of CUSTOMERS in the clients table.
We will utilize the CUSTOMERS table, which we had made in the PL/SQL Variables section −
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 |
+----+----------+-----+-----------+----------+
Make OR REPLACE FUNCTION totalCustomers
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
/
At the point when the above code is executed utilizing the SQL brief, it will deliver the accompanying outcome −
Function created.
Calling a Function
While making a capacity, you give a meaning of what the capacity needs to do. To utilize a capacity, you should call that capacity to play out the characterized task. At the point when a program calls a capacity, the program control is moved to the called work.
A called work plays out the characterized task and when its return articulation is executed or when the last end proclamation is reached, it restores the program control back to the fundamental program.
To call a capacity, you basically need to pass the necessary boundaries alongside the capacity name and on the off chance that the capacity restores a worth, at that point you can store the brought an incentive back. Following system calls the capacity totalCustomers from an unknown square −
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Total no. of Customers: 6
PL/SQL procedure successfully completed.
Example
The accompanying model exhibits Declaring, Defining, and Invoking a Simple PL/SQL Function that processes and returns the limit of two qualities.
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;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
PL/SQL Recursive Functions
We have seen that a program or subprogram may call another subprogram. At the point when a subprogram calls itself, it is alluded to as a recursive call and the cycle is known as recursion.
To delineate the idea, let us ascertain the factorial of a number. Factorial of a number n is characterized as −
n! = n*(n-1)!
= n*(n-1)*(n-2)!
...
= n*(n-1)*(n-2)*(n-3)... 1
The accompanying project figures the factorial of a given number by calling itself recursively −
DECLARE
num number;
factorial number;
FUNCTION fact(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;
BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Factorial 6 is 720
PL/SQL procedure successfully completed.