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

Эти утверждения используются для создания хранимой процедуры (хранимой процедуры или функции). То есть, указанная процедура становится известной серверу. По умолчанию хранимая процедура ассоциируется с базой данных по умолчанию. Чтобы явно связать процедуру с заданной базой данных, укажите имя как db_name.sp_name при её создании.

Утверждение CREATE FUNCTION также используется в MySQL для поддержки загружаемых функций. См. Раздел 13.7.3.1, «Утверждение CREATE FUNCTION для загружаемых функций». Загружаемая функция может рассматриваться как внешняя хранимая функция. Хранимые функции разделяют своё пространство имён с загружаемыми функциями. См. Раздел 9.2.5, «Разбор и разрешение имён функций» для правил, описывающих, как сервер интерпретирует ссылки на различные типы функций.

Для вызова хранимой процедуры используйте утверждение CALL (см. Раздел 13.2.1, «Утверждение CALL»). Для вызова хранимой функции обратитесь к ней в выражении. Функция возвращает значение во время вычисления выражения.

Для CREATE PROCEDURE и CREATE FUNCTION требуется CREATE ROUTINE разрешение. Если присутствует DEFINER, необходимые разрешения зависят от значения user, как описано в Разделе 23.6, «Управление доступом к хранимым объектам». Если включен двоичный лог, CREATE FUNCTION может потребовать SUPER разрешения, как описано в Разделе 23.7, «Двоичный лог хранимых программ».

По умолчанию MySQL автоматически предоставляет ALTER ROUTINE и EXECUTE разрешения создателю процедуры. Это поведение может быть изменено путём отключения системной переменной automatic_sp_privileges. См. Раздел 23.2.2, «Хранимые процедуры и разрешения MySQL».

DEFINER и SQL SECURITY указывают контекст безопасности, используемый при проверке разрешений на доступ во время выполнения процедуры, как описано далее в этом разделе.

Если имя процедуры совпадает с именем встроенной SQL-функции, возникает синтаксическая ошибка, если не используется пробел между именем и следующей скобкой при определении процедуры или последующем вызове. По этой причине следует избегать использования имён существующих SQL-функций для собственных хранимых процедур.

SQL-режим IGNORE_SPACE применяется к встроенным функциям, а не к хранимым процедурам. Всегда разрешается иметь пробелы после имени хранимой процедуры, независимо от того, включён ли IGNORE_SPACE.

Список параметров в скобках должен всегда присутствовать. Если параметров нет, необходимо использовать пустой список параметров (). Имена параметров нечувствительны к регистру.

Каждый параметр является параметром IN по умолчанию. Чтобы указать иначе для параметра, используйте ключевое слово OUT или INOUT перед именем параметра.

Параметр IN передает значение в процедуру. Процедура может изменить значение, но это изменение не видно вызывающей стороне при возврате процедуры. Параметр OUT передает значение из процедуры обратно вызывающей стороне. Его начальное значение является NULL внутри процедуры, и его значение видно вызывающей стороне при возврате процедуры. Параметр INOUT инициализируется вызывающей стороной, может быть изменён процедурой, и любое изменение, внесённое процедурой, видно вызывающей стороне при возврате процедуры.

Для каждого параметра OUT или INOUT, передайте переменную пользователя в утверждении CALL, которое вызывает процедуру, чтобы получить его значение при возврате процедуры. Если вы вызываете процедуру из другой хранимой процедуры или функции, вы также можете передать параметр процедуры или локальную переменную процедуры в качестве параметра OUT или INOUT. Если вы вызываете процедуру из триггера, вы также можете передать NEW.col_name в качестве параметра OUT или INOUT.

Сведения о влиянии необработанных условий на параметры процедуры см. в Разделе 13.6.7.8, «Обработка условий и параметры OUT или INOUT».

Параметры процедуры нельзя использовать в операторах, подготовленных внутри процедуры; см. Раздел 23.8, «Ограничения на хранимые программы».

Следующий пример демонстрирует простую хранимую процедуру, которая, получив код страны, подсчитывает количество городов для этой страны, которые появляются в таблице city базы данных world. Код страны передаётся с помощью параметра IN, а количество городов возвращается с помощью параметра OUT:

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)

