CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE 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:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement

These statements create stored routines. To use them, it is necessary to have the CREATE ROUTINE privilege. If binary logging is enabled, the CREATE FUNCTION statement might may also require the SUPER privilege, as described in Section 19.4, “Binary Logging of Stored Routines and Triggers”. MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator.

By default, the 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.

If the routine name is the same as the name of a built-in SQL function, you must use a space between the name and the following parenthesis when defining the routine, or a syntax error occurs. This is also true when you invoke the routine later. For this reason, we suggest that it is better to avoid re-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 allowable to have spaces after a 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. 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. (FUNCTION parameters are always regarded as IN parameters.)

Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.

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.

The routine_body consists of a valid SQL procedure statement. This can be a simple statement such as SELECT or INSERT, or it can be a compound statement written using BEGIN and END. Compound statement syntax is described in Section 19.2.5, “BEGIN ... END Compound Statement Syntax”. Compound statements can contain declarations, loops, and other control structure statements. The syntax for these statements is described later in this chapter. See, for example, Section 19.2.6, “DECLARE Statement Syntax”, and Section 19.2.10, “Flow Control Constructs”. Some statements are not allowed in stored routines; see Section I.1, “Restrictions on Stored Routines and Triggers”.

The CREATE FUNCTION statement was used in earlier versions of MySQL to support UDFs (user-defined functions). See Section 27.3, “Adding New Functions to MySQL”. UDFs continue to be supported, even with the existence of stored functions. A UDF can be regarded as an external stored function. However, do note that stored functions share their namespace with UDFs.

A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC.

For replication purposes, use of the NOW() function (or its synonyms) or RAND() does not necessarily make a routine non-deterministic. For NOW(), the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is invoked only once within a routine. (You can consider the routine execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)

Currently, the DETERMINISTIC characteristic is accepted, but not yet used by the optimizer. However, if binary logging is enabled, this characteristic affects which routine definitions MySQL accepts. See Section 19.4, “Binary Logging of Stored Routines and Triggers”.

Several characteristics provide information about the nature of data use by the routine. CONTAINS SQL indicates that the routine does not contain statements that read or write data. NO SQL indicates that the routine contains no SQL statements. READS SQL DATA indicates that the routine contains statements that read data, but not statements that write data. MODIFIES SQL DATA indicates that the routine contains statements that may write data. CONTAINS SQL is the default if none of these characteristics is given explicitly. These characteristics are advisory only. The server does not use them to constrain what kinds of statements a routine will be allowed to execute.

The SQL SECURITY characteristic can be used to specify whether the routine should be executed using the permissions of the user who creates the routine or the user who invokes it. The default value is DEFINER. This feature is new in SQL:2003. The creator or invoker must have permission to access the database with which the routine is associated. It is necessary to have the EXECUTE privilege to be able to execute the routine. The user that must have this privilege is either the definer or invoker, depending on how the SQL SECURITY characteristic is set.

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.

When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). USE statements within stored routines are disallowed.

The server uses the data type of a routine parameter or function return value as follows. These rules also apply to local routine variables created with the DECLARE statement (Section 19.2.7.1, “DECLARE Local Variables”).

The COMMENT clause is a MySQL extension, and may be used to describe the stored routine. This information is displayed by the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.

MySQL allows routines to contain DDL statements, such as CREATE and DROP. MySQL also allows stored procedures (but not stored functions) to contain SQL transaction statements such as COMMIT. Stored functions may not contain statements that do 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 allow them.

Stored routines cannot use LOAD DATA INFILE.

Statements that return a result set cannot be used within a stored function. This includes SELECT statements that do not use INTO to fetch column values into variables, SHOW statements, and other statements such as EXPLAIN. 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_IN_FUNC). 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).

The following is an example of a simple stored procedure that uses an OUT parameter. The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This allows the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

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

When using the delimiter command, you should avoid the use of the backslash (‘\’) character because that is the escape character for MySQL.

The following is an example of a function that 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)) RETURNS CHAR(50)
    -> 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)

A stored function returns a value of the data type specified in its RETURNS clause. If the RETURN statement returns a value of a different type, the value is coerced to the proper type. For example, if a function returns an ENUM or SET value, 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.