Loading

MySQL Function

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

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics