0
0
jaggy
answered Aug 23 '21 00:00
What is function in MySQL ?the function is the already made list of actions/tasks that takes some input and return output in MySQL
Functions can return string, integer, or real values and can accept arguments of those same types in MySQL
What is syntax to create function in MySQL ?
CREATE FUNCTION name_of_function [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype
BEGIN
declaration_section
executable_section
END;
CREATE FUNCTION statement create a function
name_of_function is the name of the function which we gonna create .
RETURNS define the type of return value
in between the BEGIN and END, the function code goes there. in the first part, can declare the variables, and next section you can put all executable code.
for example :
function to calculate the percentage of two values so let's make a function for it.
DELIMITER //
CREATE FUNCTION GetPercentage ( first_value INT , second_value INT )
RETURNS INT
BEGIN
DECLARE income INT;
SET income =(first_value/second_value)*100;
RETURN income;
END; //
DELIMITER ;
Let me explain above all code for creating MySQL custom function :
DELIMITER change delimiter . delimiter define the where to end statement in MYSQL
DELIMITER //
DELIMITER // will change the delimiter ; to //
and now CREATE FUNCTION specify the function name and parameters, here we passing two integer values and
CREATE FUNCTION GetPercentage ( first_value INT , second_value INT )
RETURNS define the return type of the function in Mysql
RETURNS INT
at with BEGIN, main function code starts
BEGIN
DECLARE income INT;
SET income =(first_value/second_value)*100;
RETURN income;
END;
DECLARE will define the new variables in the function in MYSQL.
SET will set the value to the variable in MYSQL.
RETURN will return the output . in our case, it should be an integer value.
at with END, the main function code ends.
at end of the code change the delimiter to; again.
DELIMITER ;
now how to call already created function ?
try to call function with select statement and pass on the argument as per need
select GetPercentage(12,12)
if you do all types of operations with custom functions as per need in MySQL, suppose like if percentage greater than >100 then return data
select * from datatable where GetPercentage(data1,data2)>100