Loading

MySQL Store Function

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

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics