Процедуры
Что это такое и что из себя представляют?
Хранимые процедуры представляют собой объекты базы данных со списком SQL инструкций, который один раз компилируется и хранится на сервере. Хранимые процедуры по своему виду очень похожи на процедуры языков высокого уровня (например, в Pascal или C). Она имеет входные и выходные параметры, локальные переменные, возможность использования операторов SQL (SELECT, INSERT и т.п.), управляющие структуры и даже возможность рекурсивного вызова, которая по умолчанию отключена. Но обо всем по порядку.
Почему и для чего используется
- Для хранения на сервере часто используемого запроса (своеобразное повторное использование кода). Что позволяет экономить трафик, за счет того, что не передаются сотни команд, часто довольно сложных запросов или транзакций.
- Для вычислений, обработки и изменений данных в базе.
- Для облегчения клиента. Так как все процедуры хранятся и выполняются на сервере, то нагрузка на клиента уменьшается.
- Для защиты данных. Использование однородного "API" для доступа к данным обеспечивает непротиворечивую и безопасную среду выполнения запросов и т.п. Пользователи не имеют прямого доступа к таблицам в базе данных а могут выполнять только сохраненные подпрограммы (процедуры/функции). Цель - оградить пользователя от прямого доступа к базе данных,оставив им возможность выполнять только хранимые процедуры. Такая изоляция обеспечивает целостность и надежность базы данных, а также снижает вероятность таких действий как SQL-иньекции.
- Для работы с разнообразным прикладным ПО и платформами. На разных клиентах и платформах будет выполняться однородный доступ к базе данных, что тоже создает дополнительную безопасность при работе с базой.
Как создать
Все хранимые процедуры хранятся в таблице proc в базе данных с именем mysql, эта таблица создается в процессе установки MySQL сервера.
Хранимая процедура создается командой CREATE PROCEDURE, вызывается процедура командой CALL и передает возвращаемые значения использую специальные переменные вывода.
Процедуры могут иметь, а могут и не иметь вовсе параметров. Параметры имеют различные спецификаторы:
- IN - означает, что значение параметра передается внутрь процедуры;
- OUT - данный спецификатор означает, что параметр может передаваться или возвращаться;
- INOUT - означает, что значение передается в процедуру, но может ею изменяться.
Каждый параметр представляет собой по умолчанию параметр IN. Чтобы определять иное для параметра, используйте ключевое слово OUT или INOUT перед именем параметра.
Определение параметра как IN, OUT или INOUT допустимо только для процедур. Параметры FUNCTION всегда расцениваются как параметры IN.
Типы аргументов задаются ключевыми такими словами как INT, FLOAT, CHAR, DATE и т.д.Для лучшего понимания напишу небольшую процедуру в виде скрипта сохраненного в текстовый файл create_proc.txt:
USE flats;
DROP PROCEDURE IF EXISTS create_procedure;
DELIMITER $$
CREATE PROCEDURE create_procedure(
IN user INT,
OUT money FLOAT
)
BEGIN
SELECT sum
INTO money
FROM accounts
WHERE id=user;
END$$
DELIMITER ;
это можно сделать такой командой mysql сервера:mysql> source /home/ihor/create_proc.txt
Также можно создать процедуру, записывая команды прямо в командной строке mysql сервера:
mysql> use flats;
Database changed
mysql> drop procedure if exists create_procedure;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure create_procedure(
-> in user int,
-> out money float
-> )
-> begin
-> select sum into money from accounts where id=user;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
Тело процедуры помещается между операторами BEGIN и END.На этом создание процедуры завершено. Теперь в MySQL сервере, в базе данных mysql, в таблице proc создана запись о процедуре и о том, что все операции процедура будет производить с базой данных flats.
Как использовать
Применяя внутри процедуры запросы SELECT, можно возвращать множество результатов в консоль mysql сервера. Такие множества результатов могут обрабатываться, используя клиентские приложения, курсоры, другие сохраненные процедуры, возвращая им указатель на полученное из запроса множество.
Инструкция CALL вызывает процедуру, которая была определена предварительно, с помощью команды CREATE PROCEDURE. В контексте созданной выше процедуры, вызов инструкции CALL будет выглядеть так:
mysql> set @balance = 10; mysql> call create_procedure(25, @balance); mysql> select @balance; +----------+ | @balance | +----------+ | 1260 | +----------+Показательный пример использования параметров 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;Чтобы вернуть значение из процедуры, использующей параметр OUT или INOUT, передайте процедуре параметр посредством переменной пользователя, и затем проверьте значение переменной после возврата из процедуры. Переменная пользователя обозначается символом @ (коммерческое at).
Результат работы данной процедуры будет такой:
mysql> SET @increment = 10; mysql> CALL p(@version, @increment); mysql> SELECT @version, @increment; +------------+------------+ | @version | @increment | +------------+------------+ | 5.0.25-log | 11 | +------------+------------+
Особенности использования
При первом запуске хранимой процедуры, она компилируется и в дальнейшем обращение к этой хранимой процедуре осуществляется быстрее.
Рекурсивные процедуры по-умолчанию заблокированы, но могут допускаться на сервере, предварительно установив переменную max_sp_recursion_depth в ненулевое значение. Функции же не могут быть рекурсивными.
Список всех созданных процедур можно посомтреть командой:
mysql> show procedure status;
Посмотреть код процедуры можно командой:mysql> show create procedure flat_action \G;
Флаг \G используется для удобочитаемого вывода кода.Удалить процедуру можно командой:
mysql> drop procedure if exists flat_action;
Функции
Отличие процедур от функций:
- В функции в качестве входных параметров используется только спецификатор IN. Так же этот спецификатор не обязательно указывать т.к. и в процедурах и функция он является по умолчанию.
- В качестве OUT, INOUT в функциях используется ключевое RETURN.
- Функция не может возвращать множество результатов.
Для чего используется хранимые функции
Хранимые функции по сути являются аналогом хранимых процедур. Функции применяются для большего соответствия языкам программирования. Функция, как и в языках высокого уровня, рассматривается как выражение формирующее на выход одно значение. Хранимые функции применяются для расширения функциональных возможностей операторов SELECT и ряда других SQL-операторов.
Функции могут применяться в арифметических выражениях, вместо переменных, параметров процедур или функций, а также прямо в составе SQL-операторов. Все эти свойства позволяют в расширить функциональные возможности языка SQL, как средства разработки приложений.
Как создать
В качестве примера создается функция возвращающая колличество пользователей в списке. Понятно, что это можно сделать гораздо проще и писать для этого отдельную функцию не целесообразно, но данный пример хорошо покажет все основные части конструкции функций.
DROP FUNCTION IF EXISTS Count_of_users; DELIMITER $$ CREATE FUNCTION Count_of_users() returns SMALLINT DETERMINISTIC BEGIN DECLARE Count_users SMALLINT; SELECT COUNT(*) INTO Count_users FROM flats.accounts; RETURN Count_users; END$$ DELIMITER ;Обратите внимание на описание возращаемого параметра (returns SMALLINT), стоящее сразу после формального описания функции. Процедура или функция рассматривается как детерминированная (DETERMINISTIC), если она всегда производит тот же самый результат для тех же самых входных параметров, или недетерминированная (NOT DETERMINISTIC) в противном случае. Если при определении функции не задано DETERMINISTIC или NOT DETERMINISTIC, берется значение по умолчанию NOT DETERMINISTIC.
Как использовать
Вызов функции происходит не так как выхов процедуры. Для вызова используется запрос SELECT; так же можно присвоить вывод функции пользовательской переменной.
mysql> select Count_of_users(); +------------------+ | Count_of_users() | +------------------+ | 3268 | +------------------+Список всех созданных функций можно посомтреть командой:
mysql> show function status;
Посмотреть код функции можно командой:mysql> show create function Count_of_users \G;
Флаг \G используется для удобочитаемого вывода кода.Удалить функцию можно командой:
mysql> drop function if exists Count_of_users;
Триггер представляет собой хранимую процедуру особого типа, вызов которой происходит по указанной операции модификации данных (DELETE, UPDATE, INSERT). Триггер запускается автоматически при изменении данных в таблице, с которой он связан. Так же определяется момент вызова триггера: BEFORE (триггер запускается до связанного с ним события) или AFTER (триггер запускается после события).
Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики.
Создание триггера
Приведу простой показательный пример. Это вымышленная предметная область и задача для триггера, которая позволит нам понять принцип работы триггеров. Существует две таблицы:
mysql> select * from accounts; +----+------+------------+ | id | sum | minus_date | +----+------+------------+ | 1 | 100 | 0000-00-00 | | 2 | 75 | 2000-03-05 | | 3 | 50 | 2000-03-05 | | 4 | 150 | 2000-03-24 | | 5 | 155 | 0000-00-00 | +----+------+------------+ 5 rows in set (0.00 sec) mysql> select * from balance; +----+--------------+---------+ | id | user_balance | user_id | +----+--------------+---------+ | 1 | 100 | 1 | | 2 | 100 | 2 | | 3 | 100 | 3 | | 4 | 100 | 4 | | 5 | 100 | 5 | +----+--------------+---------+ 5 rows in set (0.00 sec)Теперь создадим триггер который при любом добавлении записи в таблицу accounts будет записывать параметры id и sum в соответствующие поля user_id и user_balance таблицы balance.
DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `update_balance`$$ CREATE TRIGGER `update_balance` AFTER INSERT on `accounts` FOR EACH ROW BEGIN INSERT INTO balance (user_balance, user_id) VALUES(NEW.sum, NEW.id); END$$ DELIMITER ;Выполнив вышеприведенный mysql-скрипт, предварительно выбрав нужную базу данных, мы создадим для нее триггер.
Проверим работу триггера, добавив в таблицу accounts новую запись.
mysql> insert into accounts (sum) values(250); Query OK, 1 row affected (0.10 sec) mysql> select * from accounts; +----+------+------------+ | id | sum | minus_date | +----+------+------------+ | 1 | 100 | 0000-00-00 | | 2 | 75 | 2000-03-05 | | 3 | 50 | 2000-03-05 | | 4 | 150 | 2000-03-24 | | 5 | 155 | 0000-00-00 | | 6 | 250 | NULL | +----+------+------------+ 6 rows in set (0.00 sec) mysql> select * from balance; +----+--------------+---------+ | id | user_balance | user_id | +----+--------------+---------+ | 1 | 100 | 1 | | 2 | 100 | 2 | | 3 | 100 | 3 | | 4 | 100 | 4 | | 5 | 100 | 5 | | 6 | 250 | 0 | +----+--------------+---------+Результат получился не такой как мы ожидали: в user_id записано значение 0 вместо ожидаемого значения 6. Это произошло из-за того что значение момента вызова триггера было установлено как BEFORE, поэтому добавление значений в таблицу balance произошло раньше чем добавление в таблицу account. Вы можете спросить: "Но мы ведь получили значение sum!". Да, верно, но поле id в таблице является автоинкрементом и на момент добавления значений в таблицу balance было еще не добавлено в таблицу accounts, поэтому и имело нулевое значение.
Если повторить эксперимент, предварительно изменив в скрипте значение момента выполнения триггера с BEFORE на AFTER, то получим нужный нам результат.
Список всех триггеров для конкретной базы данных можно посомтреть командой:
mysql> show triggers;
Код триггера можно посмотреть командой:
mysql> show create trigger update_balance;
Удалить триггер можно командой:
mysql> drop trigger update_balance;
Заключение
Пользуйтесь материалом с умом, всё проверяйте на практике. Если нужны дополнительные пояснения - пишите в комментариях. С большим удовольствием на всё отвечу.
Полезные ссылки:
http://www.rldp.ru/mysql/mysqlpro/sto_proc.htm
http://ru.wikipedia.org/wiki/Save_proc
http://citforum.ru/database/sql_any/sql_064.shtml
http://habrahabr.ru/post/37693/
http://ru.wikipedia.org/wiki/Триггер
Мне нравится как изложен материал. Идея проста, но когда спустя годы снова нужно сделать хранимку, подобного рода статья скорее всего хорошо поможет )
ОтветитьУдалить