Stored Functions

In MySQL, a stored function is a set of SQL statements that perform some operation and return a single value. Unlike stored procedures, stored functions can be used in SQL statements wherever an expression is used.

Here is the general syntax for creating a function:

CREATE FUNCTION function_name([parameter[, ...]])
RETURNS type
    [characteristic ...] 
routine_body

In the syntax:

function_name: is the name of the function.
parameter: is the list of parameters.
type: is the data type of the return.
characteristic: determines the characteristics of the function. It could be LANGUAGE SQL, DETERMINISTIC, SQL DATA ACCESS {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}, COMMENT 'string', etc.
routine_body: is the block of statement to execute when the function is invoked.

Here is a simple example of a stored function that calculates the total salary for a specific department:

CREATE FUNCTION total_salary(dept_name VARCHAR(20))
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA ACCESS
BEGIN
   DECLARE total DECIMAL(10,2);
   SELECT SUM(salary) INTO total FROM employees WHERE department = dept_name;
   RETURN total;
END;

In this example, a function named total_salary is created. It takes one parameter, dept_name. The function calculates the total salary of the department specified by dept_name and returns that value.

You could use this function in a SQL statement like this:

SELECT total_salary('Engineering');

This would return the total salary for the Engineering department.

Similar to stored procedures, using stored functions can help you encapsulate logic for reuse and improve the efficiency and manageability of your applications. It's also important to remember that since functions can be used in SQL expressions, their execution should be fast and should not have side effects.