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
Syntax
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name]; You must have define some parametrs before creating a procedure or a function. These parameters are
- IN: It is a default parameter. It passes the value to the subprogram.
- OUT: It must be specified. It returns a value to the caller.
- IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.
Oracle Function Example
Let’s see a simple example to create a function.
create or replace function adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/ Now write another program to call the function.
DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/ Output:
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.
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;
/ Output:
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.
| Id | Name | Department | Salary |
|---|---|---|---|
| 1 | alex | web developer | 35000 |
| 2 | ricky | program developer | 45000 |
| 3 | mohan | web designer | 35000 |
| 4 | dilshad | database manager | 44000 |
Create Function:
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
/After the execution of above code, you will get the following result.
Function created.
Calling Oracle Function:
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/ 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.
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;
/ 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.
Syntax:
DROP FUNCTION function_name;
Leave a Reply