User-defined functions (UDFs) in MySQL are procedural database objects similar to stored procedures. Both consist of SQL statements and procedural code that can be invoked by aplications or SQL statements. However, key differences exist between them:

1. User-defined functions cannot have output parameters since the function itself acts as the output; stored procedures can have output parameters.
2. User-defined functions must contain a RETURN statement, which is not allowed in stored procedures.
3. User-defined functions can be called direct without the CALL keyword, while stored procedures require CALL.
4. Stored procedures can have zero or multiple return values, making them suitable for batch insert/update operations.
5. Functions must return exactly one value, making them suitable for data processing that returns a single result.

Creating and Using User-Defined Functions

The CREATE FUNCTION statement creates user-defined functions with the following syntax:

CREATE FUNCTION <function_name> ( [ <param1> <type1> [ , <param2> <type2> ] ] … )
  RETURNS <return_type>
  <function_body>

Examples

-- 1. Creating a function without parameters (retrieves the maximum ID from the user table)
-- create function getUserMaxId()
-- returns int(11) deterministic
-- RETURN (SELECT max(id) from user);

-- 2. Calling getUserMaxId()
-- SELECT getUserMaxId();

-- 3. Creating a parameterized function
-- Requirement: custom nvl function - return second parameter when first is NULL, otherwise return first parameter
-- CREATE FUNCTION nvl(str1 varchar(4000), str2 varchar(4000))
-- RETURNS VARCHAR(4000) DETERMINISTIC
-- return COALESCE(str1, str2);

-- 4. Using the custom nvl function
-- set @str1 = "China";
-- set @str2 = "default";
-- SELECT nvl(@str1, @str2);


-- 5. Using IF NOT EXISTS prevents errors when the function already exists
CREATE FUNCTION IF NOT EXISTS test.get_total(username VARCHAR(20))
RETURNS DECIMAL(10,2) deterministic
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(score * 10) INTO total FROM user WHERE username = username;
    RETURN total;
END;

Note: Returning a TABLE type from a stored function has not been successfully validated in MySQL v5.7 or v8.0. The following syntax error occurs:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE
BEGIN
    DECLARE result_table TABLE (
        id INT,
        name VARCHA' at line 2

Viewing User-Defined Functions

1. View All User-Defined Functions

SHOW FUNCTION STATUS;

2. Filter Functions by Database Using WHERE

SHOW FUNCTION STATUS WHERE Db = 'database_name';

SHOW FUNCTION STATUS where Db="test"

3. Filter Functions by Name Pattern Using LIKE

SHOW FUNCTION STATUS LIKE '%keyword%';

SHOW FUNCTION STATUS like "%nvl%"

Modifying User-Defined Functions

The ALTER FUNCTION statement modifies certain characteristics of user-defined functions. To change the function body itself, the function must be droppped and recreated.

Note: ALTER FUNCTION modification attempts have failed in MySQL versions 5.7 and 8.0:

-- Creating the stored function (success)
-- CREATE FUNCTION test.get_total(username VARCHAR(20))
-- RETURNS DECIMAL(10,2) deterministic
-- BEGIN
--     DECLARE total DECIMAL(10,2);
--     SELECT SUM(score * 10) INTO total FROM user WHERE username = username;
--     RETURN total;
-- END;

-- Attempting to modify the stored function (fails)
ALTER FUNCTION get_total()
RETURNS DECIMAL(10,2) deterministic
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(score * 10) INTO total FROM user;
    RETURN total;
END;


-- Error returned:
-- 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()
-- RETURNS DECIMAL(10,2) deterministic
-- BEGIN
--     DECLARE total DECIMAL(10,2)' at line 1

-- Calling the stored function (success)
-- SELECT test.get_total('mark');

Deleting User-Defined Functions

Syntax:

DROP FUNCTION [ IF EXISTS ] <function_name>

Parameters:

1. <function_name>: The name of the user-defined function to delete.
2. IF EXISTS: Optional keyword that prevents errors when attempting to delete a non-existent function.

drop function IF EXISTS function_name;

Viewing Function Definitions

SHOW CREATE FUNCTION function_name;

-- View the definition of the nvl function
SHOW CREATE FUNCTION nvl;
-- Returns the Create Function field:
CREATE DEFINER=`root`@`localhost` FUNCTION `nvl`(str1 varchar(4000), str2 varchar(4000)) RETURNS varchar(4000) CHARSET utf8mb4 DETERMINISTIC
return coalesce(str1, str2)