CREATE FUNCTION

Syntax:
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

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

proc_parameter:
[ IN | OUT | INOUT ] param_name type

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

These statements are used to create a stored routine (a stored
procedure or function). That is, the specified routine becomes known to
the server. By default, a stored routine is associated with the default
database. To associate the routine explicitly with a given database,
specify the name as db_name.sp_name when you create it.

The CREATE FUNCTION statement is also used in MySQL to support UDFs
(user-defined functions). See [HELP CREATE FUNCTION UDF]. A UDF can be
regarded as an external stored function. Stored functions share their
namespace with UDFs. See
https://dev.mysql.com/doc/refman/8.0/en/function-resolution.html, for
the rules describing how the server interprets references to different
kinds of functions.

To invoke a stored procedure, use the CALL statement (see [HELP CALL]).
To invoke a stored function, refer to it in an expression. The function
returns a value during expression evaluation.

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE
privilege. If the DEFINER clause is present, the privileges required
depend on the user value, as discussed in
https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html.
If binary logging is enabled, CREATE FUNCTION might require the SUPER
privilege, as discussed in
https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html.

By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE
privileges to the routine creator. This behavior can be changed by
disabling the automatic_sp_privileges system variable. See
https://dev.mysql.com/doc/refman/8.0/en/stored-routines-privileges.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 in this section.

If the routine name is the same as the name of a built-in SQL function,
a syntax error occurs unless you use a space between the name and the
following parenthesis when defining the routine or invoking it later.
For this reason, avoid using the names of existing SQL functions for
your own stored routines.

The IGNORE_SPACE SQL mode applies to built-in functions, not to stored
routines. It is always permissible to have spaces after a stored
routine name, regardless of whether IGNORE_SPACE is enabled.

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 is an IN parameter by default. To specify otherwise for
a parameter, use the keyword OUT or INOUT before the parameter name.

*Note*:

Specifying a parameter as IN, OUT, or INOUT is valid only for a
PROCEDURE. For a FUNCTION, parameters are always regarded as IN
parameters.

An IN parameter passes a value into a procedure. The procedure might
modify the value, but the modification is not visible to the caller
when the procedure returns. An OUT parameter passes a value from the
procedure back to the caller. Its initial value is NULL within the
procedure, and its value is visible to the caller when the procedure
returns. An INOUT parameter is initialized by the caller, can be
modified by the procedure, and any change made by the procedure is
visible to the caller when the procedure returns.

For each OUT or INOUT parameter, pass a user-defined variable in the
CALL statement that invokes the procedure so that you can obtain its
value when the procedure returns. If you are calling the procedure from
within another stored procedure or function, you can also pass a
routine parameter or local routine variable as an OUT or INOUT
parameter. If you are calling the procedure from within a trigger, you
can also pass NEW.col_name as an OUT or INOUT parameter.

For information about the effect of unhandled conditions on procedure
parameters, see
https://dev.mysql.com/doc/refman/8.0/en/conditions-and-parameters.html.

Routine parameters cannot be referenced in statements prepared within
the routine; see
https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.htm
l.

The following example shows a simple stored procedure that, given a
country code, counts the number of cities for that country that appear
in the city table of the world database. The country code is passed
using an IN parameter, and the city count is returned using an OUT
parameter:

mysql> delimiter //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
BEGIN
SELECT COUNT(*) INTO cities FROM world.city
WHERE CountryCode = country;
END//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> CALL citycount('JPN', @cities); -- cities in Japan
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
| 248 |
+---------+
1 row in set (0.00 sec)

mysql> CALL citycount('FRA', @cities); -- cities in France
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
| 40 |
+---------+
1 row in set (0.00 sec)

The example uses the mysql client delimiter command to change the
statement delimiter from ; to // while the procedure is being defined.
This enables the ; delimiter used in the procedure body to be passed
through to the server rather than being interpreted by mysql itself.
See
https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html.

The RETURNS clause may be specified only for a FUNCTION, for which it
is mandatory. It indicates the return type of the function, and the
function body must contain a RETURN value statement. If the RETURN
statement 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 statement 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:

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)

Parameter types and function return types can be declared to use any
valid data type. The COLLATE attribute can be used if preceded by a
CHARACTER SET specification.

The routine_body consists of a valid SQL routine statement. This can be
a simple statement such as SELECT or INSERT, or a compound statement
written using BEGIN and END. Compound statements can contain
declarations, loops, and other control structure statements. The syntax
for these statements is described in
https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html.
In practice, stored functions tend to use compound statements, unless
the body consists of a single RETURN statement.

MySQL permits routines to contain DDL statements, such as CREATE and
DROP. MySQL also permits stored procedures (but not stored functions)
to contain SQL transaction statements such as COMMIT. Stored functions
may not contain statements that perform explicit or implicit commit or
rollback. Support for these statements is not required by the SQL
standard, which states that each DBMS vendor may decide whether to
permit them.

Statements that return a result set can be used within a stored
procedure but not within a stored function. This prohibition includes
SELECT statements that do not have an INTO var_list clause and other
statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that
can be determined at function definition time to return a result set, a
Not allowed to return a result set from a function error occurs
(ER_SP_NO_RETSET
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.h
tml#error_er_sp_no_retset)). For statements that can be determined only
at runtime to return a result set, a PROCEDURE %s can't return a result
set in the given context error occurs (ER_SP_BADSELECT
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.h
tml#error_er_sp_badselect)).

USE statements within stored routines are not permitted. When a routine
is invoked, an implicit USE db_name is performed (and undone when the
routine terminates). The causes the routine to have the given default
database while it executes. References to objects in databases other
than the routine default database should be qualified with the
appropriate database name.

For additional information about statements that are not permitted in
stored routines, see
https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.htm
l.

For information about invoking stored procedures from within programs
written in a language that has a MySQL interface, see [HELP CALL].

MySQL stores the sql_mode system variable setting in effect when a
routine is created or altered, and always executes the routine with
this setting in force, regardless of the current server SQL mode when
the routine begins executing.

The switch from the SQL mode of the invoker to that of the routine
occurs after evaluation of arguments and assignment of the resulting
values to routine parameters. If you define a routine in strict SQL
mode but invoke it in nonstrict mode, assignment of arguments to
routine parameters does not take place in strict mode. If you require
that expressions passed to a routine be assigned in strict SQL mode,
you should invoke the routine with strict mode in effect.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html