Functions in PL/SQL

• Last Updated : 05 Apr, 2018

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:

1. 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_id int, s_name varchar(20), strength int );  // 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 typereturn integerastotal integer:=0;  begin                          // calculating the sum and storing it in total select sum(strength) into total from section;return total;  // 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

2. 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  intasz int;  // function codebegin    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

3. 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

4. Exception handling can be done using exception block in functions but exception handling using try-catch block cannot be done.

Example:

 set serveroutput on;  declarea int;b float;myexp exception;  function  sqroot(x int)return float  asanswer float;  begin  if x < 0 thenraise myexp;  // pre-defined sqrt()  to // calculate square root elseanswer := SQRT(x);   end if;return answer;  exceptionwhen myexp thendbms_output.put_line('square of negative number is                       not allowed so returning the same                      number');return x;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