Functions

SQL functions fill the same role as functions in any other language. They allow you to generalise a set of queries. You can enter a parameter into a function and the function will fit it into the query.

Example

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

It is also possible to return a table with a function, although this isn't supported in MySQL.

CREATE FUNCTION...
returns TABLE(
    ID varchar(5)
    name varchar(20)
    dept_name varchar(20)
    salary numeric(8,2)
)
return TABLE(<QUERY>)