(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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.

1

2

3

4

5
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

Сюда мы будем записывать все тэги из всех тем. Хранимая процедура будет выглядеть примерно так:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24
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 вставляем новый тег.

В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.

Извлечение данных

Вспомним системные переменные и рассмотрим еще одну манипуляцию над нашими таблицами - получить общее количество тегов и тем. Перейдем сразу к процедуре:

1

2

3

4

5

6

7

8

9

10
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;

 

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


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Дискуссии и обсуждения общего плана »
Написать редактору 
 Рекомендовать » Дата публикации: 12.01.2009 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Avira System Speedup, 1 ПК, 1 год
VideoStudio Ultimate 2018 ML. Электронный ключ
Business Studio 4.2 Enterprise. Конкурентная лицензия + Business Studio Portal 4.2. Пользовательская именная лицензия.
DevExpress / ASP.NET Subscription
IBM DOMINO COLLABORATION EXPRESS AUTHORIZED USER ANNUAL SW SUBSCRIPTION & SUPPORT RENEWAL
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Программирование на Microsoft Access
CASE-технологии
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
Компьютерный дизайн - Все графические редакторы
СУБД Oracle "с нуля"
Утиль - лучший бесплатный софт для Windows
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Помощь по MS Access (328)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
Модульный метод создания сайтов (5)
Модульный метод создания (компоновки) сайтов на основе наборов типовых отлаженных функциональных...
 
Excell не печатает :-( (2)
Почему то Excell не печатает страницы в альбомной ориентаций, то есть страница в аольбомной...
 
ODBC: dBase CREATE TABLE... (9)
Есть проблема Из программы на CTD 2.0 PTF3 подключаемся к DBF-файлам Set SqlDatabase='Файлы...
 
Как сменить пароль для схемы SYSTEM? (6)
Как сменить пароль по умолчанию для схемы SYSTEM c 'manager' на свой? То же и для SYS
 
 
 



    
rambler's top100 Rambler's Top100