- Все для CMS | Joomla, Drupal, phpBB, Wordpress, DLE, IPB - http://cmsart.ru -

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

Posted By Archi On 20.08.2012 @ 8:50 дп In MySQL | No Comments

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


Заключение

А заключения и не будет ;)

Источник:  internet-technologies.ru [1]


Article printed from Все для CMS | Joomla, Drupal, phpBB, Wordpress, DLE, IPB: http://cmsart.ru

URL to article: http://cmsart.ru/articles/mysql-articles/mysql-hranimyie-protseduryi

URLs in this post:

[1] internet-technologies.ru: http://cmsart.ru/goto/http://www.internet-technologies.ru

Copyright © 2012 Поддержка CMS | Документация, статьи по Joomla, Drupal, phpBB. All rights reserved.