Skip to content

Хранимые процедуры в 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 программировании.

Опубликовано вSQL

Один комментарий

  1. Юрий Юрий

    Читать ваши лекции одно удовольствие. Большое Вам спасибо за Ваши труды.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *