Хранимые процедуры в SQL

Доброго времени суток, уважаемые читатели. Продолжаем изучать основные элементы языка 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 программировании.