CREATE FUNCTION
Syntax:
CREATE [DEFINER = {user | CURRENT_USER}] FUNCTION func_name ([func_parameter[,...]]) RETURNS type [characteristic ...] RETURN func_body
func_parameter: param_name type
type: Any valid MySQL data type
characteristic: LANGUAGE SQL
|
[NOT] DETERMINISTIC|
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }|
SQL SECURITY { DEFINER | INVOKER }|
COMMENT 'string'func_body: Valid SQL procedure statement
Description:
Use the CREATE FUNCTION
statement to create a new function. You must have
the CREATE ROUTINE
database privilege to use CREATE FUNCTION
.
A function takes any number of arguments and returns a value from the function body. The
function body can be any valid SQL expression as you would use, for example, in any select
expression. If you have the appropriate privileges, you can call the function exactly as you
would any built-in function. See Security below for details on privileges.
You can also use a variant of the CREATE FUNCTION
statement to install a user-defined
function (UDF) defined by a plugin. See CREATE FUNCTION (UDF)
for details.
You can use a SELECT
statement for the function body by enclosing it in
parentheses, exactly as you would to use a subselect for any other expression. The SELECT
statement must return a single value. If more than one column is returned when the function is called,
error 1241 results. If more than one row is returned when the function is called, error 1242
results. Use a LIMIT
clause to ensure only one row is returned.
By default, a function is associated with the default database. To associate the function explicitly
with a given database, specify the fully-qualified name as db_name.func_name
when you create it. If the function name is the same as the name of a built-in function, you must
use the fully qualified name when you call it.
The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.
Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.
The RETURNS
clause specifies the return type of the function. If the RETURN
clause
returns a value of a different type, the value is coerced to the proper type. For example, if a function
specifies an ENUM
or SET
value in the RETURNS
clause, but the RETURN
clause returns an integer, the value returned from the function is the string for the corresponding ENUM
member of set of SET
members.
The following example function takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:
MariaDB [test]> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT hello('world'); +----------------+ | hello('world') | +----------------+ | Hello, world! | +----------------+ 1 row in set (0.00 sec) MariaDB [test]>
MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.
Security
To execute the CREATE PROCEDURE
or CREATE FUNCTION
statement, it is
necessary to have the CREATE ROUTINE
privilege. By default, MySQL
automatically grants the ALTER ROUTINE
and EXECUTE
privileges to the
routine creator. See also
http://dev.mysql.com/doc/refman/5.1/en/stored-routines-privileges.html. If
binary logging is enabled, the CREATE FUNCTION
statement might also require
the SUPER privilege, as described in
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html.
The DEFINER
and SQL SECURITY clauses specify the security context to
be used when checking access privileges at routine execution time, as
described later.