Глава 5. Сохраненные процедуры и функции

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа

Сохраненные подпрограммы (процедуры и функции) обеспечиваются в MySQL 5.0. Сохраненная процедура представляет собой набор инструкций SQL, которые могут быть сохранены на сервере. Если это было выполнено, клиентура не должна хранить индивидуальные инструкции, а может обратиться к сохраненной процедуре.

Некоторые ситуации, где сохраненные подпрограммы могут быть особенно полезны:

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

Сохраненные подпрограммы могут обеспечивать улучшенную эффективность, потому что меньшее количество информации должно быть переслано между клиентом и сервером. Правда это увеличивает нагрузку на сервер, поскольку большее количество работы выполнено на сервере. Рассмотрите это, если много машин пользователя (типа Web-серверов) обслуживаются только одним сервером базы данных.

Сохраненные подпрограммы также позволяют Вам иметь библиотеки функций непосредственно на сервере. Это свойство, общедоступное в современных языках прикладных программ, которые позволяют такой проект внутренне (например, используя классы). Использование этих свойств языка прикладной программы пользователя полезно для программиста даже вне контекста использования базы данных.

MySQL следует синтаксису SQL:2003 для сохраненных подпрограмм, который также используется в IBM DB2.

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

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

5.1. Сохраненные подпрограммы и таблицы предоставления привилегий

Сохраненные подпрограммы требуют таблицы proc в базе данных mysql. Эта таблица создана в течение процедуры установки MySQL 5.0. Если Вы наращиваете вычислительные возможности до MySQL 5.0 из более ранней версии, убедитесь, что модифицировали Ваши таблицы предоставления привилегий, чтобы удостовериться, что таблица proc существует.

Сервер управляет таблицей mysql.proc в ответ на инструкции, которые создают, изменяют или удаляют сохраненные подпрограммы. Это значит, что сервер не обратит внимания на ручное манипулирование этой таблицей.

Начиная с MySQL 5.0.3, система предоставления берет сохраненные подпрограммы во внимание следующим образом:

Привилегия CREATE ROUTINE необходима, чтобы создать сохраненные подпрограммы.
Привилегия ALTER ROUTINE необходима, чтобы изменять или удалять сохраненные подпрограммы. Эта привилегия предоставляется автоматически создателю подпрограммы.
Привилегия EXECUTE необходима, чтобы выполнить сохраненные подпрограммы. Эта привилегия предоставляется автоматически создателю подпрограммы. Также, по умолчанию характеристика SQL SECURITY для подпрограммы выставлена в DEFINER, что разрешает пользователям, которые имеют доступ к базе данных, с которой подпрограмма связана, выполнить эту подпрограмму.

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

Сохраненная подпрограмма является процедурой или функцией. Сохраненные подпрограммы созданы командами CREATE PROCEDURE и CREATE FUNCTION. Процедура вызывается, используя инструкцию CALL, и может только передавать обратные значения, используя переменные вывода. Функция может быть названа точно так же, как и любая другая функция языка (то есть, вызывая имя функции), и может возвращать скалярное значение. Сохраненные подпрограммы, разумеется, могут вызывать другие сохраненные подпрограммы.

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

Когда подпрограмма вызывается, выполняется неявный вызов USE db_name (и отменяется, когда подпрограмма завершается). Инструкции USE внутри сохраненных подпрограмм отвергнуты.
Вы можете квалифицировать стандартные имена с именем базы данных. Это может использоваться, чтобы обратиться к подпрограмме, которая не в текущей (актуальной) базе данных. Например, чтобы вызвать сохраненную процедуру p или функцию f, которые связаны с базой данных test, Вы можете скомандовать CALL test.p() или test.f().
Когда база данных удалена, все сохраненные подпрограммы, связанные с ней, также удалены.

В MySQL 5.0.0 сохраненные подпрограммы глобальны и не связаны с базой данных. Они наследуют заданную по умолчанию базу данных с вызывающего оператора. Если внутри подпрограммы выполнено USE db_name, первоначальная заданная по умолчанию база данных восстановлена после стандартного выхода.

MySQL поддерживает самое полезное расширение, которое позволяет использование регулярных инструкций SELECT (то есть, без того, чтобы использовать курсоры или локальные переменные) внутри сохраненной процедуры. Набор результатов такого запроса просто послан непосредственно пользователю. Многократные инструкции SELECT генерируют много наборов результатов, так что пользователь должен применять библиотеку клиентов MySQL, что поддерживает много наборов результатов. Это означает, что библиотека должна быть от MySQL не ниже 4.1. Пользователь должен также определить опцию CLIENT_MULTI_STATEMENTS, когда соединяется. Для программ на C, это может быть выполнено функцией mysql_real_connect() из C API.

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

5.2.1. Синтаксис `CREATE PROCEDURE` и `CREATE FUNCTION`

CREATE
   [DEFINER = { user | CURRENT_USER }]
   PROCEDURE sp_name ([proc_parameter[,...]])
   [characteristic ...] routine_body

