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’))
Last updated
Was this helpful?