Function can be used as a part of SQL expression i.e. we can use them with select/update/merge commands. One most important characteristic of a function is that, unlike procedures, it must return a value.
Syntax: Creating a function
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name type [, ...])]
// this statement is must for functions
RETURN return_datatype
{IS | AS}
BEGIN
// program code
[EXCEPTION
exception_section;
END [function_name];
Example:
We have to find the total strength of students using functions present in different sections in a school
C
create table section(s_id int , s_name varchar(20), strength int );
insert into section values(1, 'computer science' , 20);
insert into section values(2, 'portal' , 45);
insert into section values(3, 'geeksforgeeks' , 60);
create or replace function totalStrength
return integer
as
total integer:=0;
begin
select sum(strength) into total from section;
return total;
end totalStrength;
set serveroutput on;
declare
answer integer;
begin
answer:=totalstrength();
dbms_output.put_line( 'Total strength of students is ' || answer);
end;
|
Output:
Total strength of students is 125
Now, let’s take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL function which will compute and return the reverse of a number.
C
set serveroutput on;
declare
a int ;
c int ;
n int ;
rev int :=0;
r int ;
function reverse_it( x IN int )
return int
as
z int ;
begin
n := x;
while (n > 0)
loop
r := mod(n, 10);
rev := (rev * 10) + r;
n := trunc(n / 10);
end loop;
z := rev;
return z;
end ;
BEGIN
a := 123456789;
c := reverse_it(a);
dbms_output.put_line( 'the reverse of number is ' || c);
END;
|
Output:
the reverse of number is 987654321
Lets implement a recursive function to calculate the factorial of a number Recursive functions example:
C
DECLARE
num int ;
answer int ;
FUNCTION factorial(x number)
RETURN int
IS
f int ;
BEGIN
IF x = 0 THEN
f := 1;
ELSE
f := x * factorial(x - 1);
END IF;
RETURN f;
END;
BEGIN
num := 5;
answer := factorial(num);
dbms_output.put_line( ' Factorial of ' || num || ' is ' || answer);
END;
|
Output:
Factorial of 5 is 120
Exception handling can be done using an exception block in functions but exception handling using a try-catch block cannot be done. Example:
C
set serveroutput on;
declare
a int ;
b float ;
myexp exception;
function sqroot(x int )
return float
as
answer float ;
begin
if x < 0 then
raise myexp;
else
answer := SQRT(x);
end if ;
return answer;
exception
when myexp then
dbms_output.put_line('square of negative number is
not allowed so returning the same
number');
return x;
end;
begin
b := sqroot(-2);
dbms_output.put_line( 'the value is ' || b);
end;
|
Output:
square of negative number is not allowed so returning the same number
the value is -2
Advantages:
- We can make a single call to the database to run a block of statements thus it improves the performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
- We can divide the overall work into small modules which becomes quite manageable also enhancing the readability of the code.
- It promotes reusability.
- It is secure since the code stays inside the database thus hiding internal database details from the application(user). The user only makes a call to the PL/SQL functions. Hence security and data hiding is ensured.
Tip: Also do remember what is meant by Dropping a function. SO in order to drop a function, DROP function statement is used.
Syntax: DROP function statement
DROP Function <function_name>;
Example:
DROP Function func1;
Last Updated :
19 May, 2022
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...