Example of Create Function in MySQL

Syntax of creating the function in MySQL:

Function has only IN parameter, it take only as input.
Return specify the return value from the Function.
MySQL Default is Non-deterministic,
Meaning of a deterministic function always returns the same result for the same input parameters.
Meaning of a non-deterministic function returns different results for the same input parameters.

--Create Function 
DELIMITER ||
CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype
[NOT] DETERMINISTIC
BEGIN
   declaration_section
   executable_section
END; 
||
DELIMITER ;

----Calling or execution  function 
SELECT function_name(parameter);

Example of creating function in MySQL

DELIMITER ||
CREATE FUNCTION example_date() RETURNS date DETERMINISTIC
BEGIN
 DECLARE date2 DATE;
  Select current_date()into date2;
  RETURN date2;
END ;
||
DELIMITER ;

--Execute the function example_date

SELECT example_date();
+----------------+
| example_date() |
+----------------+
| 2021-06-12     |
+----------------+
1 row in set (0.01 sec)

Example of Creating Function:

DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT DETERMINISTIC
BEGIN
RETURN 100;
END;
||
DELIMITER ;

Check Function present in MySQL

Syntax:
SHOW function STATUS [LIKE 'pattern' | WHERE search_condition]  

Example:
SHOW function STATUS WHERE db = 'TEST_BKP';  

Check the DDL of Function in MySQL

SHOW CREATE FUNCTION functionname \G;  

mysql> SHOW CREATE FUNCTION functionname \G;
*************************** 1. row ***************************
            Function: functionname
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `functionname`() RETURNS int
    DETERMINISTIC
BEGIN
RETURN 100;
END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

Drop the Function in MySQL

DROP FUNCTION [ IF EXISTS ] function_name;
Example:
mysql> drop function example_date;
Query OK, 0 rows affected (0.03 sec)