Mysql Provides a lot of inbuilt MySQL functions. But MySQL also provides CREATE FUNCTION syntax for creating loadable programs of MySQL Function.
Syntax:- CREATE [AGGREGATE] FUNCTION [IF NOT EXISTS] function_nam RETURNS {STRING|INTEGER|REAL|DECIMAL} SONAME shared_library_name
CREATE FUNCTION requires the INSERT privilege for the mysql system schema because it adds a row to the MySQL.func system table to register the function.
CREATE FUNCTION also adds the function to the Performance Schema user_defined_functions table that provides runtime information about installed loadable functions.
CREATE FUNCTION is also used for the Stored function. So we can say, the MySQL function is a stored program that you can pass parameters into and then return a value.
Example:-
DELIMITER$$
CREATE FUNCTION Salarycalculation ( base_salary INT )
RETURNS INT
BEGIN
DECLARE salary INT;
SET salary = 0;
label1: WHILE salary <= 5000 DO
SET salary = salary + base_salary;
END WHILE label1;
RETURN salary;
END$$
DELIMITER ;
For calling this function:-
SELECT Salarycalculation(2000);
For Drop this function:-
DROP FUNCTION Salarycalculation();
For Show all functions from the current database:-
SHOW FUNCTION STATUS WHERE db = ‘databse_name’;
Read Also:–
MySQL Store Function
MySQL Procedure
MySQL Triggers
MySQL View
For more detail about MySQL Create function please read MySQL manual
