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

Хранимые процедуры это не просто контейнера для групп запросов, как может показаться. Хранимые процедуры могут в своей работе использовать операторы ветвления. Вне хранимых процедур такие операторы использовать нельзя.

Начнем изучение с операторов IF. THEN. ELSE . Если вы изучали уроки JavaScript или просто знакомы с каким-нибудь языком программирования, то эта конструкция вам знакома. Напомним, что условный оператор IF позволяет организовать ветвление программы. В случае хранимых процедур этот оператор позволяет выполнять разные запросы, в зависимости от входных параметров. На примере, как всегда, будет понятнее. Но для начала синтаксис:

Логика работы проста: если условие истинно, то выполняется запрос 1, в противном случае - запрос 2.

Предположим, каждый день мы устраиваем в нашем магазине счастливые часы, т.е. делаем скидку 10% на все книги в последний час работы магазина. Чтобы иметь возможность выбирать цену книги, нам необходимо иметь два ее варианта - со скидкой и без. Для этого, нам понадобится создать хранимую процедуру с оператором ветвления. Так как мы имеем всего два варианта цены, то удобнее в качестве входящего параметра иметь булево значение, которое, как вы помните, может принимать либо 0 - ложь, либо 1 - истина. Код процедуры может быть таким:

Т.е. на входе у нас параметр, который может являться, либо 1 (если скидка есть), либо 0 (если скидки нет). В первом случае будет выполнен первый запрос, во втором - второй. Давайте посмотрим, как работает наша процедура в обоих вариантах:

Оператор IF позволяет выбирать и большее количество вариантов запросов, в таком случае используется следующий синтаксис:

Причем блоков ELSEIF может быть несколько. Предположим, что мы решили делать скидки нашим покупателям в зависимости от суммы покупки, до 1000 рублей скидки нет, от 1000 до 2000 рублей - скидка 10%, более 2000 рублей - скидка 20%. Входным параметром для такой процедуры должна быть сумма покупки. Поэтому сначала нам надо написать процедуру, которая будет ее подсчитывать. Сделаем это по аналогии с процедурой sum_vendor, созданной в уроке 15, которая подсчитывала сумму товара по идентификатору поставщика.

Необходимые нам данные хранятся в двух таблицах Журнал покупок (magazine_sales) и Цены (prices).

Здесь перед параметром у нас появилось новое ключевое слово IN . Дело в том, что мы можем, как передавать данные в процедуру, так и передавать данные из процедуры. По умолчанию, т.е. если опустить слово IN , параметры считаются входными (поэтому раньше мы это слово и не использовали). Здесь же мы явно указали, что параметр i является входным. Если же нам понадобится извлечь какие-нибудь данные из хранимой процедуры, то мы будем использовать ключевое слово OUT , но об этом чуть позже.

Итак, мы написали процедуру, которая создает представление, выбирая идентификатор покупки, идентификатор товара, его количество, цену и подсчитывает сумму по всем строчкам получившейся таблицы. Затем идет запрос к этому представлению, где по входному параметру идентификатора покупки подсчитывается итоговая сумма этой покупки.

Теперь нам надо написать процедуру, которая пересчитает итоговую сумму с учетом предоставляемой скидки. Здесь нам и понадобится оператор ветвления:

Т.е. мы передаем процедуре два входных параметра сумму (sm) и идентификатор покупки (i) и в зависимости от того, какая это сумма, выполняется запрос к представлению sum_sale на подсчет итоговой суммы покупки, умноженной на нужный коэффициент.

Осталось только сделать так, чтобы сумма покупки автоматически передавалась в эту процедуру. Для этого процедуру sum_discount хорошо бы вызвать прямо из процедуры sum_sale. Выглядеть это будет примерно вот так:

Вопросительный знак при вызове процедуры sum_discount поставлен, т.к. не понятно, как результат предыдущего запроса (т.е. итоговой суммы) передать в процедуру sum_discount. Кроме того, не понятно, как процедура sum_discount вернет результат своей работы. Вы, наверно, уже догадались, что для решения второго вопроса нам как раз и понадобится параметр с ключевым словом OUT , т.е. параметр, который будет возвращать данные из процедуры. Давайте введем такой параметр ss, и так как сумма может быть и дробным числом, зададим ему тип DOUBLE:

Итак, в обе процедуры мы ввели выходной параметр ss. Теперь вызов процедуры CALL sum_discount(?, i, ss); означает, что передавая два первых параметра, мы ждем возврата третьего параметра в процедуру sum_sale. Осталось только понять, как внутри самой процедуры sum_discount присвоить этому параметру какое-либо значение. Нам надо, чтобы в этот параметр передавался результат одного из запросов. И, конечно, в MySQL предусмотрен такой вариант, для этого используется ключевое слово INTO :

С помощью ключевого слова INTO , мы указали, что результат запроса надо передать в параметр ss.

Теперь давайте разбираться с вопросительным знаком, вернее узнаем, как передать в процедуру sum_discount результат работы предыдущих запросов. Для этого мы познакомимся с таким понятием, как переменная .

Переменные позволяют сохранить результат текущего запроса для использования в следующих запросах. Объявление переменной начинается с символа собачки (@), за которой следует имя переменной. Объявляются они при помощи оператора SET . Например, объявим переменную z и зададим ей начальное значение 20.

Переменная с таким значение теперь есть в нашей БД, можете проверить, сделав соответствующий запрос:

Переменные действуют только в рамках одного сеанса соединения с сервером MySQL. То есть после разъединения переменная перестанет существовать.

Для использования переменных в процедурах используется оператор DECLARE , который имеет следующий синтаксис:

Итак, давайте в нашей процедуре объявим переменную s, в которую будем сохранять значение суммы покупки с помощью ключевого слова INTO:

Эта переменная и будет первым входным параметром для процедуры sum_discount. Итак, окончательный вариант наших процедур выглядит так:

Мы вызываем процедуру sum_sale, указывая в качестве входного параметра идентификатор интересующей нас покупки, например и указывая, что второй параметр - выходной, переменный, являющийся результатом работы процедуры sum_discount:

Сумма наших обеих покупок меньше 1000 рублей, поэтому скидки нет. Можете самостоятельно ввести покупки с разными суммами и посмотреть, как будет работать наша процедура.

Возможно, этот урок показался вам достаточно трудным или запутанным. Не расстраивайтесь. Во-первых, все приходит с опытом, а во-вторых, справедливости ради, надо сказать, что и переменные, и операторы ветвления в MySQL используются крайне редко. Предпочтение отдается языкам типа PHP, Perl и т.д., с помощью которых и организуется ветвление, а в саму БД посылаются простые процедуры.

Научись программировать на Python прямо сейчас!

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.