CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...]
    [USING library_reference[, library_reference][, library_reference]]
    [AS] 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 | JAVASCRIPT }
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

library_reference:
    [database.]library_name [[AS] alias]

routine_body:
    SQL routine or JavaScript statements

Эти выражения используются для создания хранимой процедуры (хранимой процедуры или функции). То есть указанная процедура становится известной серверу. По умолчанию хранимая процедура ассоциирована с базой данных по умолчанию. Чтобы явно связать процедуру с заданной базой данных, укажите имя как 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.7, «Управление доступом к хранимым объектам». Если включен двоичный лог, CREATE FUNCTION может потребовать привилегии SUPER, как описано в Разделе 27.8, «Двоичный лог хранимых программ».

По умолчанию 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.9, «Ограничения на хранимые программы».

В следующем примере показана простая хранимая процедура, которая, получив код страны, подсчитывает количество городов для этой страны, которые появляются в таблице 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.

Ключевое слово AS используется для указания того, что тело процедуры, которое следует за ним, написано на языке, отличном от SQL; оно непосредственно предшествует открывающемуся разделителю, заключенному в кавычки со знаком доллара, или кавычке тела процедуры. См. Раздел 27.3, «JavaScript-хранимые программы».

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.9, «Ограничения на хранимые программы».

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

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

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

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

Характеристика LANGUAGE указывает язык, на котором написана процедура. Сервер игнорирует эту характеристику; поддерживаются только SQL-процедуры. Если эта характеристика не указана, язык предполагается SQL. Хранимые процедуры, написанные на JavaScript (см. Раздел 27.3, «JavaScript хранимые программы»), требуют явного указания этого с помощью LANGUAGE JAVASCRIPT.

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

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

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

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

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

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

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

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

Если клауза DEFINER опущена, по умолчанию 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, или возвращаемого значения функции следующим образом:

В MySQL 9.2.0 и более поздних версиях, CREATE PROCEDURE и CREATE FUNCTION поддерживают опцию USING для импорта одной или нескольких библиотек JavaScript, ссылки на которые перечислены в скобках после ключевого слова USING. Каждая ссылка имеет вид [database.]library_name [[AS] alias], состоящий из следующих частей:

Пример:

mysql> CREATE LIBRARY IF NOT EXISTS jslib.lib1 LANGUAGE JAVASCRIPT
    ->     AS $$
    $>       export function f(n) {
    $>         return n
    $>       }
    $>     $$;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE LIBRARY IF NOT EXISTS jslib.lib2 LANGUAGE JAVASCRIPT
    ->     AS $$
    $>       export function g(n) {
    $>         return n * 2
    $>       }
    $>     $$;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION foo(n INTEGER) RETURNS INTEGER LANGUAGE JAVASCRIPT
    ->     USING (jslib.lib1 AS mylib, jslib.lib2 AS yourlib)
    ->     AS $$
    $>       return mylib.f(n) + yourlib.g(n)
    $>     $$;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT foo(8);
+--------+
| foo(8) |
+--------+
|     24 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.routines WHERE ROUTINE_NAME='foo'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: foo
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: jslib
            ROUTINE_NAME: foo
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: int
            ROUTINE_BODY: EXTERNAL
      ROUTINE_DEFINITION:
      return mylib.f(n) + otherlib.g(n)

           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: JAVASCRIPT
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2024-12-16 11:27:28
            LAST_ALTERED: 2024-12-16 11:27:28
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: me@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

USING может быть включено в оператор CREATE PROCEDURE или CREATE FUNCTION только в том случае, если оператор также содержит явное предложение LANGUAGE=JAVASCRIPT.

Сведения об инструменте для создания библиотек JavaScript, которые можно импортировать с помощью USING, см. в Разделе 15.1.16, «CREATE LIBRARY Statement». Дополнительную информацию и примеры см. также в Разделе 27.3.8, «Использование библиотек JavaScript».