Functions in PL/SQL
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
// 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.
C
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:
C
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:
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; // 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;
Please Login to comment...