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.
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...