# 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:

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

```

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.

My Personal Notes arrow_drop_up Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.

Article Tags :
Practice Tags :

3

Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.