ГлавнаяУроки SQL → Урок 18. Хранимые процедуры. Циклы.

SQL - Урок 18. Хранимые процедуры. Часть 4.

Сегодня узнаем, как работать с циклами, т.е. выполнять один и тот же запрос несколько раз. В MySQL для работы с циклами применяются операторы WHILE, REPEAT и LOOP.

Оператор цикла WHILE

Сначала синтаксис:
WHILE условие DO запрос END WHILE


Запрос будет выполняться до тех пор, пока условие истинно. Давайте посмотрим на примере, как это работает. Предположим, мы хотим знать названия, авторов и количество книг, которые поступили в различные поставки. Интересующая нас информация хранится в двух таблицах - Журнал Поставок (magazine_incoming) и Товар (products). Давайте напишим интересующий нас запрос:
SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product;


А что, если нам необходимо, чтобы результат выводился не в одной таблице, а по каждой поставке отдельно? Конечно, можно написать 3 разных запроса, добавив в каждый еще одно условие:
SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=1; SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=2; SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=3;
Но гораздо короче сделать это можно с помощью цикла WHILE:
DECLARE i INT DEFAULT 3; WHILE i>0 DO SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=i; SET i=i-1; END WHILE;
Т.е. мы ввели переменную i, по умолчанию равную 3, сервер выполнит запрос с id поставки равным 3, затем уменьшит i на единицу (SET i=i-1), убедится, что новое значение переменной i положительно (i>0) и снова выполнит запрос, но уже с новым значением id поставки равным 2. Так будет происходить, пока переменная i не получит значение 0, условие станет ложным, и цикл закончит свою работу.

Чтобы убедиться в работоспособности цикла создадим хранимую процедуру books и поместим в нее цикл:
DELIMITER // CREATE PROCEDURE books () begin DECLARE i INT DEFAULT 3; WHILE i>0 DO SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=i; SET i=i-1; END WHILE; end //


Теперь вызовем процедуру:
CALL books ()//


Теперь у нас 3 отдельные таблицы (по каждой поставке). Согласитесь, что код с циклом гораздо короче трех отдельных запросов. Но в нашей процедуре есть одно неудобство, мы объявили количество выводимых таблиц значением по умолчанию (DEFAULT 3), и нам придется с каждой новой поставкой менять это значение, а значит код процедуры. Гораздо удобнее сделать это число входным параметром. Давайте перепишем нашу процедуру, добавив входной параметр num, и, учитывая, что он не должен быть равен 0:
CREATE PROCEDURE books (IN num INT) begin DECLARE i INT DEFAULT 0; IF (num>0) THEN WHILE i < num DO SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=i; SET i=i+1; END WHILE; ELSE SELECT 'Задайте правильный параметр'; END IF; end // CALL books (0)//


Убедитесь, что с другими параметрами, мы по-прежнему получаем таблицы по каждой поставке. У нашего цикла есть еще один недостаток - если случайно задать слишком большое входное значение, то мы получим псевдобесконечный цикл, который загрузит сервер бесполезной работой. Такие ситуации предотвращаются с помощью снабжения цикла меткой и использования оператора LEAVE, обозначающего досрочный выход из цикла.
CREATE PROCEDURE books (IN num INT) begin DECLARE i INT DEFAULT 0; IF (num>0) THEN wet : WHILE i < num DO IF (i>10) THEN LEAVE wet; ENF IF; SELECT magazine_incoming.id_incoming, products.name, products.author, magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=i; SET i=i+1; END WHILE wet; ELSE SELECT 'Задайте правильный параметр'; END IF; end //
Итак, мы снабдили наш цикл меткой wet вначале (wet:) и в конце, а также добавили еще одно условие - если входной параметр больше 10 (число 10 взято произвольно), то цикл с меткой wet следует закончить (IF (i>10) THEN LEAVE wet). Таким образом, если мы случайно вызовем процедуру с большим значением num, наш цикл прервется после 10 итераций (итерация - один проход цикла).

Циклы в MySQL, так же как и операторы ветвления, на практике в web-приложениях почти не используются. Поэтому для двух других видов циклов приведем лишь синтаксис и отличия. Вряд ли вам доведется их использовать, но знать об их существовании все-таки надо.

Оператор цикла REPEAT

Условие цикла проверяется не в начале, как в цикле WHILE, а в конце, т.е. хотя бы один раз, но цикл выполняется. Сам же цикл выполняется, пока условие ложно. Синтаксис следующий:
REPEAT запрос UNTIL условие END REPEAT


Оператор цикла LOOP

Этот цикл вообще не имеет условий, поэтому обязательно должен иметь оператор LEAVE. Синтаксис следующий:
LOOP запрос END LOOP


На этом мы заканчиваем уроки посвященные SQL. Конечно, мы рассмотрели не все возможности этого языка запросов, но в реальной жизни вам вряд ли придется столкнуться даже с тем, что вы уже знаете.

Напомню, на реальных сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java...) извлекает эти данные из формы и заносит их в БД. При необходимости происходит обратный процесс, т.е. данные извлекаются из БД и выводятся на страницы сайта. Оба процесса происходят посредством SQL-запросов. HTML вы знаете, с базами данных разобрались, SQL-запросы писать научились, осталось изучить PHP, чтобы ваши сайты превратились в полноправные web-приложения. Это и есть ваш следующий шаг. До встречи в уроках PHP.



Предыдущий урок Вернуться в раздел 
Если этот сайт оказался вам полезен, вы можете помочь в его развитии, поставив одну из этих ссылок на свой сайт.
Уроки PHP Уроки PHP Уроки PHP Уроки PHP Уроки PHP Уроки PHP
Код кнопки:




Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.