# 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 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
```
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`  `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
```
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;`` ` `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

```