27.9 Restrictions on Stored Programs — MySQL 9.2

27.9 Ограничения для хранимых программ

Эти ограничения применяются к функциям, описанным в Главе 27, Хранимые объекты.

Некоторые из указанных здесь ограничений относятся ко всем хранимым процедурам; то есть, как к хранимым процедурам, так и к хранимым функциям. Также есть некоторые ограничения, специфичные для хранимых функций, но не для хранимых процедур.

Ограничения для хранимых функций также применяются к триггерам. Также существуют некоторые ограничения, специфичные для триггеров.

Ограничения для хранимых процедур также применяются к DO-элементу определений событий планировщика событий. Также есть некоторые ограничения, специфичные для событий.

SQL-выражения, запрещённые в хранимых процедурах

Хранимые процедуры не могут содержать произвольные SQL-выражения. Следующие выражения запрещены:

Операции блокировки LOCK TABLES и UNLOCK TABLES.
ALTER VIEW.
LOAD DATA и LOAD XML.
SQL-предварительно скомпонованные выражения (PREPARE, EXECUTE, DEALLOCATE PREPARE) могут быть использованы в хранимых процедурах, но не в хранимых функциях или триггерах. Таким образом, хранимые функции и триггеры не могут использовать динамический SQL (где вы строите выражения как строки, а затем выполняете их).
В общем случае, выражения, запрещённые в SQL-предварительно скомпонованных выражениях, также запрещены в хранимых программах. Список поддерживаемых выражений в виде предварительно скомпонованных выражений см. в Разделе 15.5, “Предварительно скомпонованные выражения”. Исключениями являются SIGNAL, RESIGNAL и GET DIAGNOSTICS, которые запрещены в виде предварительно скомпонованных выражений, но разрешены в хранимых программах.
Поскольку локальные переменные действуют только во время выполнения хранимой программы, ссылки на них не разрешены в предварительно скомпонованных выражениях, созданных в рамках хранимой программы. Область видимости предварительно скомпонованного выражения – текущая сессия, а не хранимая программа, поэтому выражение может быть выполнено после завершения программы, в этот момент переменные могут быть недоступны. Например, SELECT ... INTO local_var не может быть использовано в качестве предварительно скомпонованного выражения. Это ограничение также относится к параметрам хранимых процедур и функций. См. Раздел 15.5.1, “PREPARE”.
Внутри всех хранимых программ (хранимые процедуры и функции, триггеры и события) анализатор интерпретирует BEGIN [WORK] как начало блока BEGIN ... END. Чтобы начать транзакцию в этом контексте, используйте START TRANSACTION вместо этого.

Ограничения для хранимых функций

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

Выражения, выполняющие явное или неявное подтверждение или откат. Поддержка этих выражений не требуется стандартом SQL, который гласит, что каждый поставщик СУБД может решить, разрешать ли их.
Выражения, возвращающие набор результатов. Это включает SELECT выражения, у которых нет INTO var_list-элемента, и другие выражения, такие как SHOW, EXPLAIN и CHECK TABLE. Функция может обрабатывать набор результатов либо с помощью SELECT ... INTO var_list, либо с помощью курсора и FETCH-выражений. См. Раздел 15.2.13.1, “SELECT ... INTO” и Раздел 15.6.6, “Cursors”.
FLUSH-выражения.
Хранимые функции не могут быть использованы рекурсивно.
Хранимые функции или триггеры не могут изменять таблицу, которая уже используется (для чтения или записи) выражением, вызвавшим функцию или триггер.
Если вы ссылаетесь на временную таблицу несколько раз в хранимой функции под разными псевдонимами, возникает ошибка Can't reopen table: 'tbl_name', даже если ссылки происходят в разных выражениях внутри функции.
HANDLER ... READ-выражения, вызывающие хранимые функции, могут вызывать ошибки репликации и запрещены.

Ограничения для триггеров

Для триггеров применяются следующие дополнительные ограничения:

Триггеры не активируются действиями внешнего ключа.
При использовании репликации на основе строк, триггеры на реплике не активируются выражениями, исходящими от источника. Триггеры на реплике активируются при использовании репликации на основе выражений. Дополнительную информацию см. в Разделе 19.5.1.37, “Репликация и триггеры”.
Выражение RETURN запрещено в триггерах, которые не могут возвращать значение. Чтобы немедленно выйти из триггера, используйте выражение LEAVE.
Триггеры запрещены для таблиц в базе данных mysql. Также они запрещены для таблиц INFORMATION_SCHEMA или performance_schema. Эти таблицы фактически являются представлениями, и триггеры запрещены для представлений.
Кэш триггеров не обнаруживает, когда метаданные базовых объектов изменились. Если триггер использует таблицу, а таблица изменилась с момента загрузки триггера в кэш, триггер работает с устаревшими метаданными.

Возникновение конфликтов имен в хранимых процедурах

Одно и то же идентификатор может использоваться для параметра процедуры, локальной переменной и столбца таблицы. Также одно и то же имя локальной переменной может использоваться в вложенных блоках. Например:

