Functions in PL/SQL
A 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 to create 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 tablecreate table section(s_idint, s_name varchar(20), strengthint);// Inserting values into tableinsert into section values(1,'computer science', 20);insert into section values(2,'portal', 45);insert into section values(3,'geeksforgeeks', 60);// Defining functioncreate or replace function totalStrength// Defining return typereturnintegerastotal integer:=0;begin// calculating the sum and storing it in totalselect sum(strength) into total from section;returntotal;// closing functionend totalStrength;set serveroutput on;declareanswer integer;beginanswer:=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;declareaint;cint;nint;revint:=0;rint;// Defining functionfunction reverse_it( x INint)returnintaszint;// function codebeginn := x;while(n > 0)loopr := mod(n, 10);rev := (rev * 10) + r;n := trunc(n / 10);end loop;z := rev;returnz;end ;BEGINa := 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:DECLAREnumint;answerint;// Defining functionFUNCTION factorial(x number)RETURNintISfint;BEGINIF x = 0 THENf := 1;ELSEf := x * factorial(x - 1);END IF;RETURN f;END;BEGINnum := 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 exception block in functions but exception handling using try-catch block cannot be done.
Example:
set serveroutput on;declareaint;bfloat;myexp exception;function sqroot(xint)returnfloatasanswerfloat;beginifx < 0 thenraisemyexp;// pre-defined sqrt() to// calculate square rootelseanswer := SQRT(x);endif;returnanswer;exceptionwhen myexp thendbms_output.put_line('square of negative number isnot allowed so returning the samenumber');returnx;end;beginb := 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.
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.




