FUNCTION
PL-SQL
Creating a Function
A function is same as a procedure except that it returns a value.
A standalone function is created using the CREATE FUNCTION statement. The simplified
syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −
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,
• function-name specifies the name of the function.
• [OR REPLACE] option allows the modification of an existing function.
• 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.
• The function must contain a return statement.
• The RETURN clause specifies the data type you are going to return from the function.
• function-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone function.
Example
The following example illustrates how to create and call a
standalone function. This function returns the total number of
CUSTOMERS in the customers table.
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 |
CREATING FUNCTION
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
/
When the above code is executed using the SQL prompt, it will produce the
following result −
Function created.
Calling a Function
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Total no. of Customers: 6
PL/SQL procedure successfully completed.
Example
The following example demonstrates Declaring, Defining, and
Invoking a Simple PL/SQL Function that computes and returns the
maximum of two values.
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;
/
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
The following program calculates 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;
Output:
Factorial 6 is 720 PL/SQL procedure successfully completed.