практическая работа. Практическая работа - для слияния. Практическая работа 1 sql создание базы данных и таблиц Цель
 Скачать 3.41 Mb.
|
SQL - Хранимые процедуры. Часть 3.Цель: научиться работать с хранимыми процедурами Хранимые процедуры это не просто контейнера для групп запросов, как может показаться. Хранимые процедуры могут в своей работе использовать операторы ветвления. Вне хранимых процедур такие операторы использовать нельзя. Начнем изучение с операторов IF...THEN...ELSE. Если вы изучали уроки JavaScript или просто знакомы с каким-нибудь языком программирования, то эта конструкция вам знакома. Напомним, что условный оператор IF позволяет организовать ветвление программы. В случае хранимых процедур этот оператор позволяет выполнять разные запросы, в зависимости от входных параметров. На примере, как всегда, будет понятнее. Но для начала синтаксис: CREATE PROCEDURE имя_процедуры (параметры) begin IF(условие) THEN запрос 1; ELSE запрос 2; END IF; end // Логика работы проста: если условие истинно, то выполняется запрос 1, в противном случае - запрос 2. Предположим, каждый день мы устраиваем в нашем магазине счастливые часы, т.е. делаем скидку 10% на все книги в последний час работы магазина. Чтобы иметь возможность выбирать цену книги, нам необходимо иметь два ее варианта - со скидкой и без. Для этого, нам понадобится создать хранимую процедуру с оператором ветвления. Так как мы имеем всего два варианта цены, то удобнее в качестве входящего параметра иметь булево значение, которое, как вы помните, может принимать либо 0 - ложь, либо 1 - истина. Код процедуры может быть таким: CREATE PROCEDURE discount (dis BOOLEAN) begin IF(dis=1) THEN SELECT id_product, price*0.9 AS price_discount FROM prices; ELSE SELECT id_product, price FROM prices; END IF; end // Т.е. на входе у нас параметр, который может являться, либо 1 (если скидка есть), либо 0 (если скидки нет). В первом случае будет выполнен первый запрос, во втором - второй. Давайте посмотрим, как работает наша процедура в обоих вариантах: call discount(1)//  call discount(0)//  Оператор IF позволяет выбирать и большее количество вариантов запросов, в таком случае используется следующий синтаксис: CREATE PROCEDURE имя_процедуры (параметры) begin IF(условие) THEN запрос 1; ELSEIF(условие) THEN запрос 2; ELSE запрос 3; END IF; end // Причем блоков ELSEIF может быть несколько. Предположим, что мы решили делать скидки нашим покупателям в зависимости от суммы покупки, до 1000 рублей скидки нет, от 1000 до 2000 рублей - скидка 10%, более 2000 рублей - скидка 20%. Входным параметром для такой процедуры должна быть сумма покупки. Поэтому сначала нам надо написать процедуру, которая будет ее подсчитывать. Сделаем это по аналогии с процедурой sum_vendor, созданной в уроке 15, которая подсчитывала сумму товара по идентификатору поставщика. Необходимые нам данные хранятся в двух таблицах Журнал покупок (magazine_sales) и Цены (prices). CREATE PROCEDURE sum_sale(IN i INT) COMMENT 'Возвращает сумму покупки по ее идентификатору.' begin DROP VIEW IF EXISTS sum_sale; CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale, magazine_sales.id_product, magazine_sales.quantity, prices.price, magazine_sales.quantity*prices.price AS summa FROM magazine_sales, prices WHERE magazine_sales.id_product=prices.id_product; SELECT SUM(summa) FROM sum_sale WHERE id_sale=i; end // Здесь перед параметром у нас появилось новое ключевое слово IN. Дело в том, что мы можем, как передавать данные в процедуру, так и передавать данные из процедуры. По умолчанию, т.е. если опустить слово IN, параметры считаются входными (поэтому раньше мы это слово и не использовали). Здесь же мы явно указали, что параметр i является входным. Если же нам понадобится извлечь какие-нибудь данные из хранимой процедуры, то мы будем использовать ключевое слово OUT, но об этом чуть позже. Итак, мы написали процедуру, которая создает представление, выбирая идентификатор покупки, идентификатор товара, его количество, цену и подсчитывает сумму по всем строчкам получившейся таблицы. Затем идет запрос к этому представлению, где по входному параметру идентификатора покупки подсчитывается итоговая сумма этой покупки. Теперь нам надо написать процедуру, которая пересчитает итоговую сумму с учетом предоставляемой скидки. Здесь нам и понадобится оператор ветвления: CREATE PROCEDURE sum_discount(IN sm INT, IN i INT) COMMENT 'Возвращает сумму покупки с учетом скидки.' begin IF((sm>=1000) && (sm<2000)) THEN SELECT SUM(summa)*0.9 FROM sum_sale WHERE id_sale=i; ELSEIF(sm>=2000) THEN SELECT SUM(summa)*0.8 FROM sum_sale WHERE id_sale=i; ELSE SELECT SUM(summa) FROM sum_sale WHERE id_sale=i; END IF; end // Т.е. мы передаем процедуре два входных параметра сумму (sm) и идентификатор покупки (i) и в зависимости от того, какая это сумма, выполняется запрос к представлению sum_sale на подсчет итоговой суммы покупки, умноженной на нужный коэффициент. Осталось только сделать так, чтобы сумма покупки автоматически передавалась в эту процедуру. Для этого процедуру sum_discount хорошо бы вызвать прямо из процедуры sum_sale. Выглядеть это будет примерно вот так: CREATE PROCEDURE sum_sale(IN i INT) COMMENT 'Возвращает сумму покупки по ее идентификатору.' begin DROP VIEW IF EXISTS sum_sale; CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale, magazine_sales.id_product, magazine_sales.quantity, prices.price, magazine_sales.quantity*prices.price AS summa FROM magazine_sales, prices WHERE magazine_sales.id_product=prices.id_product; SELECT SUM(summa) FROM sum_sale WHERE id_sale=i; CALL sum_discount(?, i); end // Вопросительный знак при вызове процедуры sum_discount поставлен, т.к. не понятно, как результат предыдущего запроса (т.е. итоговой суммы) передать в процедуру sum_discount. Кроме того, не понятно, как процедура sum_discount вернет результат своей работы. Вы, наверно, уже догадались, что для решения второго вопроса нам как раз и понадобится параметр с ключевым словом OUT, т.е. параметр, который будет возвращать данные из процедуры. Давайте введем такой параметр ss, и так как сумма может быть и дробным числом, зададим ему тип DOUBLE: CREATE PROCEDURE sum_discount(IN sm INT, IN i INT, OUT ss DOUBLE) COMMENT 'Возвращает сумму покупки с учетом скидки.' begin IF((sm>=1000) && (sm<2000)) THEN SELECT SUM(summa)*0.9 FROM sum_sale WHERE id_sale=i; ELSEIF(sm>=2000) THEN SELECT SUM(summa)*0.8 FROM sum_sale WHERE id_sale=i; ELSE SELECT SUM(summa) FROM sum_sale WHERE id_sale=i; END IF; end // CREATE PROCEDURE sum_sale(IN i INT, OUT ss DOUBLE) COMMENT 'Возвращает сумму покупки по ее идентификатору.' begin DROP VIEW IF EXISTS sum_sale; CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale, magazine_sales.id_product, magazine_sales.quantity, prices.price, magazine_sales.quantity*prices.price AS summa FROM magazine_sales, prices WHERE magazine_sales.id_product=prices.id_product; SELECT SUM(summa) FROM sum_sale WHERE id_sale=i; CALL sum_discount(?, i, ss); end // Итак, в обе процедуры мы ввели выходной параметр ss. Теперь вызов процедуры CALL sum_discount(?, i, ss);означает, что передавая два первых параметра, мы ждем возврата третьего параметра в процедуру sum_sale. Осталось только понять, как внутри самой процедуры sum_discount присвоить этому параметру какое-либо значение. Нам надо, чтобы в этот параметр передавался результат одного из запросов. И, конечно, в MySQL предусмотрен такой вариант, для этого используется ключевое слово INTO: CREATE PROCEDURE sum_discount(IN sm INT, IN i INT, OUT ss DOUBLE) COMMENT 'Возвращает сумму покупки с учетом скидки.' begin IF((sm>=1000) && (sm<2000)) THEN SELECT SUM(summa)*0.9 INTO ss FROM sum_sale WHERE id_sale=i; ELSEIF(sm>=2000) THEN SELECT SUM(summa)*0.8 INTO ss FROM sum_sale WHERE id_sale=i; ELSE SELECT SUM(summa) INTO ss FROM sum_sale WHERE id_sale=i; END IF; end // С помощью ключевого слова INTO, мы указали, что результат запроса надо передать в параметр ss. Теперь давайте разбираться с вопросительным знаком, вернее узнаем, как передать в процедуру sum_discount результат работы предыдущих запросов. Для этого мы познакомимся с таким понятием, как переменная. Переменные позволяют сохранить результат текущего запроса для использования в следующих запросах. Объявление переменной начинается с символа собачки (@), за которой следует имя переменной. Объявляются они при помощи оператора SET. Например, объявим переменную z и зададим ей начальное значение 20. SET @z='20'// Переменная с таким значение теперь есть в нашей БД, можете проверить, сделав соответствующий запрос: SELECT @z//  Переменные действуют только в рамках одного сеанса соединения с сервером MySQL. То есть после разъединения переменная перестанет существовать. Для использования переменных в процедурах используется оператор DECLARE, который имеет следующий синтаксис: DECLARE имя_переменной тип DEFAULT значение_по_умолчанию_если_есть Итак, давайте в нашей процедуре объявим переменную s, в которую будем сохранять значение суммы покупки с помощью ключевого слова INTO: CREATE PROCEDURE sum_sale(IN i INT, OUT ss DOUBLE) COMMENT 'Возвращает сумму покупки по ее идентификатору.' begin DECLARE s INT; DROP VIEW IF EXISTS sum_sale; CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale, magazine_sales.id_product, magazine_sales.quantity, prices.price, magazine_sales.quantity*prices.price AS summa FROM magazine_sales, prices WHERE magazine_sales.id_product=prices.id_product; SELECT SUM(summa) INTO s FROM sum_sale WHERE id_sale=i; CALL sum_discount(s, i, ss); end // Эта переменная и будет первым входным параметром для процедуры sum_discount. Итак, окончательный вариант наших процедур выглядит так: CREATE PROCEDURE sum_discount(IN sm INT, IN i INT, OUT ss DOUBLE) COMMENT 'Возвращает сумму покупки с учетом скидки.' begin IF((sm>=1000) && (sm<2000)) THEN SELECT SUM(summa)*0.9 INTO ss FROM sum_sale WHERE id_sale=i; ELSEIF(sm>=2000) THEN SELECT SUM(summa)*0.8 INTO ss FROM sum_sale WHERE id_sale=i; ELSE SELECT SUM(summa) INTO ss FROM sum_sale WHERE id_sale=i; END IF; end // CREATE PROCEDURE sum_sale(IN i INT, OUT ss DOUBLE) COMMENT 'Возвращает сумму покупки по ее идентификатору.' begin DECLARE s INT; DROP VIEW IF EXISTS sum_sale; CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale, magazine_sales.id_product, magazine_sales.quantity, prices.price, magazine_sales.quantity*prices.price AS summa FROM magazine_sales, prices WHERE magazine_sales.id_product=prices.id_product; SELECT SUM(summa) INTO s FROM sum_sale WHERE id_sale=i; CALL sum_discount(s, i, ss); end // На случай, если вы запутались, давайте посмотрим алгоритм работы нашей процедуры sum_sale: Мы вызываем процедуру sum_sale, указывая в качестве входного параметра идентификатор интересующей нас покупки, например id=1, и указывая, что второй параметр - выходной, переменный, являющийся результатом работы процедуры sum_discount: call sum_sale(1, @sum_discount)// Процедура sum_sale создает представление, в котором собираются данные обо всех покупках, товарах, их количестве, цене и сумме по каждой строчке. Затем выполняется запрос к этому представлению на итоговую сумму по покупке с нужным идентификатором, и результат записывается в переменную s. Теперь вызывается процедура sum_discount, в которой в качестве первого параметра выступает переменная s (сумма покупки), в качестве второго - идентификатор покупки i, а в качестве третьего указывается параметр ss, который выступает, как выходной, т.е. в него вернется результат действия процедуры sum_discount. В процедуре sum_discount проверяется, какому условию соответствует входная сумма, и выполняется соответствующий запрос, результат записывается в выходной параметр ss, который возвращается в процедуру sum_sale. Чтобы увидеть результат работы процедуры sum_sale нужно сделать запрос: select @sum_discount// Давайте убедимся, что наша процедура работает:  Сумма наших обеих покупок меньше 1000 рублей, поэтому скидки нет. Можете самостоятельно ввести покупки с разными суммами и посмотреть, как будет работать наша процедура. Возможно, этот урок показался вам достаточно трудным или запутанным. Не расстраивайтесь. Во-первых, все приходит с опытом, а во-вторых, справедливости ради, надо сказать, что и переменные, и операторы ветвления в MySQL используются крайне редко. Предпочтение отдается языкам типа PHP, Perl и т.д., с помощью которых и организуется ветвление, а в саму БД посылаются простые процедуры. |