Working with user-defined functions

MySQL has many built-in functions that you can call to return values or perform tasks on data, including CURRENT_DATE(), AVG(), SUM(), ABS(), and CONCAT(). These functions can be used in SQL statements, views, and stored procedures. MySQL also has another type of function, known as the user-defined function (UDF), that you can create to add new functionality to the database that is not already provided by MySQL. For example, you may want a function that can calculate and return the GST or sales tax or maybe calculate the income tax for your weekly earnings. A UDF is active when it is loaded into the database with CREATE FUNCTION and hasn't been removed with DROP FUNCTION. A function can be used while it is active.

The basic syntax for creating a UDF is as follows:

USE database_name;
DROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION function_name ([parameter(s)])
 RETURNS data type
 DETERMINISTIC
 STATEMENTS

Let's look at each...