В примере используется клиент mysql delimiter команда для изменения разделителя операторов с ; на // во время определения процедуры. Это позволяет разделителю ;, используемому в теле процедуры, передаваться на сервер, а не интерпретироваться самим клиентом mysql. См. Раздел 23.1, «Определение хранимых программ».

Оператор RETURNS может быть указан только для FUNCTION, для которого он является обязательным. Он указывает тип возвращаемого значения функции, и тело функции должно содержать утверждение RETURN value. Если утверждение RETURN возвращает значение другого типа, значение приводится к нужному типу. Например, если функция указывает значение типа ENUM или SET в операторе RETURNS, но утверждение RETURN возвращает целое число, значение, возвращаемое из функции, представляет собой строку для соответствующего члена ENUM набора членов SET.

Следующая примерная функция принимает параметр, выполняет операцию с помощью SQL-функции и возвращает результат. В этом случае использование delimiter не требуется, так как определение функции не содержит внутренних разделителей операторов ;:

mysql> CREATE FUNCTION hello (s CHAR(20))
    ->   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)

Типы параметров и типы возвращаемых значений функций могут быть объявлены с использованием любого допустимого типа данных. Атрибут COLLATE может быть использован, если перед ним указано CHARACTER SET.

routine_body состоит из допустимого оператора SQL-процедуры. Это может быть простое утверждение, такое как SELECT или INSERT, или сложное утверждение, написанное с использованием BEGIN и END. Сложные утверждения могут содержать объявления, циклы и другие операторы управления. Синтаксис этих операторов описан в Разделе 13.6, «Сложные утверждения». На практике хранимые функции, как правило, используют сложные утверждения, если тело не состоит из одного утверждения RETURN.

MySQL позволяет процедурам содержать операторы DDL, такие как CREATE и DROP. MySQL также разрешает хранимым процедурам (но не хранимым функциям) содержать утверждения SQL-транзакций, такие как COMMIT. Хранимые функции не могут содержать операторов, которые выполняют явное или неявное подтверждение или откат.

Операторы, возвращающие набор результатов, могут использоваться внутри хранимой процедуры, но не внутри хранимой функции. Это ограничение распространяется на операторы SELECT, которые не имеют условия INTO var_list, и другие операторы, такие как SHOW, EXPLAIN и CHECK TABLE. Для операторов, возвращающих набор результатов в момент определения функции, возникает ошибка Not allowed to return a result set from a function (). Для операторов, возвращающих набор результатов только во время выполнения, возникает ошибка PROCEDURE %s can't return a result set in the given context ().

Операторы USE внутри хранимых процедур запрещены. При вызове процедуры выполняется неявное USE db_name (и отменяется при завершении процедуры). Это задаёт для процедуры базу данных по умолчанию во время её выполнения. Ссылки на объекты в базах данных, отличных от базы данных по умолчанию процедуры, должны быть квалифицированы соответствующим именем базы данных.

Дополнительную информацию об операторах, запрещённых в хранимых процедурах, см. в Разделе 23.8, «Ограничения на хранимые программы».

Информацию о вызове хранимых процедур из программ, написанных на языке с интерфейсом MySQL, см. в Разделе 13.2.1, «Оператор CALL».

MySQL сохраняет значение системной переменной sql_mode, действующее при создании или изменении процедуры, и всегда выполняет процедуру с этим значением, независимо от текущего режима SQL сервера при запуске процедуры.

Переключение с режима SQL вызывающего приложения на режим SQL процедуры происходит после оценки аргументов и присвоения полученных значений параметрам процедуры. Если вы определяете процедуру в строгом режиме SQL, но вызываете её в нестрогом режиме, присвоение аргументов параметрам процедуры не происходит в строгом режиме. Если вам необходимо, чтобы выражения, передаваемые в процедуру, присваивались в строгом режиме SQL, вы должны вызвать процедуру в строгом режиме.

Характеристика COMMENT является расширением MySQL и может быть использована для описания хранимой процедуры. Эта информация отображается операторами SHOW CREATE PROCEDURE и SHOW CREATE FUNCTION.

Характеристика LANGUAGE указывает язык, на котором написана процедура. Сервер игнорирует эту характеристику; поддерживаются только SQL-процедуры.

Процедура считается “детерминированной”, если она всегда производит один и тот же результат для одних и тех же входных параметров, и “недетерминированной” в противном случае. Если ни DETERMINISTIC, ни NOT DETERMINISTIC не указаны в определении процедуры, значение по умолчанию — NOT DETERMINISTIC. Чтобы объявить функцию детерминированной, необходимо явно указать DETERMINISTIC.

