MySQL Store Function is a part of MySQL stored program. It is a set of SQL statements that perform some specific task and return a single value.
Syntax:-
DELIMITER $$
CREATE FUNCTION fun_name(para1,para2,…)
RETURN datatype
[NOT] characteristics
BEGIN
//your statements
END $$
DELIMITER;
Here characteristics are DETERMINISTIC, NO SQL, or READS SQL DATA
The store function can call directly.
It can be used with Procedures or Triggers to improve readability and maintainability.
we can see Store Function in the MySQL workbench under the Function section
Also, we can see all functions in our database from the following query:-
SHOW FUNCTION STATUS WHERE db = ‘dbname’;
For Example:-
Suppose we have a “Student” table in our database, Which field is Name, age, class, Section
Now I am writing a Store Function which will return class based on age.
DELIMITER $$
CREATE FUNCTION Student_class(
age int
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE student_class VARCHAR(20);
IF age > 5 THEN
SET student_class = ‘First’;
ELSEIF (age <= 7 AND
age >= 5) THEN
SET student_class = ‘Second’;
ELSEIF age < 10 THEN
SET student_class = ‘Third’;
END IF;
— return the student_class
RETURN (student_class);
END$$
DELIMITER;
For calling this function we can use:-
SELECT name, age, Student_class (age) FROM Students ORDER BY age;
This function can be called in a stored procedure:-
DELIMITER $$
CREATE PROCEDURE GetStudentDetail()
BEGIN
SELECT name, age, Student_class (age) FROM Students ORDER BY age;
END$$
DELIMITER
For calling the above Procedure:-
CALL GetStudentDetail();
For Drop function Syntax:-
DROP FUNCTION [ IF EXISTS ] function_name;
In the case of our Function:-
DROP FUNCTION Student_class;
Read Also:–
MySQL Function
MySQL Procedure
MySQL Triggers
MySQL View
For more detail about MySQL Create function please read MySQL manual
