Functions

Functions and procedures can be defined using SQL procedural extensions that allow iteration and conditional statements.

Example:

Following is a function developed, that is given a name of the department. It returns the count of the number of faculty in that department.

Create function dept_count (dept_name varchar2(20) )
returns  integer                          
begin
declare  d_count  integer ;
select count(FNo) into d_count
from FACULTY, DEPT
where FACULTY.DNo = DEPT.DNo    
and DName = dept_name
return  d_count ;
end

This function can be used in a query that returns DNo and DNames of all departments with more than 5 faculty.

Select DNo, Dname
From DEPT
Where dept_count(dept_name) > 5 ;

The SQL standard supports functions that can return tables as results; Such functions are called ‘table functions’.

Let a function returns a table containing faculty of ‘IT’ department, named ‘fac_of.dept_name’. 

This function can be used in query as follows :

select *
from table(fac_of(‘IT’) ) ;