MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminUser 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’;
Web development and Automation testing
solutions delivered!!