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’))