SQL Functions

Lecture 6 · a stored function in MySQL is a reusable program stored in the database that returns exactly one value (number, text, date). It can be used inside SQL like SELECT, WHERE, JOIN, etc.

Functions and procedures in the standard

SQL:1999 supports functions and procedures.
Functions/procedures can be written in SQL itself, or in an external programming language (e.g. C, Java).
Functions written in external languages are particularly useful with specialized data types such as images and geometric objects — e.g. functions to check if polygons overlap, or to compare images for similarity.
Some database systems support table-valued functions, which return a relation as a result.
SQL:1999 also supports a rich set of imperative constructs: loops, if-then-else, assignment.
Many databases have proprietary procedural extensions that differ from SQL:1999 (PL/SQL, T-SQL, MySQL’s own dialect).

Syntax

CREATE FUNCTION

DELIMITER $$

CREATE FUNCTION function_name (param1 datatype, param2 datatype, ...)
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA | NO SQL]
BEGIN
    -- declarations (optional)
    -- statements
    RETURN value;
END$$

DELIMITER ;

Key points

RETURNS … defines the return type.
Must have one RETURN statement.
Usually use DETERMINISTIC if the same input always gives the same output.
Use READS SQL DATA if it reads tables, NO SQL if it doesn’t access tables.

Characteristic	Means	Note
`DETERMINISTIC`	The same input always gives the same output.	Required by MySQL when binary logging is on, unless you declare NOT DETERMINISTIC.
`NO SQL`	The body does not access any table.	e.g. a pure arithmetic function like yearly_salary().
`READS SQL DATA`	The body reads tables but does not modify them.	e.g. get_salary() doing a SELECT … INTO.
`MODIFIES SQL DATA`	The body writes to tables.	Usually a sign you wanted a PROCEDURE, not a FUNCTION.

Example 1 — annual salary from monthly salary

yearly_salary

DELIMITER $$

CREATE FUNCTION yearly_salary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
NO SQL
BEGIN
    RETURN monthly_salary * 12;
END$$

DELIMITER ;

-- use it
SELECT yearly_salary(50000) AS annual_salary;   -- → 600000.00

Example 2 — return an employee’s salary by id

get_salary

-- Sample table
CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    name   VARCHAR(50),
    salary DECIMAL(10,2)
);
INSERT INTO employee VALUES (1,'Rahim',50000), (2,'Karim',60000);

DELIMITER $$

CREATE FUNCTION get_salary(p_emp_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_salary DECIMAL(10,2);

    SELECT salary INTO v_salary
    FROM employee
    WHERE emp_id = p_emp_id;

    RETURN v_salary;
END$$

DELIMITER ;

-- use it
SELECT emp_id, name, get_salary(emp_id) AS salary
FROM employee;

Note the naming convention p_emp_id for the parameter — if the parameter had the same name as the column, the WHERE clause would compare the column with itself and return every row.

Example 3 — count instructors in a department

dept_count

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

-- department names and budget of all departments with more than 12 instructors
select dept_name, budget
from department
where dept_count(dept_name) > 12;

This is the pattern to remember: a scalar function used inside a WHERE clause, evaluated once per row of department.

Table functions (SQL:2003)

SQL:2003 added functions that return a relation as a result.

table function

create function instructor_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 = instructor_of.dept_name);

-- usage
select *
from table (instructor_of('Music'));

A table function is essentially a parameterised view — a view you can pass an argument to.

Function vs procedure vs trigger — say this in the viva. A function returns one value and is called inside an expression. A procedure may return many values (OUT parameters) and is called with CALL. A trigger is never called by you at all — the DBMS fires it automatically on an INSERT/UPDATE/DELETE event.

Previous: Triggers → · Exam practice: Final Spring 2026 Q5 →