MySQL in a Nutshell, 2nd Edition

Name

CREATE FUNCTION

Synopsis

CREATE
[DEFINER = {'user'@'host'|CURRENT_USER}]
FUNCTION function ([parameter data_type[,...]])
RETURNS data_type
  [LANGUAGE SQL]
  [[NOT] DETERMINISTIC]
  [{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
  [COMMENT 'string']
  [SQL SECURITY {DEFINER|INVOKER}]
RETURN routine

A user-defined function is essentially a set of SQL statements that may be called as a unit, processing any data it’s given in its parameters and returning a value to the caller of the function. This is similar to a stored procedure, except that a function returns a value and a stored procedure does not. A stored procedure normally places the values it generates in user variables that can then be retrieved in various ways.

The basic, minimum syntax is something like this:

CREATE FUNCTION function_name (parameter) RETURNS INT RETURN routine

The function name given can be any nonreserved name; don’t use the name of a built-in function. The name is case-insensitive. Within parentheses, give a comma-separated list of the parameters. For each parameter, specify the data type to be used (INT, CHAR, etc.). The keyword RETURNS is followed by the data type of the value that will be returned by the function. At the end comes the keyword RETURN followed by the routine to perform.

You may provide special parameters to indicate the characteristics of the function. Several may be given in any order, in a space-separated list. You can specify the language used as SQL with the LANGUAGE SQL parameter, ...