CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

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

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

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

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

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

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

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

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

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

IF NOT EXISTS предотвращает ошибку, если уже существует процедура с таким же именем. Этот параметр поддерживается как с CREATE FUNCTION, так и с CREATE PROCEDURE.

Если встроенная функция с таким же именем уже существует, попытка создать хранимую функцию с CREATE FUNCTION ... IF NOT EXISTS завершится с предупреждением о том, что она имеет то же имя, что и встроенная функция; это ничем не отличается от выполнения той же команды CREATE FUNCTION без указания IF NOT EXISTS.

Если загружаемая функция с таким же именем уже существует, попытка создать хранимую функцию с помощью IF NOT EXISTS завершится с предупреждением. Это аналогично ситуации без указания IF NOT EXISTS.

См. Разрешение имён функций для получения дополнительной информации.

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

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

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

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

Информацию об эффекте необработанных условий на параметры процедуры см. в Разделе 15.6.7.8, «Обработка условий и параметры OUT или INOUT».

Параметры процедуры нельзя использовать в операторах, подготовленных внутри процедуры; см. Раздел 27.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. См. Раздел 27.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. Составные операторы могут содержать объявления, циклы и другие операторы управления. Синтаксис этих операторов описан в Разделе 15.6, «Синтаксис составных операторов». На практике хранимые функции, как правило, используют составные операторы, если тело функции не состоит из одного оператора RETURN.

MySQL позволяет хранимым процедурам содержать операторы DDL, такие как CREATE и DROP. MySQL также разрешает хранимым процедурам (но не хранимым функциям) содержать операторы SQL транзакций, такие как COMMIT. Хранимые функции не могут содержать операторы, которые выполняют явное или неявное подтверждение или откат. Поддержка этих операторов не требуется стандартом SQL, в котором указывается, что каждый поставщик СУБД может решить, разрешать ли их.

Операторы, возвращающие результат, могут использоваться внутри хранимой процедуры, но не внутри хранимой функции. Это ограничение включает в себя операторы 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 (и отменяется при завершении процедуры). Это приводит к тому, что процедура использует указанную базу данных по умолчанию во время выполнения. Ссылки на объекты в базах данных, отличных от базы данных по умолчанию процедуры, должны быть квалифицированы соответствующим именем базы данных.

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

Сведения о вызове хранимых процедур из программ, написанных на языке, имеющем интерфейс MySQL, см. в Разделе 15.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 принимает. См. Раздел 27.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 зависят от имеющихся у вас привилегий, как обсуждается в Разделе 27.6, «Управление доступом к хранимым объектам». Также в этом разделе вы найдете дополнительную информацию о безопасности хранимых процедур.

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

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

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

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

Процедуре назначена учетная запись DEFINER '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.

По умолчанию, при выполнении процедуры с характеристикой SQL SECURITY DEFINER, сервер MySQL не устанавливает активные роли для учётной записи MySQL, указанной в предложении DEFINER, а только роли по умолчанию. Исключением является случай, когда системная переменная activate_all_roles_on_login включена; в этом случае сервер MySQL устанавливает все роли, предоставленные пользователю DEFINER, включая обязательные роли. Таким образом, по умолчанию права, предоставленные через роли, не проверяются при выполнении оператора CREATE PROCEDURE или CREATE FUNCTION. Для хранимых программ, если выполнение должно происходить с ролями, отличными от стандартных, тело программы может выполнить SET ROLE для активации необходимых ролей. Это следует делать с осторожностью, так как права, назначенные ролям, могут быть изменены.

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