Хранимые процедуры и триггеры. MySQL хранимые процедуры
Теперь давайте узнаем, как можно посмотреть, какие хранимые процедуры имеются у нас на сервере, и как они выглядят. Для этого познакомимся с двумя операторами:
- SHOW PROCEDURE STATUS - позволяет просмотреть список имеющихся хранимых процедур. Правда просматривать этот список не очень удобно, т.к. по каждой процедуре выдается информация об имени БД, к которой процедура принадлежит, ее типе, учетной записи, от имени которой была создана процедура, о дате создания и изменения процедуры и т.д. И все-таки, если вам необходимо посмотреть, какие процедуры у вас есть, то стоит воспользоваться этим оператором.
- SHOW CREATE PROCEDURE имя_процедуры - позволяет получить информацию о конкретной процедуре, в частности просмотреть ее код. Вид для просмотра также не очень удобный, но разобраться можно.
SELECT * FROM mysql.proc//
То получим нечто такое же нечитабельное, как и при использовании операторов SHOW. Поэтому мы будем создавать запросы с условиями. Например, если мы создадим вот такой запрос:
SELECT name FROM mysql.proc//
То получим имена всех процедур всех баз данных, имеющихся на сервере. Нас, например, на данный момент интересуют только процедуры базы данных shop, поэтому изменим запрос:
Вот теперь мы получили то, что хотели:
Если же мы хотим посмотреть только тело конкретной процедуры (т.е. от begin до end), то мы напишем такой запрос:
И увидим вполне читабельный вариант:
Вообще, чтобы извлекать из таблицы proc необходимую вам информацию, надо просто знать, какие столбцы она содержит, а для этого можно воспользоваться знакомым нам с первого урока оператором describe имя_таблицы , в нашем случае describe mysql.proc . Правда, вид у нее тоже не очень читабельный, поэтому приведем здесь названия наиболее востребованных столбцов:
- db - имя БД, в которую сохранена процедура.
- name - имя процедуры.
- param_list - список параметров процедуры.
- body - тело процедуры.
- comment - комментарий к хранимой процедуре.
Комментарии вещь крайне необходимая, ведь через какое-то время мы может забыть, что делает та или иная процедура. Конечно, по ее коду можно восстановить нашу память, но зачем? Гораздо проще сразу при создании процедуры указать, что она делает, и тогда, даже по прошествии долгого времени, обратившись к комментариям, мы сразу вспомним, зачем эта процедура создавалась.
Создавать комментарии крайне просто. Для этого сразу после списка параметров, но еще до начала тела хранимой процедуры указываем ключевое слово COMMENT "здесь комментарий" . Давайте удалим нашу процедуру sum_vendor и создадим новую, с комментарием:
CREATE PROCEDURE sum_vendor(i INT) COMMENT "Возвращает сумму товара по идентификатору поставщика." begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //
А теперь сделаем запрос к комментарию процедуры:
Вообще-то, чтобы добавить комментарий, вовсе не обязательно было удалять старую процедуру. Можно было отредактировать имеющуюся хранимую процедуру с помощью оператора ALTER PROCEDURE . Давайте посмотрим, как это сделать, на примере процедуры ins_cust из прошлого урока. Эта процедура вводит информацию о новом покупателе в таблицу Покупатели (customers). Давайте добавим комментарий к этой процедуре:
ALTER PROCEDURE ins_cust COMMENT "Вводит информацию о новом покупателе в таблицу Покупатели."//
И сделаем запрос к комментарию, чтобы проверить:
В нашей базе данных всего две процедуры, и комментарии к ним кажутся излишними. Не ленитесь, обязательно пишите комментарии.
Представьте, что в нашей базе данных десятки или сотни процедур. Сделав нужный запрос, вы без труда узнаете, какие процедуры есть
и что они делают и поймете, что комментарии - это не излишества, а экономия вашего времени в будущем. Кстати, а вот и сам запрос:
Ну вот, теперь мы умеем извлекать любую информацию о наших процедурах, что позволит нам ничего не забыть и не запутаться.
Представляем вашему вниманию новый курс от команды The Codeby - "Тестирование Веб-Приложений на проникновение с нуля". Общая теория, подготовка рабочего окружения, пассивный фаззинг и фингерпринт, Активный фаззинг, Уязвимости, Пост-эксплуатация, Инструментальные средства, Social Engeneering и многое другое.
Хранимые процедуры MySQL (часть 1)
Продолжаем работать базой данных mytest, созданной в предыдущей . Сегодня добавим в наше приложение возможность ответа на комментарий пользователя, а так же научимся создавать хранимые процедуры и функции.
Создание хранимой процедуры
Открываем phpmyadmin. Выбираем базу данных mytest и нажимаем на её заголовок, либо на значок Browse. Затем переходим на вкладку Routines и создаём новую процедуру, нажав на надпись Add routine.
Появится форма, которую необходимо заполнить.
Routine Name (имя процедуры/функции) ReplyToComment .
Type (тип) — процедура. Отличие процедуры от функции в том, что функция всегда возвращает какое-то значение и содержит оператор return.
Parameters (параметры) наша процедура будет принимать два параметра: текст ответа и id комментария, на который мы отвечаем. Оба параметра будут передаваться из нашего клиентского приложения.
Создание параметров процедуры
Создадим первый параметр
Direction указываем направление параметра (IN, OUT, INOUT). В нашей процедуре оба передаваемых параметра будут входящими (IN).
Name (имя параметра) Content .
Type (тип) INT, VARCHAR, DATETIME и так далее. Параметр Content содержит текст ответа, который будет храниться в столбце comment_content. Данная колонка имеет определенный тип, чтобы его определить, открываем таблицу wp_comments и переходим на вкладку Structure, находим нужное нам имя столбца и смотрим на его тип в колонке Type. В данном примере колонка имеет тип - text, этот же тип нужно указать для нашего параметра.
Length/Values (длина или значение) для типа Text данное поле установить нельзя, но обычно здесь указывается длина, например VARCHAR(20), INT(10), либо какое-то значение по умолчанию.
Options в качестве дополнительных опций можно указать текущую кодировку столбца, её так же можно посмотреть на вкладке Structure колонка Collation. Установим значение utf8.
результат
Добавим второй параметр, нажав на кнопку Add parameter.
Direction - IN Name - ComID Type - BIGINT Length/Values - 20 Options - UNSIGNED
Оба параметра созданы, продолжаем заполнять форму.
Definition здесь мы описываем тело процедуры. Тело представляет собой блок, который начинается с ключевого слова BEGIN и заканчиваются ключевым словом END. Внутри тела процедуры можно размещать текст запроса, объявлять переменные, использовать конструкции ветвления, циклы и многое другое, как в любом языке программирования.
тело процедуры
Для начала создадим блок начала и конца тела нашей процедуры.
BEGIN END;
Теперь добавим текст запроса, который будет заполнять поля в таблице wp-comments, при добавлении нового комментария (ответа).
BEGIN INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (подставляемые значения); END;
Хранить подставляемые значения будем в переменных. Для создания переменной используется ключевое слово DECLARE затем указывается имя, тип и длина переменной, так же можно указать значение по умолчанию. Если у переменной есть параметр DEFAULT, то такая переменная является инициализированной.
DECLARE имя тип (длина) DEFAULT значение по умолчанию;
Так же можно установить значение, для любой переменной используя, оператор SET.
SET имя переменной = значение;
И так создадим три переменных: Author, Email, UsedID, которые будут хранить значения для колонок: comment_author, comment_author_email, user_id.
BEGIN DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; -- Объявили переменную Email DECLARE Email varchar(100); -- Установили значение переменной Email SET Email = "[email protected]"; END;
comment_content данная колонка хранит текст комментария, который передается в процедуру в виде входного параметра Cоntent. Мы не будем создавать отдельную переменную, а просто подставим в VALUES значение входного параметра.
comment_date и comment_date_gmt обе колонки при первом заполнение будут иметь одинаковые значения. Создадим переменную Date и присвоим ей в качестве значения результат, который будет возвращать встроенная функция NOW. Данная функция возвращает текущую дату и время в формате DATETIME.
DECLARE MyCurDate DATETIME DEFAULT NOW();
DECLARE MyCurDate DATETIME; SET MyCurDate = NOW();
comment_approved Одобрен ли комментарий, 1 (да) иначе 0. Создадим переменную Approved, но перед установкой значения сделаем небольшую проверку.
DECLARE Approved varchar(20); IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF;
comment_parent здесь в качестве значения нужно указать id комментария, на который мы отвечаем. ID передается в процедуру как второй входной параметр. Создадим переменную ParentCom и присвоем ей значение переданного параметра.
DECLARE ParentCom varchar(20); SET ParentCom = ComID ;
Остался последний параметр comment_post_id здесь нужно указать id поста, в котором будет размещён наш комментарий. Объявим переменную с именем PostID.
DECLARE PostID BIGINT(20);
на данный момент тело процедуры должно выглядеть так
BEGIN -- блок объявления переменных DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; DECLARE Email varchar(100); DECLARE Date DATETIME DEFAULT NOW(); DECLARE ParentCom varchar(20); DECLARE Approved varchar(20); DECLARE PostID BIGINT(20); -- Установка значений переменных IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF; SET Email = "[email protected]"; SET ParentCom = ComID ; -- запрос INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (Author, Email, Content, Date, Date, PostID, ParentCom, Approved, UserID); END;
Внимание! Данная работа построена на основе перевода раздела «17.1. Stored Routines and the Grant Tables» описания ПО MySQL 5.0.19, «Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision:995)»
``Сначала прочти все, а потом пробуй примеры"
Хранимые процедуры представляют собой набор команд SQL, которые могут компилироваться и храниться на сервере. Таким образом, вместо того, чтобы хранить часто используемый запрос, клиенты могут ссылаться на соответствующую хранимую процедуру. Это обеспечивает лучшую производительность, поскольку данный запрос должен анализироваться только однажды и уменьшается трафик между сервером и клиентом. Концептуальный уровень можно также повысить за счет создания на сервере библиотеки функций.
Триггер представляет собой хранимую процедуру, которая активизируется при наступлении определенного события. Например, можно задать хранимую процедуру, которая срабатывает каждый раз при удалении записи из транзакционной таблицы - таким образом, обеспечивается автоматическое удаление соответствующего заказчика из таблицы заказчиков, когда все его транзакции удаляются.
Хранимые программы (процедуры и функции) поддерживаются в MySQL 5.0. Хранимые процедуры - набор SQL -выражений, который может быть сохранен на сервере. Как только это сделано, клиенту уже не нужно повторно передавать запрос, а требуется просто вызвать хранимую программу.
Это может быть полезным тогда, когда:
- многочисленные приложения клиента написаны в разных языках или работают на других платформах, но нужно использовать ту же базу данных операций
- безопасность на 1 месте
Хранимые процедуры и функции (подпрограммы) могут обеспечить лучшую производительность потому, что меньше информации требуется для пересылки между клиентом и сервером. Выбор увеличивает нагрузку на сервер БД, но снижает затраты на стороне клиента. Используйте это, если много клиентских машин (таких как Веб-серверы) обслуживаются одной или несколькими БД.
Хранимые подпрограммы также позволяют вам использовать библиотеки функций, хранимые в БД сервера. Эта возможность представлена для многих современных языков программирования, которые позволяют вызывать их непосредственно (например, используя классы).
MySQL следует в синтаксисе за SQL:2003 для хранимых процедур, который уже используется в IBM"s DB2.
От слов к делу…
При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицей mysql.proc
Начиная с MySQL 5.0.3 требуются следующие привилегии:
CREATE ROUTINE для создания хранимых процедур
ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю процедуры (функции)
EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.
Синтаксис хранимых процедур и функций
Хранимая подпрограмма представляет собой процедуру или функцию. Хранимые подпрограммы создаются с помощью выражений CREATE PROCEDURE или CREATE FUNCTION . Хранимая подпрограмма вызывается, используя выражение CALL , причем только возвращающие значение переменные используются в качестве выходных. Функция может быть вызвана подобно любой другой функции и может возвращать скалярную величину. Хранимые подпрограммы могут вызывать другие хранимые подпрограммы.
Начиная с MySQL 5.0.1, загруженная процедура или функция связана с конкретной базой данных. Это имеет несколько смыслов:
- Когда подпрограмма вызывается, то подразумевается, что надо произвести вызов USE db_name (и отменить использование базы, когда подпрограмма завершилась, и база больше не потребуется)
- Вы можете квалифицировать обычные имена с именем базы данных. Это может быть использовано, чтобы ссылаться на подпрограмму, которая - не в текущей базе данных. Например, для выполнения хранимой процедуры p или функции f которые связаны с БД test , вы можете сказать интерпретатору команд так: CALL test.p() или test.f() .
- Когда база данных удалена, все загруженные подпрограммы связанные с ней тоже удаляются. В MySQL 5.0.0, загруженные подпрограммы - глобальные и не связанны с базой данных. Они наследуют по умолчанию базу данных из вызывающего оператора. Если USE db_name выполнено в пределах подпрограммы, оригинальная текущая БД будет восстановлена после выхода из подпрограммы (Например текущая БД db_11 , делаем вызов подпрограммы, использующей db_22 , после выхода из подпрограммы остается текущей db_11)
MySQL поддерживает полностью расширения, которые разрешают юзать обычные SELECT выражения (без использования курсоров или локальных переменных) внутри хранимых процедур. Результирующий набор, возвращенный от запроса, а просто отправляется напрямую клиенту. Множественный SELECT запрос генерирует множество результирующих наборов, поэтому клиент должен использовать библиотеку, поддерживающую множественные результирующие наборы.
CREATE PROCEDURE - создать хранимую процедуру.
CREATE FUNCTION - создать хранимую функцию.
Синтаксис:
CREATE PROCEDURE имя_процедуры ([параметр_процедуры[,...]])[характеристёика...] тело_подпрограммы
CREATE FUNCTION
имя_функции ([параметр_функции[,...]])
RETURNS
тип
[характеристика...] тело_подпрограммы
параметр_процедуры:
[ IN
| OUT
| INOUT
] имя_параметра тип
параметр_функции:
имя_параметра тип
тип:
Любой тип данных MySQL
характеристика:
LANGUAGE SQL
| DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT "string"
тело_подпрограммы:
Правильное SQL выражение.
Рассмотрим все на практике.
Сначала создадим хранимую процедуру следующим запросом:
CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""
BEGIN
select val1+val2 into "t" from `my` LIMIT 0,1;
END;
Применение выражения LIMIT в этом запросе сделано из соображений того, что не любой клиент способен принять многострочный результирующий набор.
После этого вызовем ее:
CALL my_proc(@a);
SELECT @a;
Для отделения внутреннего запроса от внешнего всегда используют разделитель отличный от обычно (для задания используют команду DELIMITER <строка/символ>)
Вот еще один пример с учетом всех требований.
Mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
Весь процесс можно пронаблюдать на рисунке ниже:
Триггеры
Поддержка триггеров появилась в MySQL начиная с версии 5.0.2.
Триггер - поименованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, события связанного с этой таблицей.
Например, нижеприведенный код создает таблицу и INSERT триггер. Триггер суммирует значения, вставляемые в один из столбцов таблицы.
Mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)
Объявим переменную sum и присвоим ей значение 1. После этого при каждой вставке в таблицу account значение этой переменной будет увеличивать согласно вставляемой части.
Замечание . Если значение переменной не инициализировано, то триггер работать не будет!
Синтаксис создания триггера
CREATETRIGGER имя_триггера время_триггера событие_срабатывания_триггера
ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера
Если с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.
время_триггера
Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.
событие_срабатывания_триггера
Здесь все проще. Тут четко обозначается, при каком событии выполняется триггер.
- INSERT: т.е. при операциях вставки или аналогичных ей выражениях (INSERT, LOAD DATA, и REPLACE)
- UPDATE: когда сущность (строка) модифицирована
- DELETE: когда запись удаляется (запросы, содержащие выражения DELETE и/или REPLACE)
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; SELECT COUNT(* ) INTO iVar FROM `data` ;
Системные переменные
SET @iVar = 5 ; SELECT @iVar;
Разница между простыми и системными переменными в том, что системные переменные доступны из вне хранимой процедуры. То есть, чтобы извлечь какие-то данные нужно пользоваться системными, а переменные которые нужны только внутри процедуры должны быть простыми.
Параметры в хранимых процедурах
Здесь тоже всё достаточно просто. Изменяем первую строку, объявляющая саму процедуру:
CREATE PROCEDURE `procedure1` (IN iInput1 INT, IN iInput2 INT)
Здесь, ключевое слово IN указывает на то, что параметр указан только для чтения. Далее с этим параметром работаем как с обычной переменной внутри процедуры:
Условия, Циклы. 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.
Объяснять особо нечего, просто два запроса объединили в один. Теперь мы можем вызвать эту процедуру таким образом:
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;
Что для этого нужно? Установите 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;
Простой пример
Один из хороших случаев применения хранимых процедур - тогда, когда вам нужно объединить несколько запросов в один, например добавление темы в форум и увеличение общего количества тем. Допустим таблица threadsCREATE TABLE `threads` (
`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;