O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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) DETERMINISTICBEGIN 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 >= 90000) THEN ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required