Хранимые процедуры в SQL - это аналог функций в других языках программирования. Хранимые процедуры могут выполнять действия над данными автоматически: вывод данных, удаление, изменение.

Особенностью процедур является то, что есть возможность передавать аргументы (так же как и функциям в других языка), и выводить различные данные в зависимости от аргумента. Также, процедура является сущностью SQL, которую создают один раз, а затем вызывают, передавая аргументы.

Создание процедур в SQL

Чтобы создать процедуру необходимо воспользоваться оператором CREATE PROCEDURE. После оператора следует указать имя процедуры, а затем в круглых скобках аргументы, если они имеются, вместе с указанием типа данных каждого аргумента. Примерно это будет выглядеть так:

CREATE PROCEDURE name_procedure (arg1 datatype, arg2 datatype, ...)

Ну и сразу разберем простой пример, напишем процедуру, которая будет показывать нам список покупателей из города, который мы передадим в качестве аргумента.

Итак, весь код создания процедуры приведем сразу:

DELIMITER //
CREATE PROCEDURE get_customers (city_arg VARCHAR(45))
BEGIN
    SELECT cname, city
    FROM customers
    WHERE city = city_arg;
END //
DELIMITER;

Поясним некоторые моменты кода выше:

  • По умолчанию окончанием запроса служит точка с запятой. Оператор DELIMITER задает новый разделитель, то есть новый знак окончания запроса. Это необходимо для того, чтобы запрос создания процедуры заканчивался после ключевого слова END. В противном случае, запрос закончится на точке с запятой, после основного тела процедуры.
  • Процедура обязательно должна начинаться с BEGIN и заканчиваться END. Внутри процедуры используется уже знакомый запрос SELECT, который сравнивает город со значением аргумента.

Итак, процедура создана и хранится отдельно от таблиц или представлений.

Вызов процедур в SQL

Очевидно, для использования процедуры, ее следует вызвать и передать входные аргументы, если они требуются. Вызов хранимой процедуры в SQL производится с помощью оператора CALL. Вызов созданной выше процедуры будет произведен следующим образом:

CALL get_customers("Москва");

Таким образом, в выводе получим список покупателей, проживающих в Москве.

Операторы хранимых процедур

Также, существуют еще особенности хранимых процедур. В хранимых процедурах есть возможность использовать условные операторы и циклы, такие как 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("Большие суммы");

Соответственно, в выводе будут разные данные в зависимости от аргумента.