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
// first lets create a table create table section(s_id int , s_name varchar(20), strength int );
// Inserting values into table insert into section values(1, 'computer science' , 20);
insert into section values(2, 'portal' , 45);
insert into section values(3, 'geeksforgeeks' , 60);
// Defining function create or replace function totalStrength // Defining return type return integer
as total integer:=0; begin // calculating the sum and storing it in total select sum(strength) into total from section; return total;
// closing function 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.
set serveroutput on; declare a int ;
c int ;
n int ;
rev int :=0;
r int ;
// Defining function function reverse_it( x IN int )
return int
as z int ;
// function code 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:
DECLARE num int ;
answer int ;
// Defining function 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:
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;
// pre-defined sqrt() to // calculate square root 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;