Долго мучался с этим вопросом. Литературы в интернете мало, особенно на
русском языке. Пришлось поспрашивать на зарубежных форумах, глубже
покопаться в мануалах и разъяснить для себя некоторые непонятные
моменты. Итак, коротко о хранимых процедурах в MySQL.
Stored procedures - что это?
Хранимые
процедуры появились начиная с 5 версии MySQL. Они позволяют
автоматизировать сложные процессы на уровне MySQL, нежели использовать
для этого внешние скрипты. Это даёт нам наиболее высокую скорость
выполнения, т.к. мы не гоняем большое количество запросов, а всего лишь
один раз вызываем ту или иную процедуру (или функцию).
Что для
этого нужно? Установите MySQL сервер версии 5 или выше
(dev.mysql.com/downloads). Процедуры можно создавать как запросы,
например через командную строку MySQL, но для удобства советую скачать
MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Данный пакет
включает в себя три программы - MySQL Administrator, MySQL Query
Browser и MySQL Migration Toolkit. Нам понадобятся первые две. (Хотя
можно обойтись одним MySQL Query Browser, но все эти $$ в хранимых
процедурах иногда могут сбить с толку).
Первая хранимая процедура
Итак,
открываем MySQL Administrator, подключаемся к серверу MySQL и создаем
новую схему (базу данных): щелкните Catalogs, выберите Create New
Schema в области Schemata (Ctrl+N). Назовите ее как-нибудь (например
db). Откройте только что созданную схему, выберите вкладку Stored
procedures и щелкните кнопку Create Stored Proc. Назовите свою
процедуру procedure1. В тело процедуры (между BEGIN и END) впишите
следующее:
SELECT "This is my stored procedure";
И нажмите Execute SQL - процедура создана. Откройте MySQL Query Browser, выберите свою схему (db) и впишите следующий запрос:
CALL procedure1();
Вуала! Поздравляю.
Переменные в MySQL
Для
того, чтобы извлечь каку-то пользу от хранимых процедур в MySQL, вам
придется поработать с переменными. Так как это не входи в рамки данной
статьи, покажу лишь несколько примеров.
Простые переменные
DECLARE iVar INT DEFAULT 0;
SET iVar = 5;
SELECT * FROM `data` WHERE `id` = iVar;
DECLARE iVar INT DEFAULT 0;
SELECT COUNT(*) INTO iVar FROM `data`;
Системные переменные
SET @iVar = 5;
SELECT @iVar;
Разница между простыми и системными
переменными в том, что системные переменные доступны из вне хранимой
процедуры. То есть, чтобы извлечь какие-то данные нужно пользоваться
системными, а переменные которые нужны только внутри процедуры должны
быть простыми.
Параметры в хранимых процедурах
Здесь тоже всё достаточно просто. Изменяем первую строку, объявляющая саму процедуру:
CREATE PROCEDURE `procedure1`(IN iInput1 INT, IN iInput2 INT)
Здесь, ключевое слово IN указывает на
то, что параметр указан только для чтения. Далее с этим параметром
работаем как с обычной переменной внутри процедуры:
SELECT * FROM `data` WHERE `id` = iInput1 AND `id2` = iInput2;
Условия, Циклы. IF THEN ELSE, WHILE
Условия
и циклы вам обязательно понадобятся при написании комплексных хранимых
процедур, но зацикливаться на этой теме не буду. Думаю хоть какие-то
навыки программирования у вас есть, так что покажу всего лишь синтаксис.
IF условие THEN
действие;
ELSE
действие;
END IF;
WHILE условие DO
действие;
END WHILE;
Простой пример
Один
из хороших случаев применения хранимых процедур - тогда, когда вам
нужно объединить несколько запросов в один, например добавление темы в
форум и увеличение общего количества тем. Допустим таблица threads
CREATE TABLE `threads` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR(255) NOT NULL,
`tag` VARCHAR(255) NOT NULL,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;
Здесь title у нас будет заголовком новой
темы. Ну и таблица, например с различными статистическими переменными
сайта, в том числе общее количество тем в форме.
CREATE TABLE `variables` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL,
`value` INT NOT NULL DEFAULT 0,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;
Тут вроде всё понятно, допустим у нас
там есть запись с name = threads и value = 0. Создадим новую хранимую
процедуру procedure2.
CREATE PROCEDURE `procedure2`(IN sTitle VARCHAR(255))
BEGIN
INSERT INTO `threads` (`title`) VALUES (sTitle);
UPDATE `variables` SET `value` = `value` + 1 WHERE `name` = 'threads';
END
Объяснять особо нечего, просто два запроса объединили в один. Теперь мы можем вызвать эту процедуру таким образом:
CALL procedure2('My new thread');
Таким
образом, вместо того, чтобы передать два или больше запросов (например
через php), мы можем передать один - оптимизация, чистый код и можно
изменить в любой момент не затрагивая другие скрипты.
Курсоры (MySQL Cursors)
Курсоры
позволяют пройтись по всем полученным результатам запроса. На теории
объяснить сложно, покажу на практике. Добавим еще одну таблицу к нашей
базе данных - hits:
CREATE TABLE `tags` (
`id` INT NOT NULL AUTO_INCREMENT ,
`tag` VARCHAR(255) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM
Сюда мы будем записывать все тэги из всех тем. Хранимая процедура будет выглядеть примерно так:
CREATE PROCEDURE `procedure3`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE sTag VARCHAR(255);
DECLARE iCount INT DEFAULT 0;
DECLARE rCursor CURSOR FOR
SELECT `tag` FROM `threads` WHERE 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN rCursor;
FETCH rCursor INTO sTag;
WHILE done = 0 DO
SELECT COUNT(*) INTO iCount FROM `tags` WHERE `tag` = sTag;
IF iCount = 0 THEN
INSERT INTO `tags` (`tag`) VALUES (sTag);
END IF;
FETCH rCursor INTO sTag;
END WHILE;
CLOSE rCursor;
END
Подробно. Процедура пройдет через каждую
тему, каждый тег пробьет по таблице tags, и если данный тег
отсутствует, то она его добавит.
Курсор для запроса SELECT,
который выберет теги из всех тем (WHERE 1). После курсора объявляем
что-то вроде исключения - что делать, когда результаты кончатся
(SQLSTATE ‘02000′ означает это окончание). В этом случае мы в
переменную done запишем 1, чтобы в последствии выйти из цикла.
Открываем
курсор, и получаем первую запись. Дальше в цикле - Выбираем количество
совпадений из таблицы тегов для текущего тега и помещаем результат в
переменную iCount. Если результатов нет, то запросом INSERT вставляем
новый тег.
В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.
Извлечение данных
Вспомним
системные переменные и рассмотрим еще одну манипуляцию над нашими
таблицами - получить общее количество тегов и тем. Перейдем сразу к
процедуре:
CREATE PROCEDURE `procedure4`()
BEGIN
DECLARE iTags INT DEFAULT 0;
DECLARE iThreads INT DEFAULT 0;
SELECT COUNT(*) INTO iTags FROM `tags`;
SELECT COUNT(*) INTO iThreads FROM `threads`;
SET @tags = iTags, @threads = iThreads;
END
Объявляем две переменных - iTags - количество тегов, и iThreads - общее количество тем.
Далее
два простых запроса на выборку, заполняя наши переменные. Ну и в конце
присваиваем системным переменным значения текущих простых переменных.
При вызове данная процедура ничего не возвращает, но после ее вызова мы
можем считать требуемые значения из системных переменных:
CALL procedure4();
SELECT @tags, @threads;
Заключение
А заключения и не будет ;)
|
