CREATE FUNCTION

Description

The CREATE FUNCTION statement is used to create a stored function.

Privilege requirements

You must have the CREATE FUNCTION privilege to use the CREATE FUNCTION statement.

Here is an example:

After granting the CREATE ROUTINE privilege to a user, the user can create a FUNCTION.

GRANT CREATE ROUTINE ON my.* TO mingye;
DELIMITER //
CREATE FUNCTION db.add_numbers(a INT, b INT)
    -> RETURNS INT
    -> BEGIN
    -> RETURN a + b;
    -> END //

By default, after you create a PROCEDURE, seekdb automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine (stored procedure or function) creator. If the DEFINER clause is specified, the required privileges depend on the value of user.

Syntax

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] 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

By default, a stored function is associated with the default database. To associate a stored function with a specified database, use database_name.sp_name to specify the name.

To call a stored function, you can reference the function in an expression. The function returns a value during expression evaluation.

The DEFINER and SQL SECURITY clauses specify the security context to use for checking access privileges during routine execution.

The IF NOT EXISTS clause prevents the creation of a routine with the same name as an existing routine. If the routine name is the same as that of an internal SQL function, a syntax error occurs unless you use a space between the routine name and the subsequent parentheses when defining or calling the routine. Therefore, avoid using the names of existing SQL functions to create your own stored routines.

The proc_parameter parameter list in parentheses must always be specified. If no parameters are specified, use empty parentheses () to indicate an empty parameter list. Parameter names are case-insensitive. Each parameter of a stored function is an IN parameter.

The RETURNS clause is used only for stored functions to specify the return type of the function. The function body must contain a RETURN value statement. If the RETURN statement returns a value of a different type, the value is implicitly converted to the appropriate type. For example, if the RETURNS clause specifies an ENUM or SET value, but the RETURN statement returns an integer, the value returned by the function is the string representation of the corresponding ENUM member in the SET member collection.

Parameter types and function return types can be declared as any valid data type, and the COLLATE attribute can be used if the CHARACTER SET is specified.

The routine_body consists of valid SQL statements. SQL statements can be simple statements such as SELECT or INSERT, or compound statements written using BEGIN and END. Compound statements can include declarations, loops, and other control structure statements. In practice, stored functions tend to use compound statements unless the body consists of a single RETURN statement.

The USE statement cannot be used in stored routines. When a routine is called, the USE database_name statement is implicitly executed (and revoked when the routine terminates), specifying the default database for executing the routine. If you need to reference objects from a database other than the default database of the routine, you can qualify the object name with the database name.

If a routine is defined in strict SQL mode but is called in non-strict mode, the parameters of the routine are not assigned values in strict mode. If expressions are assigned to a routine in strict SQL mode, the routine must be called in strict mode.

The COMMENT characteristic can be used to describe stored routines. You can use the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements to display comment information.

The LANGUAGE characteristic specifies the language in which the routine is written. This characteristic is only applicable to SQL routines, and the server ignores it.

A routine is considered "deterministic" if it always produces the same result for the same input parameters. Otherwise, it is considered "non-deterministic". If neither the DETERMINISTIC nor the NOT DETERMINISTIC characteristic is specified in the routine definition, the default is NOT DETERMINISTIC. To declare a function as deterministic, you must explicitly specify DETERMINISTIC.

The following table describes the characteristics of data used by routines:

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default value. For example, SET@x=1 or DO RELEASE_LOCK('abc') are executed but do not read or write data.

• NO SQL indicates that the routine does not contain SQL statements.

• READS SQL DATA indicates that the routine contains statements that read data (such as SELECT) but do not write data.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (such as INSERT or DELETE).

The SQL SECURITY clause can be DEFINER or INVOKER, specifying whether the routine is executed with the privileges of the specified account (which must have access privileges to the database associated with the routine) or the privileges of the caller. The default value is DEFINER. The user calling the routine must have the EXECUTE privilege on the routine.

The DEFINER clause specifies the account used to check access privileges during routine execution. If the DEFINER clause is specified, the user value must be an account specified as 'user_name'@'host_name' (such as 'admin'@'oblocalhost') or an account obtained using the CURRENT_USER() function. If the DEFINER clause is omitted, the default definer is the user who executed the CREATE FUNCTION statement. In the body of a stored routine defined with the SQL SECURITY DEFINER characteristic, you can use the CURRENT_USER function to return the DEFINER value of the routine.

The server handles data types of routine parameters, local routine variables created using DECLARE, and function return values as follows:

• It checks for data type mismatches and overflows. Conversion and overflow issues can result in warnings or errors in strict SQL mode.

• Scalar values can be specified. For example, the SET val=(SELECT 1, 2) statement is invalid.

• For character data types, if the CHARACTER SET is specified in the declaration, the specified character set and its default collation are used. If the COLLATE attribute is used, the specified collation is used instead of the default collation.

  If neither CHARACTER SET nor COLLATE is specified, the database character set and collation applied when the routine was created are used. To prevent the server from using the database character set and collation, explicitly specify the CHARACTER SET and COLLATE characteristics for the parameters.

  If you want to change the default character set or collation of the database, you must drop and recreate the stored routine to apply the new database defaults.

  The character_set_database and collation_database system variables can be used to specify the database character set and collation.

For more information about creating functions, see Stored functions.