Functions in MySQL

Lesson 89 of 96

In MySQL, a Function is similar to a Stored Procedure but with some key differences. Functions are used to perform calculations or operations and return a single value.

SQL 
-- Create function
DELIMITER &&

DROP FUNCTION IF EXISTS GetCountryNameByCode&&
CREATE FUNCTION GetCountryNameByCode
(
    inputCode VARCHAR(50)
)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN 
    DECLARE CName VARCHAR(50);
    SELECT C.name INTO CName 
    FROM Country C
    WHERE C.Code = inputCode;
    RETURN CName;
END &&
DELIMITER ;
Bash 
--Calling function
select world.GetCountryNameByCode('AGO');

In MySQL, unlike SQL Server, there isn't direct support for Table-Valued Functions (TVFs) like you would have in other RDBMS such as Microsoft SQL Server. However, you can achieve similar functionality using Stored Procedures or by selecting from Views or Derived(sub-query) Tables.