User defined functions in MySQL

Function is just like procedure but only difference is the it returns a value so we can use returned value in SQL queries. Use below syntax to create a user defined function. We need to change the delimiter as function would contain multiple statements.

 
delimiter “$$”;

Do not use reserved keywords as names of functions.

 
delimiter $$
Create function addno(a INT, b INT)
Returns INT Deterministic
BEGIN
          Declare c INT;
         Set c=(a+b);
         Return c;
END$$
delimiter ;

Calling a Function

This is how we can call the function.

 
select addno(2,3);

-- Another example on Function

USE `deals`;

DROP function IF EXISTS `getCustomerName`;

DELIMITER $$

USE `deals`$$

CREATE FUNCTION `getCustomerName` (id bigint)

RETURNS VARCHAR(30)

BEGIN

DECLARE tempFirstName VARCHAR(30);

SELECT FirstName INTO tempFirstName FROM Customer WHERE CustomerId = id;

RETURN COALESCE(tempFirstName, ‘Customer not found’);

END$$

DELIMITER ;

You can call this function using below syntax.

select getCustomerName(1);

MySQL function to calculate the rectangle area

 
CREATE DEFINER=`root`@`localhost` FUNCTION `getRectangleArea`(h DOUBLE, w DOUBLE)
RETURNS double

BEGIN

DECLARE area DOUBLE;

SET area = h*w;

RETURN area;

END

select getrectanglearea(2.1,2.3)

Viewing user defined functions To view user defined functions, you can use below syntax

 
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=’FUNCTION’ AND ROUTINE_SCHEMA=’YOUR_DB_NAME’;