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, but this is the default and usually unnecessary.

A function that returns the same results each time for the same given parameters is considered deterministic. You can save processing time on the server by specifying this property through the DETERMINISTIC parameter. NOT DETERMINISTIC is the default.

The following keywords may be used to tell the server how the function will interact with it, allowing the server to optimize the function. The server does not enforce the restrictions on the function, however:

CONTAINS SQL: The function executes SQL statements, but does not read from or write to a table; one example is a function that queries server status. This is the default.
NO SQL: The function does not contain any SQL statements.
READS SQL DATA: The function might read data from at least one table, but it doesn’t write data to any tables.
MODIFIES SQL DATA: The function might write data to at least one table, as well as potentially read data from tables.

With the COMMENT clause, you can add a comment describing the function for future reference.

This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and host account that creates or alters a routine, by default. With the DEFINER clause, you can specify the MySQL user and host to be used by MySQL for the function. Related to this clause is SQL SECURITY keyword, which instructs MySQL to use either the user account of the creator (DEFINER) of the function or the account that’s calling the function (INVOKER). This can help to prevent some users from accessing restricted functions.

Here is an example using this statement:

CREATE FUNCTION date_reformatted (new_date VARCHAR(13))
RETURNS DATE
RETURN STR_TO_DATE(REPLACE(new_date, '.', ''), '%b %d, %Y');

SELECT date_reformatted('Dec. 7, 2007') 
AS proper_date;

+-------------+
| proper_date |
+-------------+
| 2007-12-07  |
+-------------+

This function simply uses the STR_TO_DATE() function to convert a string to a particular date format (i.e., yyyy-mm-dd) based on a common string that users may give. It expects the data given to be no more than 13 characters long. Because some users may include a period after the abbreviated month name and some may not, the function uses the REPLACE() function to remove the period. A function like this one can be used in any type of statement (e.g., an UPDATE statement to set a column value).

To change an existing user-defined function, use the ALTER FUNCTION statement. The DROP FUNCTION statement removes a user-defined function. You cannot change standard, built-in functions.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition