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 ‘020002 означает это окончание). В этом случае мы в переменную 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;

 


Заключение



А заключения и не будет ;) буду рад ответить на ваши вопросы - пишите в отзывы.

Читать комменты и комментировать

Добавить комментарий / отзыв



Защитный код
Обновить

Комментарии   

 
+2 # Федор 01.08.2011 16:11
Мне необходимо обновить все строки таблицы определенными значениями, а каждое значение = "set N=(SELECT count(*) FROM ... WHERE ...)", притом N для каждой ячеки таблицы - свой(его необходимо вычислить для каждой ячейки таблицы). Кто-нибудь, может привести пример хранимой процедуры на MySQL для решения подобной задачи.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # Андрей 09.10.2012 18:10
Извлечение данных курсором в процедуре, как в Firebird, Oracle или MSSQL в MySQL не предусмотрено? Даже для небольших и несложных баз это может оказаться очень даже кстати, с чем я неожиданно и столкнулся...
Ответить | Ответить с цитатой | Цитировать
 
MySQL хранимые процедуры | | 2010-09-09 00:20:26 | | Базы данных | | Долго мучался с этим вопросом. Литературы в интернете мало, особенно на русском языке. Пришлось поспрашивать на зарубежных форумах, глубже покопаться в мануалах и разъяснить для себя некоторые | РэдЛайн, создание сайта, заказать сайт, разработка сайтов, реклама в Интернете, продвижение, маркетинговые исследования, дизайн студия, веб дизайн, раскрутка сайта, создать сайт компании, сделать сайт, создание сайтов, изготовление сайта, обслуживание сайтов, изготовление сайтов, заказать интернет сайт, создать сайт, изготовить сайт, разработка сайта, web студия, создание веб сайта, поддержка сайта, сайт на заказ, сопровождение сайта, дизайн сайта, сайт под ключ, заказ сайта, реклама сайта, хостинг, регистрация доменов, хабаровск, краснодар, москва, комсомольск |
 
Поделиться с друзьями: