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:

1. 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.
2. We can divide the overall work into small modules which becomes quite manageable also enhancing the readability of the code.
3. It promotes reusability.
4. 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;