CREATE PROCEDURE p (i INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SELECT i FROM t;
  BEGIN
    DECLARE i INT DEFAULT 1;
    SELECT i FROM t;
  END;
END;

В таких случаях идентификатор является неоднозначным, и применяются следующие правила приоритета:

Локальная переменная имеет приоритет над параметром процедуры или столбцом таблицы.
Параметр процедуры имеет приоритет над столбцом таблицы.
Локальная переменная во внутреннем блоке имеет приоритет над локальной переменной во внешнем блоке.

Поведение, при котором переменные имеют приоритет над столбцами таблиц, не является стандартным.

Рассмотрение особенностей репликации

Использование хранимых процедур может вызывать проблемы с репликацией. Этот вопрос более подробно обсуждается в разделе 27.8 «Регистрация двоичного кода хранимых программ».

Опция --replicate-wild-do-table=db_name.tbl_name применяется к таблицам, представлениям и триггерам. Она не применяется к хранимым процедурам и функциям, а также событиям. Для фильтрации операторов, работающих с последними объектами, используйте одну или несколько опций --replicate-*-db.

Рассмотрение особенностей отладки

Средств отладки хранимых процедур нет.

Неподдерживаемый синтаксис из стандарта SQL:2003

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

UNDO обработчики
FOR циклы

Рассмотрение особенностей конкурентности хранимых процедур

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

Для максимальной конкурентности хранимые функции должны сводить к минимуму побочные эффекты; в частности, обновление таблицы внутри хранимой функции может снизить одновременные операции с этой таблицей. Хранимая функция приобретает блокировки таблиц перед выполнением, чтобы избежать несогласованности в двоичном журнале из-за несовпадения порядка, в котором выполняются операторы, и когда они появляются в журнале. При использовании двоичного журналирования на основе операторов записываются операторы, вызывающие функцию, а не операторы, выполняемые внутри функции. Следовательно, хранимые функции, обновляющие одни и те же базовые таблицы, не выполняются параллельно. В отличие от этого, хранимые процедуры не приобретают блокировки на уровне таблицы. Все операторы, выполняемые внутри хранимых процедур, записываются в двоичный журнал, даже при использовании двоичного журналирования на основе операторов. См. раздел 27.8 «Регистрация двоичного кода хранимых программ».

Ограничения Планировщика событий

Следующие ограничения относятся к Планировщику событий:

Имена событий обрабатываются без учета регистра. Например, вы не можете иметь два события в одной базе данных с именами anEvent и AnEvent.
Событие не может быть создано из хранимой программы. Событие не может быть изменено или удалено из хранимой программы, если имя события указано с помощью переменной. Событие также не может создавать, изменять или удалять хранимые процедуры или триггеры.
Операторы DDL для событий запрещены, пока активен оператор LOCK TABLES.
Временные интервалы использования YEAR, QUARTER, MONTH и YEAR_MONTH при расчете времени событий выражаются в месяцах; при использовании любых других интервалов — в секундах. Нет способа заставить события, запланированные на выполнение в одну и ту же секунду, выполняться в заданном порядке. Кроме того — из-за округления, природы многопоточных приложений и того факта, что для создания событий и сигнализации об их выполнении требуется ненулевой промежуток времени — события могут быть задержаны на 1 или 2 секунды. Однако время, показанное в таблице схемы информации EVENTS в столбце LAST_EXECUTED, всегда точно соответствует фактическому времени выполнения события в пределах одной секунды. (См. также ошибку № 16522.)
Каждое выполнение операторов, содержащихся в теле события, происходит в новом соединении; поэтому эти операторы не оказывают никакого влияния на счетчик операторов в данном сеансе пользователя на сервере, такие как Com_select и Com_insert, которые отображаются оператором SHOW STATUS. Однако эти счетчики обновляются в глобальной области. (Ошибка № 16422)
События не поддерживают время позже конца эпохи Unix; это приблизительно начало 2038 года. Такие даты специально не допускаются Планировщиком событий. (Ошибка № 16396)
Ссылки на хранимые функции, загружаемые функции и таблицы в ON SCHEDULE-фрагментах операторов CREATE EVENT и ALTER EVENT не поддерживаются. Такие ссылки не допускаются. (См. ошибку № 22830 для получения дополнительной информации.)

Хранимые процедуры и триггеры в NDB Cluster

Хотя хранимые процедуры, хранимые функции, триггеры и запланированные события поддерживаются таблицами, использующими хранилище NDB, вы должны помнить, что они не распространяются автоматически между серверами MySQL, действующими в качестве узлов SQL кластера. Это связано с тем, что определения хранимых процедур и триггеров хранятся в таблицах в базе данных mysql, использующих таблицы InnoDB, которые не копируются между узлами кластера.

Любая хранимая процедура или триггер, взаимодействующая с таблицами MySQL Cluster, должны быть повторно созданы, выполнив соответствующие операторы CREATE PROCEDURE, CREATE FUNCTION или CREATE TRIGGER на каждом сервере MySQL, участвующем в кластере, где вы хотите использовать хранимую процедуру или триггер. Аналогично, любые изменения в существующих хранимых процедурах или триггерах должны быть выполнены явно на всех узлах SQL кластера, используя соответствующие операторы ALTER или DROP на каждом сервере MySQL, обращающемся к кластеру.

Предупреждение

Не пытайтесь обойти эту проблему, преобразовывая любые таблицы базы данных mysql для использования хранилища NDB. Изменение системных таблиц в базе данных mysql не поддерживается и, скорее всего, приведет к нежелательным результатам.