Functions

Just like stored procedures, you can create stored functions. The main difference is functions should have a return value and they can be called in SELECT. Usually, stored functions are created to simplify complex calculations.

How to do it...

Here is an example of how to write a function and how to call it. Suppose a banker wants to give a credit card based on income level, instead of exposing the actual salary, you can expose this function to find out the income level:

shell> vi function.sql;
DROP FUNCTION IF EXISTS get_sal_level;
DELIMITER $$
CREATE FUNCTION get_sal_level(emp int) RETURNS VARCHAR(10)
 DETERMINISTIC
BEGIN
 DECLARE sal_level varchar(10);
 DECLARE avg_sal FLOAT;

 SELECT AVG(salary) INTO avg_sal FROM salaries WHERE emp_no=emp;

 IF avg_sal < 50000 THEN
 SET sal_level = 'BRONZE';
 ELSEIF (avg_sal >= 50000 AND avg_sal < 70000) THEN
 SET sal_level = 'SILVER';
 ELSEIF (avg_sal >= 70000 AND avg_sal < 90000) THEN
 SET sal_level = 'GOLD';
 ELSEIF (avg_sal...