Доброго времени суток, уважаемые читатели. Продолжаем изучать основные элементы языка SQL и в этой статье разберемся с хранимыми процедурами. Напомню, что мы работаем в СУБД MySQL, если вы работаете в другой СУБД, то некоторые синтаксические конструкции могут отличаться, но суть остается такой же.
Общие сведения
Итак, хранимые процедуры в SQL — это аналог функций в других языках программирования. Хранимые процедуры могут выполнять действия над данными автоматически: вывод данных, удаление, изменение — то, что мы делали в прошлых уроках вручную.
Особенностью процедур является то, что есть возможность передавать аргументы, (так же как и функциям в других языка) и выводить различные данные в зависимости от аргумента. Также, процедура является сущностью SQL, которую создают один раз, а затем вызывают, передавая аргументы.
Хранимые процедуры в MySQL
Хранимые процедуры в СУБД MySQL можно создать несколькими способами, мы кратко опишем 2 способа через утилиту phpmyadmin:
- Через консоль SQL запросов
- Через специальную вкладку процедуры
Также как и все запросы SELECT, INSERT и т.д код создания хранимых процедур можно прописывать на вкладке SQL.
Второй способ возможен тогда, когда мы выбрали определенную БД, и конкретно для нее появляется вкладка процедур, как на скриншоте ниже.
Далее в этой вкладке следует нажать кнопку «Добавить процедуру».
Итак, где создавать процедуры описали, а что конкретно прописывать пока непонятно. Как раз к этому и перейдем.
Создание процедур в SQL
Чтобы создать процедуру необходимо воспользоваться оператором CREATE PROCEDURE. После оператора следует указать имя процедуры, а затем в круглых скобках аргументы, если они имеются, вместе с указанием типа данных каждого аргумента. Примерно это будет выглядеть так:
CREATE PROCEDURE name_procedure (arg1 datatype, arg2 datatype,...)
Ну и сразу разберем простой пример, напишем процедуру, которая будет показывать нам список покупателей из города, который мы передадим в качестве аргумента.
Напомню, что мы работаем с уже созданной БД и ее структуру с данными можно посмотреть по этим ссылкам на предыдущие уроки:
Оператор CREATE в SQL
Оператор INSERT в SQL
Итак, весь код создания процедуры приведем сразу:
DELIMITER // CREATE PROCEDURE get_customers (city_arg VARCHAR(45)) BEGIN SELECT cname, city FROM customers WHERE city = city_arg; END // DELIMITER ;
Мы используем 1 способ создания процедуры — через консоль SQL для нашей базы данных. Поясним некоторые моменты кода выше:
- По умолчанию окончанием запроса служит точка с запятой. Оператор DELIMITER задает новый разделитель, то есть новый знак окончания запроса. Это необходимо для того, чтобы запрос создания процедуры заканчивался после ключевого слова END. В противном случае, запрос закончится на точке с запятой, после основного тела процедуры.
Процедура обязательно должна начинаться с BEGIN и заканчиваться END. Внутри процедуры используется уже знакомый запрос SELECT, который сравнивает город со значением аргумента.
Итак, процедура создана и хранится отдельно от таблиц или представлений. Посмотреть все процедуры можно в одноименной вкладке.
Вызов процедур в SQL
Очевидно, для использования процедуры, ее следует вызвать и передать входные аргументы, если они требуются. Вызов хранимой процедуры в SQL производится с помощью оператора CALL. Вызов созданной выше процедуры будет произведен следующим образом:
CALL get_customers("Москва");
Таким образом, в выводе получим список покупателей, проживающих в Москве.
Операторы хранимых процедур
Итак, хранимые процедуры это достаточно мощный инструмент. И в SQL хранимые процедуры также могут использовать различные операторы, вложенные запросы и т.д. То есть, все, что мы изучали в предыдущих статьях по SQL.
Также, существуют еще особенности хранимых процедур. В хранимых процедурах есть возможность использовать условные операторы и циклы, такие как IF-ELSE, CASE и WHILE. Далее, приведем пример использования конструкции CASE.
Создать хранимую процедуру, которая выведет продавцов, оформивших заказы, по диапазонам. Если аргумент имеет значение «Маленькая суммы», то диапазон продаж от 0 до 1000, «Средние суммы» — от 1000 до 1500, «Большие суммы» — свыше 1500.
DELIMITER // CREATE PROCEDURE get_salespeople(str VARCHAR(45)) BEGIN CASE str WHEN "Маленькие суммы" THEN SELECT sname as "Имя продавца", SUM(amt) as "Суммарные продажи" FROM salespeople INNER JOIN orders on salespeople.snum = orders.snum group by sname HAVING SUM(amt) < 1000; WHEN "Средние суммы" THEN SELECT sname as "Имя продавца", SUM(amt) as "Суммарные продажи" FROM salespeople INNER JOIN orders on salespeople.snum = orders.snum group by sname HAVING SUM(amt) >= 1000 and SUM(amt) < 1500; WHEN "Большие суммы" THEN SELECT sname as "Имя продавца", SUM(amt) as "Суммарные продажи" FROM salespeople INNER JOIN orders on salespeople.snum = orders.snum group by sname HAVING SUM(amt) >= 1500; END CASE; END // DELIMITER ;
Вызвать созданную процедуру можно с тремя различными аргументами:
CALL get_salespeople("Маленькие суммы");
CALL get_salespeople("Средние суммы");
CALL get_salespeople("Большие суммы");
Соответственно, в выводе будут разные данные в зависимости от аргумента.
Примеры
1. Создать хранимую процедуру, которая определяет лучшего продавца по суммарному значению продаж за период дат, полученных в аргументах.
DELIMITER // CREATE PROCEDURE best_salespeople (d1 DATE, d2 DATE) BEGIN SELECT sname, SUM(amt) AS "Суммарные продажи" FROM salespeople INNER JOIN orders ON orders.snum = salespeople.snum and odate BETWEEN d1 and d2 GROUP BY sname ORDER BY SUM(amt) DESC LIMIT 1; END // DELIMITER ;
CALL best_salespeople ('2010-01-01', '2019-01-01');
2. Создать хранимую процедуру, которая отобразит информацию о совершенных заказах, стоимость которых выше числа, заданного в аргументе.
DELIMITER // CREATE PROCEDURE info_orders (arg int) BEGIN SELECT onum, amt, odate FROM orders WHERE amt > arg; END // DELIMITER ;
CALL info_orders(100);
Заключение
Итак, мы познакомились с хранимыми процедурами в SQL. Очевидно, что примеров на их использование можно придумать очень много. Конкретно эта статья посвящена созданию и освоению базовых принципов в работе с хранимыми процедурами в SQL программировании.
Читать ваши лекции одно удовольствие. Большое Вам спасибо за Ваши труды.
Очень приятно читать ваши статьи, узнал о них через методические пособия в нашем колледже, к сожалению нигде не было прямой отсылки на ваш сайт. В одной из практических работ содержится задание по хранимым процедурам которое так и не смог понять «Создать хранимую процедуру с помощью запроса INSERT, для того чтобы заполнить таблицу заказ. «, буду признателен если сможете разъяснить порядок действий или добавить описание в свою статью.
Андрей, спасибо. Не встречал в практике создание процедур кроме как CREATE PROCEDURE. А если нужно создать процедуру, которая выполняет INSERT в таблицу, то нужно также как в примере создать процедуру, но только вместо SELECT написать запрос вставки. Также на сайте есть статья по INSERT — https://codetown.ru/sql/operator-insert/