# Functions and Procedures

We can store procedures in the database and execute them using a **call** statement.

This allows external applications to operate on the database without knowing about the internal details.

Functions/procedures can be written in SQL itself, or in an external programming language.

SQL also supports a rich set of imperative constructs, including loops, if-then-else, assignment etc.

**For example**: Define a function that, given the name of a department, returns the count of the number of instructors in that department:

```
create function dept_count (dept_name varchar(20))
returns integer
begin
    declare d_count integer;
    select count (* ) into d_count
    from instructor
    where instructor.dept_name = dept_name
    return d_count;
end
```

Now, we can use the above function to find the department name and budget of all departments with more that 12 instructors:

```
select dept_name, budget
from department
where dept_count (dept_name ) > 12
```

The dept\_count function could instead be written as procedure:

```
create procedure dept_count_proc (in dept_name varchar(20), out d_count integer)
begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name
end
```

Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement:

```
declare d_count integer;
call dept_count_proc( 'Physics', d_count);
```

We can have more than one function/procedure with the same name (overloading), as long as the number/types of arguments is different.

## Table Functions

These functions return a relation as a result.

**For example**: Return all instructors from a given department

```
create function instructors_of (dept_name char(20)
returns table ( ID varchar(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))
return table
(select ID, name, dept_name, salary
 from instructor
 where instructor.dept_name = instructors_of.dept_name)
```

Usage: `select * from table (instructors_of (‘Music’))`