CREATE
   [DEFINER = { user | CURRENT_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:
Любой допустимый тип данных MySQL

characteristic:
LANGUAGE SQL | [NOT] DETERMINISTIC
   | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
   | SQL SECURITY {DEFINER | INVOKER}
   | COMMENT 'string'

routine_body:
Допустимая инструкция процедуры SQL

Эти инструкции создают сохраненные подпрограммы. С MySQL 5.0.3, чтобы использовать их, необходимо иметь привилегию CREATE ROUTINE. Если двоичная регистрация допускается, эти инструкции могут также требовать привилегии SUPER. MySQL автоматически предоставляет создателю привилегии ALTER ROUTINE и EXECUTE.

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

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

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

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

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

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

Обратите внимание: определение параметра как IN, OUT или INOUT допустимо только для PROCEDURE! Параметры FUNCTION всегда расцениваются как параметры IN.

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

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

Предложение RETURNS может быть определено только для FUNCTION, для которой это является обязательным. Это указывает тип возврата функции, и функциональное тело должно содержать инструкцию RETURN value.

routine_body состоит из допустимой инструкции процедуры SQL. Это может быть простая инструкция типа SELECT или INSERT, либо это может быть составная инструкция, использующая BEGIN и END. Синтаксис составного оператора описан в разделе "5.2.5. Синтаксис составного оператора BEGIN ... END". Составные инструкции могут содержать объявления, циклы и другие инструкции управляющей структуры. Синтаксис для этих инструкций описан позже в этой главе. Некоторые инструкции не позволяются в сохраненных подпрограммах.

Инструкция CREATE FUNCTION использовалась в более ранних версиях MySQL, чтобы поддерживать UDFs (user-defined functions). UDF продолжает обеспечиваться, даже с существованием сохраненных функций. UDF может быть расценен как внешняя сохраненная функция. Однако, обратите внимание, что сохраненные функции совместно используют их пространство имен с UDF.

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

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

В настоящее время характеристика DETERMINISTIC принята, но еще не используется оптимизатором. Однако, если двоичная регистрация допускается, эта характеристика воздействует на то, которые стандартные определения MySQL принимает.

Несколько характеристик обеспечивают информацию относительно характера использования данных подпрограммой. CONTAINS SQL указывает, что подпрограмма не содержит инструкции, которые читают или записывают данные. NO SQL указывает, что подпрограмма не содержит никаких инструкций SQL. READS SQL DATA указывает, что подпрограмма содержит инструкции, которые читают данные, но не инструкции, чтобы те данные записывать. MODIFIES SQL DATA указывает, что подпрограмма содержит инструкции, которые могут записывать данные. CONTAINS SQL значение по умолчанию, если ни одна из этих характеристик не дана явно. Эти характеристики только консультативные. Сервер не использует их, чтобы ограничить то, какие виды инструкций подпрограмме позволено выполнить.

Характеристика SQL SECURITY может использоваться, чтобы определить, должна ли подпрограмма быть выполнена, используя разрешения пользователя, который создает подпрограмму, или пользователя, который ее вызывает. Значение по умолчанию: DEFINER. Это свойство ново в SQL:2003. Создатель или исполнитель должен иметь разрешение обратиться к базе данных, с которой подпрограмма связана. Начиная с MySQL 5.0.3, необходимо иметь привилегию EXECUTE, чтобы выполнить подпрограмму. Пользователь, который должен иметь эту привилегию, является создателем или исполнителем в зависимости от того, как установлена характеристика SQL SECURITY.

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

Если дано значение user, это должно быть логином MySQL в формате 'user_name'@'host_name' (тот же самый формат используется в инструкции GRANT). Параметры user_name и host_name обязательны. CURRENT_USER также может быть дан как CURRENT_USER(). Заданное по умолчанию для DEFINER: пользователь, который выполняет CREATE PROCEDURE, CREATE FUNCTION или инструкцию (аналогично DEFINER=CURRENT_USER).

Если Вы определяете предложение DEFINER, Вы не можете устанавливать значение к любому логину (только к Вашему собственному), если Вы не имеете привилегию SUPER. Эти правила определяют допустимые значения пользователя DEFINER:

Если Вы не имеете привилегию SUPER, единственное допустимое значение user: Ваш собственный логин, определенный буквально или используя CURRENT_USER. Вы не можете устанавливать DEFINER к некоторому другому логину.
Если Вы имеете привилегию SUPER, Вы можете определять любой синтаксически допустимый логин. Если он фактически не существует, будет сгенерировано предупреждение.

Хотя возможно создать подпрограммы с несуществующим значением DEFINER, ошибка происходит, если подпрограмма выполняется с привилегиями DEFINER, но сам DEFINER не существует во время выполнения.

MySQL сохраняет установку переменной системы sql_mode, которая была во время создания подпрограммы, и всегда выполняет подпрограмму именно с этой установкой.

Когда подпрограмма вызывается, выполняется неявное USE db_name (и отменяется по завершении). Инструкции USE внутри сохраненных подпрограмм отвергнуты.

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

Назначения проверены на предмет несоответствия типов данных и переполнение. Преобразование и проблемы переполнения приводит к предупреждениям или ошибкам в строгом режиме.
Для символьных типов данных, если имеется предложение CHARACTER SET в объявлении, используются определенный набор символов и заданное по умолчанию объединение. Если не имеется никакого такого предложения, используются наборы символов базы данных и объединение, которые были актуальными во время написания подпрограммы (они заданы значениями переменных системы character_set_database и collation_database). Атрибут COLLATE не обеспечивается. Это включает использование BINARY, потому что в этом контексте BINARY определяет двоичное объединение набора символов.
Только скалярные значения могут быть назначены к параметрам или переменным. Например, инструкция типа SET x=(SELECT 1,2) глубоко ошибочна.

До MySQL 5.0.18 параметры, возвращаемые значения и локальные переменные обрабатываются как элементы в выражениях, и подчиненны автоматическому (тихому) преобразованию и усечению. Сохраненные функции игнорируют установку sql_mode.

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

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

Сохраненные подпрограммы не могут использовать LOAD DATA INFILE.

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

Обратите внимание: до MySQL 5.0.10 сохраненные функции, созданные с CREATE FUNCTION, не должны содержать ссылки к таблицам, с ограниченными исключительными ситуациями. Они могут включать некоторые инструкции SET, которые содержат ссылки на таблицы, например, SET a:= (SELECT MAX(id) FROM t), и инструкции SELECT, которые выбирают значения непосредственно в переменные, например, SELECT i INTO var1 FROM t.

Следующее показывает пример простой сохраненной процедуры, которая использует параметр OUT. Пример использует команду delimiter клиента mysql, чтобы изменить операторный разделитель с ; на // в то время как процедура определяется. Это позволяет использовать разделитель ; в теле процедуры, которое будет передано на сервер.

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

При использовании команды delimiter, Вы должны избежать использования наклонной черты влево (\), потому что это escape-символ для MySQL.

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

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

Сохраненная функция возвращает значение типа данных, определенного в предложении RETURNS. Если инструкция RETURN возвращает значение иного типа, значение приведено к соответствующему типу. Например, если функция возвращает значение ENUM или SET, но инструкция RETURN возвращает целое число, значение, возвращенное из функции: строка для соответствующего ENUM-члена набора элементов SET.

5.2.2. Синтаксис `ALTER PROCEDURE` и `ALTER FUNCTION`

ALTER {PROCEDURE | FUNCTION} sp_name
      [characteristic ...]

characteristic:
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
   | SQL SECURITY { DEFINER | INVOKER }
   | COMMENT 'string'

Эта инструкция может использоваться, чтобы изменить характеристики сохраненной процедуры или функции. Начиная с MySQL 5.0.3, Вы должны иметь привилегию ALTER ROUTINE для подпрограммы. Эта привилегия предоставляется автоматически стандартному создателю. Если двоичная регистрация допускается, эта инструкция могла бы также требовать привилегии SUPER. Больше чем одно изменение может быть определено в команде ALTER PROCEDURE или ALTER FUNCTION.

5.2.3. Синтаксис `DROP PROCEDURE` и `DROP FUNCTION`

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Эта инструкция используется, чтобы удалить сохраненную процедуру или функцию. То есть определенная подпрограмма будет удалена с сервера. Начиная с MySQL 5.0.3, Вы должны иметь привилегию ALTER ROUTINE для подпрограммы. Такая привилегия предоставляется автоматически создателю.

Предложение IF EXISTS является расширением MySQL. Это предотвращает ошибку, если процедура или функция не существует. Будет произведено предупреждение, которое может просматриваться с помощью команды SHOW WARNINGS.

5.2.4. Синтаксис команды `CALL`

CALL sp_name([parameter[,...]])

Инструкция CALL вызывает процедуру, которая была определена предварительно, с помощью команды CREATE PROCEDURE.

CALL может передавать обратно значения к параметрам использования вызывающего оператора, которые объявлены как OUT или INOUT. Это также вернет число строк, на которые воздействовал запрос, это же значение программа пользователя может получать в уровне SQL, вызывая функцию ROW_COUNT() или из C API функцией mysql_affected_rows().

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

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

Перед вызовом процедуры, инициализируйте переменную, которая будет передана как параметр INOUT. После вызова процедуры, значения в двух переменных будут установлены или изменены:

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+------------+------------+
| @version   | @increment |
+------------+------------+
| 5.0.25-log | 11         |
+------------+------------+

Если Вы пишете программы на C, которые выполняют сохраненные процедуры с инструкцией SQL CALL, Вы должны установить флажок CLIENT_MULTI_RESULTS, когда Вы вызываете mysql_real_connect(), либо установить CLIENT_MULTI_STATEMENTS. Это потому, что каждый CALL возвращает результат, чтобы указать состояние обращения, в дополнение к любым наборам результатов, которые могли бы быть возвращены инструкциями, выполненными внутри процедуры. Чтобы обрабатывать результат инструкции CALL, используйте цикл, который вызывает mysql_next_result() чтобы определить, имеется ли большее количество результатов.

5.2.5. Синтаксис составного оператора `BEGIN ... END`

   [begin_label:] BEGIN
   [statement_list]
END [end_label]

Синтаксис BEGIN ... END используется для записи составных инструкций, которые могут появляться внутри сохраненных подпрограмм и триггеров. Составная инструкция может содержать много инструкций, взятых в ключевые слова BEGIN и END. Здесь statement_list представляет список инструкций. Каждая инструкция внутри statement_list должна быть завершена операторным разделителем точкой с запятой (;). Обратите внимание, что список statement_list факультативный, что означает, что пустая составная инструкция (BEGIN END) допустима.

Использование многих инструкций требует, чтобы пользователь был способен послать операторные строки, содержащие операторный разделитель ;. Это обработано в клиенте командной строки mysql командой delimiter. Изменение разделителя конца инструкции ; (например, на //) позволяет ; использоваться в стандартном теле.

Составная инструкция может быть помечена. end_label не может быть дан, если begin_label также не присутствует. Если оба присутствуют, они должны быть те же самые.

Факультативное предложение [NOT] ATOMIC еще не обеспечивается. Это означает, что никакие точки сохранения транзакций не установлены в начале блока команды, и предложение BEGIN, используемое в этом контексте не имеет никакого эффекта на текущей (актуальной) транзакции.

5.2.6. Синтаксис `DECLARE`

Инструкция DECLARE используется, чтобы определить различные элементы, локальные для подпрограммы:

Локальные переменные. Подробности в разделе "5.2.7. Переменные в сохраненных подпрограммах".
Условия и драйверы. Подробности в разделе "5.2.8. Условия и драйверы ".
Курсоры. Подробности в разделе "5.2.9. Курсоры".

Инструкции SIGNAL и RESIGNAL в настоящее время не обеспечиваются.

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

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

5.2.7. Переменные в сохраненных подпрограммах

Вы можете объявлять и использовать переменные внутри подпрограммы.

5.2.7.1. Локальные переменные `DECLARE`

DECLARE var_name[,...] type
        [DEFAULT value]

Эта инструкция используется, чтобы объявить локальные переменные. Чтобы обеспечивать значение по умолчанию для переменной, включите предложение DEFAULT. Значение может быть определено как выражение, оно не обязательно должно быть константой. Если предложение DEFAULT отсутствует, начальное значение NULL.

Локальные переменные обрабатываются подобно стандартным параметрам относительно типа данных и проверки переполнения.

Область (контекст) локальной переменной: внутри блока BEGIN ... END, где это объявлено. Переменная может упоминаться в блоках, вложенных внутри блока объявления, за исключением тех блоков, которые объявляют переменную с тем же самым именем.

5.2.7.2. Команда `SET`

SET var_name = expr
    [, var_name = expr] ...

Инструкция SET в сохраненных подпрограммах представляет собой расширенную версию общей инструкции SET. Вызванные переменные могут быть объявлены внутри подпрограммы или глобальными переменными системы.

Инструкция SET в сохраненных подпрограммах выполнена как часть синтаксиса SET. Это позволяет расширенный синтаксис SET a=x, b=y, ..., где различные виды переменных (локально объявленные, глобальные и переменные сеанса сервера могут быть смешаны. Это также позволяет комбинации локальных переменных и некоторых параметров, которые имеют смысл только для переменных системы. В этом случае, параметры распознаны, но игнорируются.

5.2.7.3. Команда `SELECT ... INTO`

SELECT col_name[,...]
       INTO var_name[,...] table_expr

Этот синтаксис SELECT сохраняет выбранные столбцы непосредственно в переменные. Следовательно, только одиночная строка может быть получена.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

Имена переменных пользователя не чувствительны к регистру. Важно: имена переменных SQL не должны совпадать с именами столбцов. Если инструкция SQL, типа SELECT ... INTO, содержит ссылку к столбцу, и есть объявленная локальная переменная с тем же самым именем, MySQL в настоящее время интерпретирует ссылку как имя переменной. Например, в следующей инструкции xname интерпретируется как ссылка к переменной, а не к столбцу с именем xname:

CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE newname VARCHAR(5);
  DECLARE xid INT;
  SELECT xname, id INTO newname, xid FROM table1 WHERE xname = xname;
  SELECT newname;
END;

Когда эта процедура вызвана, переменная newname возвращает значение 'bob' независимо от значения столбца table1.xname.

5.2.8. Условия и драйверы

Некоторые условия могут требовать специфической обработки. Эти условия могут касаться ошибок или общего управления потоком данных внутри подпрограммы.

5.2.8.1. `DECLARE` условий

DECLARE condition_name CONDITION FOR
        condition_value

condition_value:
SQLSTATE [VALUE] sqlstate_value
   | mysql_error_code

Эта инструкция определяет условия, которые нуждаются в специфической обработке. Это сопоставляет имя с определенным условием ошибки. Имя может впоследствии использоваться в инструкции DECLARE HANDLER.

Здесь condition_value может быть значением SQLSTATE или же кодом ошибки MySQL.

5.2.8.2. `DECLARE` драйвера

DECLARE handler_type HANDLER FOR
        condition_value[,...] statement

handler_type:
   CONTINUE | EXIT | UNDO

condition_value:
SQLSTATE [VALUE] sqlstate_value
   | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
   | mysql_error_code

Инструкция DECLARE ... HANDLER определяет драйверы, с каждым из которых может иметь дело одно или большее количество условий. Если одно из этих условий происходит, определенная инструкция statement будет выполнена. Инструкция может быть простой инструкцией (например, SET var_name = value), или это может быть составной инструкцией, записанной с помощью парочки BEGIN и END.

Для драйвера CONTINUE выполнение текущей подпрограммы продолжается после выполнения инструкции драйвера. Для драйвера EXIT выполнение завершается для составной инструкции BEGIN ... END, в которой драйвер объявлен. Это истинно, даже если условие происходит во внутреннем блоке. Инструкция типа драйвера UNDO еще не обеспечивается (пока?).

Если происходит условие, для которого никакой драйвер не был объявлен, заданное по умолчанию действие: EXIT.

Параметр condition_value может быть любым из следующих значений:

Значение SQLSTATE или MySQL-код ошибки.
Имя условия, предварительно определенное с DECLARE ... CONDITION.
SQLWARNING (краткая запись для всех кодов SQLSTATE, которые начинаются с 01).
NOT FOUND (краткая запись для всех кодов SQLSTATE, которые начинаются с 02).
SQLEXCEPTION (краткая запись для всех кодов SQLSTATE, не охваченных SQLWARNING или NOT FOUND).

Пример:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

Пример сопоставляет драйвер с SQLSTATE 23000, который происходит для ошибки дублирования ключа. Обратите внимание, что @x равен 3, это показывает что MySQL перейдет к концу процедуры. Если строка DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; не была представлена, MySQL примет заданный по умолчанию путь (EXIT) после второй неудачи INSERT из-за ограничения PRIMARY KEY, а SELECT @x возвратит 2.

Если Вы хотите игнорировать условие, Вы можете объявлять драйвер CONTINUE для этого и сопоставлять его с пустым блоком. Например:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

5.2.9. Курсоры

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

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

Пример:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  OPEN cur2;
  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN INSERT INTO test.t3 VALUES (a,b);
          ELSE INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;
  CLOSE cur2;
END

5.2.9.1. Объявление курсоров

DECLARE cursor_name
        CURSOR FOR select_statement

Эта инструкция объявляет курсор. Много курсоров может быть объявлено в подпрограмме, но каждый курсор в данном блоке должен иметь уникальное имя.

Инструкция SELECT не может иметь предложение INTO.

5.2.9.2. Инструкция `OPEN`

OPEN cursor_name

Эта инструкция открывает предварительно объявленный курсор.

5.2.9.3. Инструкция `FETCH`

FETCH cursor_name
INTO var_name [, var_name] ...

Эта инструкция выбирает следующую строку (если строка существует), используя определенный открытый курсор, и продвигает указатель курсора.

Если больше нет доступных строк, происходит условие No Data со значением SQLSTATE 02000. Чтобы обнаружить это условие, Вы можете установить драйвер для этого. Пример показывается в разделе "5.2.9. Курсоры".

5.2.9.4. Инструкция `CLOSE`

CLOSE cursor_name

Эта инструкция закрывает предварительно открытый курсор. Если курсор не закрыт явно, он все равно закроется в конце составной инструкции, в которой он был объявлен.

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

Конструкции IF, CASE, LOOP, WHILE, REPLACE ITERATE и LEAVE полностью выполнены.

Многие из этих конструкций содержат другие инструкции, как обозначено спецификациями грамматики в следующих разделах. Такие конструкции могут быть вложены. Например, IF мог бы содержать цикл времени, который непосредственно содержит WHILE, который в свою очередь включает в себя оператор CASE. Циклы FOR в настоящее время не обеспечиваются.

5.2.10.1. Инструкция `IF`

IF search_condition THEN statement_list
   [ELSEIF search_condition
   THEN statement_list] ...
   [ELSE statement_list]
END IF

IF реализован как базисная условная конструкция. Если выражение search_condition истинно, соответствующий список инструкции SQL выполнен. Если пары search_condition не нашлось, будет выполнен операторный список в предложении ELSE. Каждый statement_list состоит из одной или большего количества инструкций.

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

5.2.10.2. Инструкция `CASE`

   CASE case_value WHEN when_value
        THEN statement_list
        [WHEN when_value THEN statement_list] ...
        [ELSE statement_list]
   END CASE

Или:

   CASE WHEN search_condition THEN statement_list
        [WHEN search_condition THEN statement_list] ...
        [ELSE statement_list]
   END CASE

Инструкция CASE для сохраненных подпрограмм осуществляет сложную условную конструкцию. Если search_condition равно true, соответствующий список инструкций SQL выполнен. Если никакие условия не подошли, отрабатывается операторный список в предложении ELSE. Каждый statement_list состоит из одной или большего количества инструкций.

Обратите внимание: синтаксис инструкции CASE, показанной здесь для использования внутри сохраненных подпрограмм немного отличается от такового выражения в SQL. Инструкция CASE не может иметь предложение ELSE NULL, и она завершена END CASE вместо END.

5.2.10.3. Инструкция `LOOP`

   [begin_label:] LOOP
    statement_list
   END LOOP [end_label]

LOOP осуществляет простую конструкцию цикла, допуская повторенное выполнение операторного списка, который состоит из одной или большего количества инструкций. Инструкции внутри цикла повторены, пока цикл не покидается. Обычно это выполнено инструкцией LEAVE.

Инструкция LOOP может быть помечена. end_label не может быть дан, если нет begin_label. Если оба присутствуют, они должны быть те же самые.

5.2.10.4. Инструкция `LEAVE`

LEAVE label

Эта инструкция используется, чтобы из выйти любой помеченной конструкции управления потоком данных. Это может использоваться внутри BEGIN ... END или же конструкций цикла (LOOP, REPEAT, WHILE).

5.2.10.5. Инструкция `ITERATE`

ITERATE label

ITERATE может появляться только внутри инструкций LOOP, REPEAT и WHILE. ITERATE означает "выполнить цикл снова ".

Пример:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

5.2.10.6. Инструкция `REPEAT`

[begin_label:]
  REPEAT statement_list
    UNTIL search_condition
  END REPEAT
[end_label]

Операторный список внутри инструкции REPEAT повторен, пока search_condition равно true. Таким образом, REPEAT всегда проходит цикл по крайней мере один раз. Перечень statement_list состоит из одной или большего числа инструкций. Инструкция REPEAT может быть помечена по обычным правилам.

mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

5.2.10.7. Инструкция `WHILE`

[begin_label:]
  WHILE search_condition DO statement_list
  END WHILE
[end_label]

Операторный список внутри инструкции WHILE повторен, пока search_condition равно true. Инструкция WHILE может быть помечена. Пример:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END

5.3. Сохраненные процедуры, функции, триггеры и `LAST_INSERT_ID()`

Внутри тела сохраненной подпрограммы (процедуры или функции) или триггера значение LAST_INSERT_ID() меняется по обычным правилам. Эффект сохраненной подпрограммы или триггера на значение LAST_INSERT_ID(), который замечен следующими инструкциями, зависит от вида подпрограммы:

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

5.4. Сохраненные процедуры, функции, триггеры и репликация

В MySQL 5.0 сохраненные процедуры и функции работают с репликацией?

Да, стандартные действия, выполненные в сохраненных процедурах и функциях, скопируются. Имеются несколько ограничений, которые описаны подробно в разделе "5.5. Двоичная регистрация сохраненных подпрограмм и триггеров".
Будут ли сохраненные процедуры и функции, созданные на главном сервере, скопированы на подчиненный?

Да, создание сохраненных процедур и функций, выполненное через нормальные инструкции DDL, скопируется на подчиненный, так что объекты будут существовать на обеих серверах. Инструкции ALTER и DROP для сохраненных процедур и функций также скопируются.
Как реплицируются действия, которые происходят внутри сохраненных процедур и скопированных функций?

MySQL записывает каждое событие DML, которое происходит в сохраненной процедуре, и копирует эти индивидуальные действия на подчиненный сервер. Фактические обращения, сделанные, чтобы выполнить сохраненные процедуры не скопируются. Сохраненные функции, которые изменяют данные, регистрируются как функциональные вызовы, а не как события DML, которые происходят внутри каждой функции.
Есть ли специальные требования защиты для использования сохраненных процедур и функций вместе с репликацией?

Да. Поскольку подчиненный сервер имеет полномочия, выполнить любое операторное чтение из двоичного файла регистрации главного сервера, специальные ограничения защиты существуют для использования сохраненных функций с репликацией. Если репликация или двоичная регистрация вообще (с целью восстановления в контрольной точке активна, то MySQL DBA имеет два параметров защиты для них:
- Любому пользователю, желающему создать сохраненные функции, нужно предоставлять SUPER привилегию.
- В качестве альтернативы, DBA может устанавливать переменную системы log_bin_trust_function_creators в 1, что позволяет любому со стандартной привилегией CREATE ROUTINE создавать сохраненные функции.
Обратите внимание: до MySQL 5.0.16 эти ограничения также относятся к сохраненным процедурам, и переменная системы именована log_bin_trust_routine_creators.
Какие ограничения существуют для копирования сохраненной процедуры и функциональных действий?

Не детерминированные (произвольные) или основанные на времени действия, внедренные в сохраненных процедурах, не могут копироваться правильно. Для них очень характерны беспорядочно произведенные не предсказуемые результаты, которые не могут быть точно воспроизведены, а, следовательно, произвольные действия, скопированные на подчиненный сервер, не будут отражать, что именно выполнили на главном сервере. Обратите внимание, что объявление сохраненных функций как DETERMINISTIC или установка переменной системы log_bin_trust_function_creators в 0 не будет позволять вызывать произвольно оцененные операции.

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

В заключение, нетранзакционные таблицы, для которых происходят ошибки в течение больших действий DML (типа объемных вставок), могут испытывать проблемы дублирования, в которых главный сервер может частично модифицироваться из действия DML, но никакие модификации не выполнены на подчиненном из-за ошибок, которые произошли. Обойти проблему можно с ключевым словом IGNORE так, чтобы модификации на главном сервере, которые вызывают ошибки, игнорировались, а модификации, которые не вызывают ошибок, скопируются на подчиненный.
Предшествующие ограничения воздействуют на способность MySQL делать восстановление до контрольной точки?

Те же самые ограничения, которые воздействуют на репликацию, воздействуют и на восстановление до контрольной точки.
Что будет делать MySQL, чтобы исправить вышеупомянутые ограничения?

Будущий выпуск MySQL, как ожидается, даст выбор в том, как репликация должна быть обработана:
- Операторно-основанная репликация (текущая реализация).
- Дублирование уровня строки (которое решит все ограничения, описанные ранее).
Триггеры работают с репликацией?

Триггеры и репликация в MySQL 5.0 работают также как в большинстве других СУБД: действия, выполненные через триггеры на главном сервере, не скопируются на подчиненный. Вместо этого, триггеры, которые существуют на таблицах, которые постоянно находятся на главном сервере, должны быть созданы на соответствующих таблицах на любых подчиненных серверах так, чтобы триггеры активизировались там соответственно главному.
Как действия, выполненные через триггер на главном сервере, скопированы на подчиненный?

Сначала, триггеры, которые существуют на главном сервере, должны быть вновь созданы на подчиненном сервере. Если это выполнено, поток дублирования работает как любая другая стандартная инструкция DML, которая участвует в дублировании. Например, рассмотрите таблицу EMP, которая имеет триггер AFTER insert, существующий на главном сервере. Та же самая таблица и триггер существуют также и на подчиненном сервере. Поток дублирования был бы:
1. Инструкция INSERT сделана в EMP.
2. Триггер AFTER сработал на EMP.
3. Инструкция INSERT записана в двоичный файл регистрации.
4. Подчиненный сервер подбирает инструкцию INSERT к EMP и выполняет ее.
5. Триггер AFTER в EMP, который существует на подчиненном сервере, активизируется.

5.5. Двоичная регистрация сохраненных подпрограмм и триггеров

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

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

Этот раздел описывает разработку двоичной регистрации в MySQL 5.0 относительно сохраненных подпрограмм (процедуры и функции) и триггеров. Обсуждение сначала подводит итог изменений, которые имели место в реализации регистрации, а затем приводит текущие условия использования сохраненных подпрограмм.

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

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

Разработка регистрации сохраненной подпрограммы в MySQL 5.0 может быть получена в итоге следующим образом:

До MySQL 5.0.6: в начальной реализации регистрации сохраненной подпрограммы, инструкции, которые создают сохраненные подпрограммы, и инструкции CALL не регистрируются. Эти вычеркивания могут вызывать проблемы для восстановления данных и дублирования.
MySQL 5.0.6: инструкции, которые создают сохраненные подпрограммы, и инструкции CALL регистрируются. Сохраненные функциональные вызовы регистрируются, когда они происходят в инструкциях, которые модифицируют данные (потому что те инструкции регистрируются). Однако, функциональные вызовы не регистрируются, когда они происходят в инструкциях типа SELECT, которые не изменяют данные, даже если изменение данных происходит непосредственно внутри функции, это может вызвать проблемы. При некоторых обстоятельствах, функции и процедуры могут иметь различные эффекты если выполнено в разное время или на различных серверах (главном или подчиненном), и таким образом они могут быть опасны для восстановления данных или дублирования. Чтобы обрабатывать это, приняты меры, чтобы позволить идентификацию безопасных подпрограмм и предотвратить создание опасных подпрограмм (за исключением создаваемых пользователями с достаточными привилегиями).
MySQL 5.0.12: для сохраненных функций, когда функциональный вызов, который изменяет данные, происходит внутри не регистрируемой инструкции типа SELECT, сервер регистрирует инструкцию DO func_name(), которая вызывает функцию так, чтобы функция была выполнена в течение восстановления данных или дублирования на подчиненные серверы. Для сохраненных процедур, сервер не регистрирует инструкции CALL. Вместо этого, он регистрирует индивидуальные инструкции внутри процедуры, которые выполнены в результате CALL. Это устраняет проблемы, которые могут происходить, когда процедура выполнялась бы на подчиненном сервере иным способом, чем на главном.
MySQL 5.0.16: процедура, регистрирующая изменения, сделанные в 5.0.12, позволяет ослабить условия на опасных подпрограммах для сохраненных процедур. Следовательно, интерфейс пользователя для управления этими условиями пересмотрен, чтобы применить только к функциям. Создатели процедуры больше не связаны ими.
MySQL 5.0.17: регистрация сохраненных функций также, как и инструкции DO func_name() (для изменений, сделанных в 5.0.12), регистрируется как инструкции SELECT func_name() для лучшего контроля проверки ошибок.

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

Чтобы создавать или изменять сохраненную функцию, Вы должны иметь привилегию SUPER, в дополнение к привилегии CREATE ROUTINE или ALTER ROUTINE, которая обычно требуется.
Когда Вы создаете сохраненную функцию, Вы должны объявить, что это детерминировано или не изменяет данные. Иначе, это может быть опасно для восстановления данных или дублирования. Два набора функциональных характеристик применяются здесь:
- Характеристики DETERMINISTIC и NOT DETERMINISTIC указывают, производит ли функция всегда тот же самый результат для входных данных. Значение по умолчанию: NOT DETERMINISTIC, если никакая характеристика не дана, так что Вы должны определить DETERMINISTIC явно, чтобы объявить, что функция детерминирована.
  
  Использование функции NOW() (или синонимов) либо RAND() не обязательно делает функцию не детерминированной. Для NOW() двоичный файл регистрации включает timestamp и все копирует правильно. RAND() также копируется правильно, пока это вызывается только один раз внутри функции. Вы можете рассматривать функциональное выполнение timestamp и начальное значение случайного числа как неявные вводы, которые являются идентичными на главном и подчиненном серверах.
  
  SYSDATE() не воздействует на timestamp в двоичном файле регистрации, так что это заставляет сохраненные подпрограммы быть не детерминированными, если используется регистрация, основанная на командах. Этого не происходит, если сервер запущен с опцией --sysdate-is-now, чтобы заставить SYSDATE() быть псевдонимом для NOW().
- Характеристики CONTAINS SQL, NO SQL, READS SQL DATA и MODIFIES SQL DATA обеспечивают информацию относительно того, читает ли функция или записывает данные. NO SQL или READS SQL DATA указывают, что функция не изменяет данные, но Вы должны определить одну из них явно, потому что значение по умолчанию: CONTAINS SQL, если никакая характеристика не дана.
По умолчанию для инструкции CREATE FUNCTION, которая будет принята, должны быть определены явно DETERMINISTIC или что-то из NO SQL и READS SQL DATA. Иначе происходит ошибка:
```
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled (you
*might* want to use the less safe log_bin_trust_function_creators variable)
```
Оценка характера функции основана на честности ее создателя: MySQL не проверяет, что функция, объявленная DETERMINISTIC, не содержит никаких инструкций, которые производят не детерминированные результаты.
Чтобы ослабить предшествующие условия на функциональном создании (что Вы должны иметь привилегию SUPER, и что функция должна быть объявлена детерминированной или не изменять данные), установите глобальную переменную системы log_bin_trust_function_creators в 1. По умолчанию, эта переменная имеет значение 0, но Вы можете изменить это:
```
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
```
Вы можете также устанавливать эту переменную, используя опцию --log-bin-trust-function-creators при старте сервера.

Если двоичная регистрация не допускается, log_bin_trust_function_creators не применяется, и для стандартного создания не требуется привилегия SUPER.

Триггер подобен сохраненным функциям, так что предшествующие замечания относительно функций также относятся к ним со следующей исключительной ситуацией: CREATE TRIGGER не имеет факультативной характеристики DETERMINISTIC, так что триггеры приняты, чтобы быть всегда детерминированными. Однако, это предположение могло бы в некоторых случаях быть недопустимым. Например, функция UUID() не детерминирована (и не копируется!). Вы должны быть внимательны относительно использования таких функций в триггерах.

Триггер может модифицировать таблицы (начиная с MySQL 5.0.10), так что сообщения об ошибках, подобны тем же для сохраненных функций с CREATE TRIGGER, если Вы не имеете привилегии SUPER, а log_bin_trust_function_creators равна 0.

5.6 MySQL 5 FAQ по хранимым подпрограммам

5.6.1: Есть ли форум для обсуждения сохраненных подпрограмм в MySQL?

Да. http://forums.mysql.com/list.php?98.

5.6.2: Где я могу найти спецификацию ANSI SQL 2003 для сохраненных процедур?

К сожалению, официальные спецификации не свободно доступны (ANSI делает их доступными для приобретения). Однако, имеются книги, типа SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer, которые дают всесторонний краткий обзор стандарта, включая покрытие сохраненных процедур.

5.6.3: Как управлять сохраненными подпрограммами?

Лучше использовать чистую схему наименования сохраненных подпрограмм. Вы можете управлять сохраненными подпрограммами с помощью CREATE [FUNCTION|PROCEDURE], ALTER [FUNCTION|PROCEDURE], DROP [FUNCTION|PROCEDURE] и SHOW CREATE [FUNCTION|PROCEDURE]. Вы можете получать информацию относительно существующих сохраненных процедур, используя таблицу ROUTINES в базе данных INFORMATION_SCHEMA.

5.6.4: Есть ли способ просматривать все сохраненные процедуры и функции в базе данных?

Да. Для базы данных dbname используйте этот запрос к таблице INFORMATION_SCHEMA.ROUTINES:

SELECT ROUTINE_TYPE, ROUTINE_NAME
       FROM INFORMATION_SCHEMA.ROUTINES
       WHERE ROUTINE_SCHEMA='dbname';

Тело сохраненной подпрограммы может просматриваться, используя SHOW CREATE FUNCTION (для сохраненной функции) или SHOW CREATE PROCEDURE (для сохраненной процедуры).

5.6.5: Где сохраненные процедуры сохранены?

В таблице proc базы данных mysql. Однако, Вы не должны обращаться к таблицам в базе данных системы непосредственно. Вместо этого, используйте SHOW CREATE FUNCTION, чтобы получить информацию относительно сохраненных функций и SHOW CREATE PROCEDURE, чтобы получить информацию относительно сохраненных процедур.

Вы можете также сделать запрос к таблице ROUTINES в базе данных INFORMATION_SCHEMA для информации относительно этой таблицы.

5.6.6: Возможно ли группировать сохраненные процедуры или функции в пакеты?

Нет. Это не обеспечивается в MySQL 5.1.

5.6.7: Может сохраненная процедура вызывать другую сохраненную процедуру?

Да.

5.6.8: Может сохраненная процедура вызывать триггер?

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

5.6.9: Может сохраненная процедура обращаться к таблицам?

Да. Сохраненная процедура может обращаться к таблицам.

5.6.10: Может сохраненная процедура выдать ошибку прикладной программы?

В MySQL 5.1 нет. Предполагается выполнять стандартные SQL-инструкции SIGNAL и RESIGNAL в будущем.

5.6.11: Может сохраненная процедура обеспечивать обработку особых ситуаций?

MySQL осуществляет определения HANDLER согласно стандарту SQL.

5.6.12: Может сохраненная процедура в MySQL 5.1 вернуть набор результатов?

Сохраненная процедура может, а вот сохраненная функция нет. Если Вы выполняете обычный SELECT внутри сохраненной процедуры, набор результатов возвращен непосредственно пользователю. Вы должны использовать клиент-серверный протокол MySQL 4.1 (или выше), чтобы это сработало. Это означает, что например, в PHP Вы должны использовать расширение mysqli вместо mysql.

5.6.13: WITH RECOMPILE обеспечивается для сохраненных процедур?

В MySQL 5.1 нет.

5.6.14: Есть ли в MySQL аналог mod_plsql как шлюза к Apache, чтобы общаться непосредственно с сохраннеными процедурами в базе данных?

Не имеется никакого эквивалента в MySQL 5.1.

5.6.15: Я могу передавать массив как ввод сохраненной процедуре?

В MySQL 5.1 нет.

5.6.16: Я могу передавать курсор как параметр IN для сохраненной процедуры?

В MySQL 5.1 курсоры доступны только внутри сохраненных процедур.

5.6.17: Я могу возвращать курсор как параметр OUT из сохраненной процедуры?

В MySQL 5.1 курсоры доступны только внутри сохраненных процедур. Однако, если Вы не открываете курсор на SELECT, результат будет послан непосредственно пользователю. Вы можете также применить SELECT INTO в переменные.

5.6.18: Я могу распечатывать значение переменной внутри сохраненной подпрограммы для целей отладки?

Да, Вы можете делать это в сохраненной процедуре, но не в сохраненной функции. Если Вы выполняете обычный SELECT внутри сохраненной процедуры, набор результатов возвращен непосредственно пользователю. Вы должны будете использовать протокол MySQL 4.1 (или выше). В PHP Вы должны использовать расширение mysqli вместо mysql.

5.6.19: Я могу передавать или отменять транзакции внутри сохраненной процедуры?

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

Партнёры:

Хостинг:

Закладки на сайте
Проследить за страницей

Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру

5.1. Сохраненные подпрограммы и таблицы предоставления привилегий

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

5.2.1. Синтаксис CREATE PROCEDURE и CREATE FUNCTION

5.2.2. Синтаксис ALTER PROCEDURE и ALTER FUNCTION

5.2.3. Синтаксис DROP PROCEDURE и DROP FUNCTION

5.2.4. Синтаксис команды CALL

5.2.5. Синтаксис составного оператора BEGIN ... END

5.2.6. Синтаксис DECLARE

5.2.7. Переменные в сохраненных подпрограммах

5.2.7.1. Локальные переменные DECLARE

5.2.7.2. Команда SET

5.2.7.3. Команда SELECT ... INTO

5.2.8. Условия и драйверы

5.2.8.1. DECLARE условий

5.2.8.2. DECLARE драйвера

5.2.9. Курсоры

5.2.9.1. Объявление курсоров

5.2.9.2. Инструкция OPEN

5.2.9.3. Инструкция FETCH

5.2.9.4. Инструкция CLOSE

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

5.2.10.1. Инструкция IF

5.2.10.2. Инструкция CASE

5.2.10.3. Инструкция LOOP

5.2.10.4. Инструкция LEAVE

5.2.10.5. Инструкция ITERATE

5.2.10.6. Инструкция REPEAT

5.2.10.7. Инструкция WHILE

5.3. Сохраненные процедуры, функции, триггеры и LAST_INSERT_ID()

5.4. Сохраненные процедуры, функции, триггеры и репликация

5.5. Двоичная регистрация сохраненных подпрограмм и триггеров

5.6 MySQL 5 FAQ по хранимым подпрограммам

5.2.1. Синтаксис `CREATE PROCEDURE` и `CREATE FUNCTION`

5.2.2. Синтаксис `ALTER PROCEDURE` и `ALTER FUNCTION`

5.2.3. Синтаксис `DROP PROCEDURE` и `DROP FUNCTION`

5.2.4. Синтаксис команды `CALL`

5.2.5. Синтаксис составного оператора `BEGIN ... END`

5.2.6. Синтаксис `DECLARE`

5.2.7.1. Локальные переменные `DECLARE`

5.2.7.2. Команда `SET`

5.2.7.3. Команда `SELECT ... INTO`

5.2.8.1. `DECLARE` условий

5.2.8.2. `DECLARE` драйвера

5.2.9.2. Инструкция `OPEN`

5.2.9.3. Инструкция `FETCH`

5.2.9.4. Инструкция `CLOSE`

5.2.10.1. Инструкция `IF`

5.2.10.2. Инструкция `CASE`

5.2.10.3. Инструкция `LOOP`

5.2.10.4. Инструкция `LEAVE`

5.2.10.5. Инструкция `ITERATE`

5.2.10.6. Инструкция `REPEAT`

5.2.10.7. Инструкция `WHILE`

5.3. Сохраненные процедуры, функции, триггеры и `LAST_INSERT_ID()`