What are MySQL stored functions and how to create them?

MySQL stored function is basically a special kind of stored program that returns a single value. We can use stored functions in MySQL to encapsulate mainly simple formulas or business rules that are reusable among SQL statements or stored programs. Other than that stored functions are used in SQL statements whenever an expression is used.

This feature of stored functions is different from stored procedures. Actually, a stored function parameter is equivalent of the IN parameter of the stored procedure as the functions use RETURN keyword to determine what is passed back. Its syntax can be as follows −

Syntax

CREATE
   [DEFINER = { user | CURRENT_USER }]
   FUNCTION sp_name ([func_parameter[,...]])
   RETURNS type
   [characteristic ...] routine_body

func_parameter:
   param_name type

type:
   Any valid MySQL data type

characteristic:
   COMMENT 'string'
 | LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }

routine_body:
   Valid SQL routine statement

The function has only input parameters and return value, so in There must be a RETURNS clause in the function definition to indicate the data type of the return value. Additionally, there must be at least one RETURN statement within the function body to return a value to the caller.

The above is the detailed content of What are MySQL stored functions and how to create them?. For more information, please follow other related articles on the PHP Chinese website!