MySQL хранимые процедуры

Константин

Долго мучался с этим вопросом. Литературы в интернете мало, особенно на русском языке. Пришлось поспрашивать на зарубежных форумах, глубже покопаться в мануалах и разъяснить для себя некоторые непонятные моменты. Итак, коротко о хранимых процедурах в 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;

Ссылки по теме

MySQL хранимые процедуры (исходники)