Оценка характера процедуры основана на “честности” создателя: MySQL не проверяет, что процедура, объявленная DETERMINISTIC, не содержит операторов, которые дают недетерминированный результат. Однако неправильное объявление процедуры может повлиять на результаты или производительность. Объявление недетерминированной процедуры как DETERMINISTIC может привести к неожиданным результатам, заставив оптимизатор выбрать неверный план выполнения. Объявление детерминированной процедуры как NONDETERMINISTIC может снизить производительность, из-за недоступности оптимизаций.

Если двоичное протоколирование включено, характеристика DETERMINISTIC влияет на то, какие определения процедур MySQL принимает. См. Раздел 23.7, «Двоичное протоколирование хранимых программ».

Процедура, содержащая функцию NOW() (или её синонимы) или RAND(), является недетерминированной, но может быть всё ещё безопасной для репликации. Для NOW() двоичный журнал включает метку времени и реплицирует её корректно. RAND() также реплицируется корректно, если её вызов происходит только один раз во время выполнения процедуры. (Вы можете рассматривать метку времени выполнения процедуры и генератор случайных чисел как неявные входные параметры, которые одинаковы в источнике и реплике.)

Несколько характеристик предоставляют информацию о характере использования данных процедурой. В MySQL эти характеристики являются лишь рекомендательными. Сервер не использует их для ограничения того, какие операторы может выполнять процедура.

Характеристика SQL SECURITY может быть DEFINER или INVOKER, чтобы указать контекст безопасности; то есть, выполняет ли процедура операции с привилегиями учётной записи, указанной в DEFINER-оператор, или пользователя, который её вызывал. Эта учётная запись должна иметь разрешения для доступа к базе данных, с которой связана процедура. Значение по умолчанию — DEFINER. Пользователь, вызывающий процедуру, должен иметь привилегию EXECUTE, как и учётная запись DEFINER, если процедура выполняется в контексте безопасности создателя.

Оператор DEFINER указывает учётную запись MySQL, которая используется при проверке разрешений доступа во время выполнения процедуры для процедур, имеющих характеристику SQL SECURITY DEFINER.

Если оператор DEFINER присутствует, значение user должно быть учётной записью MySQL, заданной как 'user_name'@'host_name', CURRENT_USER или CURRENT_USER(). Разрешённые значения user зависят от имеющихся у вас привилегий, как обсуждалось в Разделе 23.6, «Управление доступом к хранимым объектам». Также см. этот раздел для дополнительной информации о безопасности хранимых процедур.

Если оператор DEFINER опущен, значением по умолчанию является пользователь, который выполнил оператор CREATE PROCEDURE или CREATE FUNCTION. Это эквивалентно явному указанию DEFINER = CURRENT_USER.

В теле хранимой процедуры, определённой с характеристикой SQL SECURITY DEFINER, функция CURRENT_USER возвращает значение DEFINER процедуры. Сведения об аудировании пользователей внутри хранимых процедур см. в Разделе 6.2.18, «Аудирование активности учётных записей на основе SQL».

Рассмотрим следующую процедуру, которая отображает количество учётных записей MySQL, перечисленных в системной таблице mysql.user:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

Процедуре присваивается учётная запись 'admin'@'localhost' независимо от того, какой пользователь её определяет. Она выполняется с привилегиями этой учётной записи независимо от того, какой пользователь её вызывает (поскольку по умолчанию характер безопасности — DEFINER). Успех или неудача процедуры зависит от того, обладает ли вызывающий пользователь привилегией EXECUTE для неё и от того, обладает ли учётная запись 'admin'@'localhost' привилегией SELECT для таблицы mysql.user.

Теперь предположим, что процедура определена с характеристикой SQL SECURITY INVOKER:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

У процедуры всё ещё есть учётная запись DEFINER 'admin'@'localhost', но в этом случае она выполняется с привилегиями вызывающего пользователя. Таким образом, успех или неудача процедуры зависит от того, обладает ли вызывающий пользователь привилегией EXECUTE для неё и привилегией SELECT для таблицы mysql.user.

Сервер обрабатывает тип данных параметра процедуры, локальной переменной процедуры, созданной с помощью DECLARE, или значения возвращаемого функцией следующим образом: