Разработка сайтов и программного обеспечения, системное администрирование, обучение программированию и работе с СУБД MySQL

in english

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

Главная Статьи Хранимые процедуры и триггеры

Внимание! Данная работа построена на основе перевода раздела «17.1. Stored Routines and the Grant Tables» описания ПО MySQL 5.0.19, «Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision:995)»
``Сначала прочти все, а потом пробуй примеры"

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

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

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

Это может быть полезным тогда, когда:

 

Хранимые процедуры и функции (подпрограммы) могут обеспечить лучшую производительность потому, что меньше информации требуется для пересылки между клиентом и сервером. Выбор увеличивает нагрузку на сервер БД, но снижает затраты на стороне клиента. Используйте это, если много клиентских машин (таких как Веб-серверы) обслуживаются одной или несколькими БД.

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

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

От слов к делу…

Хранимые процедуры требуют наличия таблицы proc в базе mysql. Эта таблица обычно создается во время установки сервера БД. Если вы апгрейдите СУБД более ранних версий, то вам следует почитать секцию 2.10.3, “Upgrading the Grant Tables”. Сейчас вместо нее читайте Upgrade MySQL System Tables.

При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицей mysql.proc

Начиная с MySQL 5.0.3 требуются следующие привилегии:

CREATE ROUTINE для создания хранимых процедур

ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю  процедуры (функции)

EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.

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

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

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

MySQL поддерживает полностью расширения, которые разрешают юзать обычные SELECT выражения (без использования курсоров или локальных переменных) внутри хранимых процедур. Результирующий набор, возвращенный от запроса, а просто отправляется напрямую клиенту. Множественный SELECT запрос генерирует множество результирующих наборов, поэтому клиент должен использовать библиотеку, поддерживающую множественные результирующие наборы.

CREATE PROCEDURE – создать хранимую процедуру.

CREATE FUNCTION – создать хранимую функцию.

Синтаксис:

CREATE PROCEDURE имя_процедуры ([параметр_процедуры[,...]])
[характеристёика ...] тело_подпрограммы

CREATE FUNCTION имя_функции ([параметр_функции[,...]])
RETURNS тип
[характеристика ...] тело_подпрограммы

параметр_процедуры:
[ IN | OUT | INOUT ] имя_параметра тип
параметр_функции:
имя_параметра тип

тип:
Любой тип данных MySQL

характеристика:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

тело_подпрограммы:
Правильное SQL выражение.

 

Рассмотрим все на практике.

Сначала создадим хранимую процедуру следующим запросом:

CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ''
BEGIN
select val1+val2 into 't' from `my` LIMIT 0,1;
END;

 

Применение выражения LIMIT в этом запросе сделано из соображений того, что не любой клиент способен принять многострочный результирующий набор.

После этого вызовем ее:

CALL my_proc(@a);
SELECT @a;

 

Для отделения внутреннего запроса от внешнего всегда используют разделитель отличный от обычно (для задания используют команду DELIMITER <строка/символ>)

Вот еще один пример с учетом всех требований. 

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)

Готово.

Весь процесс можно пронаблюдать на рисунке ниже:

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

Триггеры

Поддержка триггеров появилась в MySQL начиная с версии 5.0.2.

Триггер – поименованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, события связанного с этой таблицей.

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

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

Триггер в MySQL

Объявим переменную sum и присвоим ей значение 1. После этого при каждой вставке в таблицу account значение этой переменной будет увеличивать согласно вставляемой части.

Замечание. Если значение переменной не инициализировано, то триггер работать не будет!

Ошибка триггера в MySQL

 

Синтаксис создания триггера

CREATE
[DEFINER = { имя_ пользователя | CURRENT_USER }]
TRIGGER имя_триггера время_триггера событие_срабатывания_триггера
ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера

Если с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.

время_триггера

Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.

событие_срабатывания_триггера

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

MySQL_dc.chm (1.7 MB) - подборка документации по MySQL.

 

Дополнительная литература

Автор в Google+

Реклама:

Метки: MySQL, stored, routines, triggers, procedure, хранимые процедуры.

Комментарии:

SerMick:
Статья очень помогла.Спасибо!
zoonman.ru:
Вы можете задать свои вопросы на форуме http://xpoint.ru/forums/computers/dbms/mysql/forum.xhtml
oldoshak:
Очень хорошая статья - большое спасибо!
m0m0k0:
Большое спасибо. Классная статья!
вымяч:
характеристёика
Вадим:
Мало информации. Хотелось бы увидеть описание всех параметров процедур и триггеров.
Мотор4uk:
Инфа нормальная, 100% труъ. Афтору зачет!
Иван:
Возможно я несколько не внимателен, но я не увидел использование и применение функций. К тому же функции вызываются не с помощью CALL а с помощью SELECT. С уважением Иван
zoonman:
The CALL statement invokes a procedure that was defined previously with CREATE PROCEDURE.

CALL can pass back values to its caller using parameters that are declared as OUT or INOUT parameters. It also “returns” the number of rows affected, which a client program can obtain at the SQL level by calling the ROW_COUNT() function and from C by calling the mysql_affected_rows() C API function.

As of MySQL 5.1.13, stored procedures that take no arguments now can be invoked without parentheses. That is, CALL p() and CALL p are equivalent.
Юрий:
Хранимая функция остается навсегда ? когда она может стать не активной ? или удалиться?
zoonman:
Хранимая процедура активна постоянно после того, как вы ее создали. Но ее можно удалить.
Например:

DROP PROCEDURE имя_процедуры;
Лили:
Не совсем понятно: сначало пишется CREATE ROUTINE для создания хранимых процедур, а потом CREATE PROCEDURE – создать хранимую процедуру. В чем разница?
zoonman:
CREATE ROUTINE - это наименование требуемой привилегии.
zoonman:
Создание временных таблиц необходимо производить после объявления переменных через DECLARE.
Сергей:
О, эпоха...
Неужели нельзя писать по-русски? Т.е., без "заюзать" и т.п. словесного хлама.
Читать статью тяжело, чего я до конца и не сделал.
Илья:
В общих чертах всё ясно и понятно. Кому что то интересно будут гуглить дальше по интересующим словам)
Виталий:
Для тех, кто имеет минимальный опыт программирования в СУБД и решил освоить именно процедуры, функции и триггеры MySQL - статья самое то для старта в нужном направлении - СПАСИБО!
Марина:
Отличная статья, спасибо!
А можно ли внутри триггера использовать условный оператор? Если событие срабатывания триггера - когда значение в одном из столбцов таблицы устанавливается (INSERT или UPDATE) больше определенного.
Например, имеется таблица "users" на форуме, в ней указано количество сообщений kol. когда количество сообщений больше 30, пользователю присваивается рейтинг в графе rating - "профи".

CREATE TRIGGER rating_up AFTER kol>30 FOR EACH ROW SET rating='Профи';
имя:

e-mail (не публикуется):

комментарий:

© Ткачев Филипп, 2005—2017
Программист, веб-разработка и прикладное ПО.
Все права защищены.