СОЗДАНИЕ ПРОЦЕДУРЫ

Синтаксис

CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB 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

Описание

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

При вызове процедуры неявно выполняется USE db_name (и отменяется при завершении процедуры). Это позволяет процедуре использовать заданную базу данных во время её выполнения. Использование инструкций USE внутри хранимых процедур запрещено.

После создания хранимой процедуры, её вызывают, используя инструкцию CALL (см. CALL).

Для выполнения инструкции CREATE PROCEDURE необходимо иметь привилегию CREATE ROUTINE. По умолчанию MariaDB автоматически предоставляет привилегии ALTER ROUTINE и EXECUTE создателю процедуры. См. также Хранимые привилегии процедур.

Определения DEFINER и SQL SECURITY определяют контекст безопасности, используемый при проверке привилегий на время выполнения процедуры, как описано здесь. Требуется привилегия SUPER, или, начиная с MariaDB 10.5.2, привилегия SET USER.

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

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

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

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

Допустимые идентификаторы для имён процедур см. в Именах идентификаторов.

Что нужно учитывать с CREATE OR REPLACE

• Нельзя использовать OR REPLACE вместе с IF EXISTS.

CREATE PROCEDURE IF NOT EXISTS

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

IN/OUT/INOUT

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

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

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

ДЕТЕРМИНИРОВАННАЯ/НЕДЕТЕРМИНИРОВАННАЯ

DETERMINISTIC и NOT DETERMINISTIC применяются только к функциям. Указание DETERMINISTC или NON-DETERMINISTIC в процедурах не имеет эффекта. Значение по умолчанию — NOT DETERMINISTIC. Функции являются DETERMINISTIC тогда, когда они всегда возвращают одно и то же значение для одного и того же входного значения. Например, функция усечения или подстроки. Любая функция, связанная с данными, поэтому всегда является NOT DETERMINISTIC.

CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA

CONTAINS SQL, NO SQL, READS SQL DATA, и MODIFIES SQL DATA являются информативными фразами, которые сообщают серверу, что делает функция. MariaDB не проверяет никоим образом, правильна ли указанная фраза. Если ни одна из этих фраз не указана, по умолчанию используется CONTAINS SQL.

MODIFIES SQL DATA означает, что функция содержит инструкции, которые могут изменять данные, хранящиеся в базах данных. Это происходит, если функция содержит инструкции, такие как DELETE, UPDATE, INSERT, REPLACE или DDL.

READS SQL DATA означает, что функция считывает данные, хранящиеся в базах данных, но не изменяет никаких данных. Это происходит, если используются инструкции SELECT, но никаких операций записи не выполняется.

CONTAINS SQL означает, что функция содержит по крайней мере одну SQL-инструкцию, но не считывает и не записывает данные, хранящиеся в базе данных. Примеры включают SET или DO.

NO SQL означает ничего, потому что MariaDB в настоящее время не поддерживает языки, кроме SQL.

Тело процедуры routine_body состоит из допустимой SQL-инструкции процедуры. Это может быть простая инструкция, например, SELECT или INSERT, или это может быть составная инструкция, написанная с использованием BEGIN и END. Составные инструкции могут содержать объявления, циклы и другие инструкции управления. См. Программно-ориентированные и составные инструкции для получения подробной информации о синтаксисе.

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

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

Вызов хранимой процедуры из программ

Для получения информации о вызове хранимых процедур из программ, написанных на языке, имеющем интерфейс MariaDB/MySQL, см. CALL.

OR REPLACE

Если используется необязательная фраза OR REPLACE, она действует как сокращение для:

DROP PROCEDURE IF EXISTS name;
CREATE PROCEDURE name ...;

за исключением того, что любые существующие привилегии для процедуры не удаляются.

sql_mode

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

Наборы символов и сортировки

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

Режим Oracle

В дополнение к традиционному синтаксису MariaDB на основе SQL/PSM поддерживается подмножество языка PL/SQL Oracle. См. Режим Oracle для получения подробной информации об изменениях при запуске режима Oracle.

Примеры

Следующий пример демонстрирует простую хранимую процедуру, использующую параметр OUT. Он использует команду DELIMITER для установки нового разделителя на время процесса — см. Разделители в клиенте mariadb.

DELIMITER //

CREATE PROCEDURE simpleproc (OUT param1 INT)
 BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
 END;
//

DELIMITER ;

CALL simpleproc(@a);

SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+

Набор символов и сортировка:

DELIMITER //

CREATE PROCEDURE simpleproc2 (
  OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
 BEGIN
  SELECT CONCAT('a'),f1 INTO param1 FROM t;
 END;
//

DELIMITER ;

CREATE OR REPLACE:

DELIMITER //

CREATE PROCEDURE simpleproc2 (
  OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
 BEGIN
  SELECT CONCAT('a'),f1 INTO param1 FROM t;
 END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists

DELIMITER ;

DELIMITER //

CREATE OR REPLACE PROCEDURE simpleproc2 (
  OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
 BEGIN
  SELECT CONCAT('a'),f1 INTO param1 FROM t;
 END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists

DELIMITER ;
Query OK, 0 rows affected (0.03 sec)

См. также

• Имена идентификаторов
• Обзор хранимых процедур
• ALTER PROCEDURE
• DROP PROCEDURE
• SHOW CREATE PROCEDURE
• SHOW PROCEDURE STATUS
• Хранимые привилегии процедур
• Таблица схем информации ROUTINES