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

3 комментария

  1. Юрий Юрий

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

  2. Андрей Андрей

    Очень приятно читать ваши статьи, узнал о них через методические пособия в нашем колледже, к сожалению нигде не было прямой отсылки на ваш сайт. В одной из практических работ содержится задание по хранимым процедурам которое так и не смог понять «Создать хранимую процедуру с помощью запроса INSERT, для того чтобы заполнить таблицу заказ. «, буду признателен если сможете разъяснить порядок действий или добавить описание в свою статью.

    • Nikiqq Nikiqq

      Андрей, спасибо. Не встречал в практике создание процедур кроме как CREATE PROCEDURE. А если нужно создать процедуру, которая выполняет INSERT в таблицу, то нужно также как в примере создать процедуру, но только вместо SELECT написать запрос вставки. Также на сайте есть статья по INSERT — https://codetown.ru/sql/operator-insert/

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

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