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’;

You may also like...