Что такое хранимые процедуры в SQL, их создание, вызов и удаление

Как и в других языках программирования, в SQL есть свой способ упаковки часто повторяющегося кода для повторного использования — это хранимые процедуры. Они позволяют группировать несколько инструкций SQL в единый блок, который затем можно выполнить с помощью одной команды. В этой статье разберем, как создать и удалить и хранимые процедуры, а также рассмотрим их преимущества.

Обратите внимание, что примеры в этой статье будут продемонстрированы с использованием синтаксиса СУБД MySQL.

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

Хранимые процедуры в SQL — это предварительно скомпилированные и сохраненные наборы команд SQL, предназначенные для выполнения определенных задач. Процедуры хранятся в базе данных и могут вызываться другими операторами SQL, приложениями или пользователями с соответствующими разрешениями. Их можно использовать для инкапсуляции сложных операций базы данных, что упрощает управление базой данных и ее обслуживание. Кроме того, хранимые процедуры можно компилировать и кэшировать, что может повысить производительность за счет сокращения времени, необходимого для разбора и выполнения операторов SQL.

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

Рассмотрим, как создать и вызвать хранимую процедуру с параметрами и без них.

Создание хранимых процедур

На простом примере рассмотрим создание хранимой процедуры. Допустим, у нас есть таблица со списком сотрудников компании (employees). В этой таблице содержатся имена (first_name), фамилии (last_name) сотрудников, их рейтинг (rating) и суммы зарплат (salary):

id	first_name	last_name	rating	salary
1	Andy	Brown	3	100000
2	Sarah	Murray	1	70000
3	Derek	Wilson	4	125000
4	Martha	Andreson	1	90000
5	William	Podolsky	4	130000

Мы хотим получать среднее значение заработков, используя хранимую процедуру. Сперва напишем SQL-запрос для извлечения нужного результата:

SELECT ROUND(AVG(salary)) AS average_salary FROM employees;

Результат выполнения команды выше:

average_salary
103000

Чтобы каждый раз не писать полностью запрос на вывод средней зарплаты, создадим хранимую процедуру, вызывая которую получим идентичный результат:

DELIMITER //
CREATE PROCEDURE calculate_average_salary ()
BEGIN
    SELECT ROUND(AVG(salary)) AS average_salary
    FROM employees;
END //
DELIMITER ;

Запрос начинается с определения нового разделителя для SQL-запроса. Поясним, для чего это нужно.

В SQL точка с запятой (;) используется в качестве разделителя команд по умолчанию. Так как тело процедуры (между BEGIN и END) содержит команды, заканчивающиеся точкой с запятой, то SQL будет интерпретировать завершение инструкции CREATE PROCEDURE до первого символа ;, в нашем случае после строки FROM employees.

Чтобы избежать этой проблемы, перед созданием хранимой процедуры необходимо определить новый разделитель. В нашем примере мы используем // в качестве разделителя вместо точки с запятой. В таком случае инструкция CREATE PROCEDURE завершится после ключевого слова END. После этого мы возвращаем точку с запятой как разделитель по умолчанию с помощью DELIMITER ;.

Создание самой хранимой процедуры происходит с помощью команды CREATE PROCEDURE, за которой следует имя процедуры (calculate_average_salary). Круглые скобки после имени используются для указания любых входных параметров, которые могут потребоваться хранимой процедуре. Ключевое слово BEGIN применяется, чтобы указать на начало блока кода хранимой процедуры. Внутри блока кода вы можете задействовать любые допустимые команды SQL, которые будут выполняться при вызове процедуры. Наконец, ключевое слово END используется для указания конца блока кода.

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

Вызов хранимых процедур

После создания процедуры ее можно выполнить, вызвав ее по имени с помощью оператора CALL:

CALL calculate_average_salary();

После оператора CALL мы прописываем имя процедуры. В скобках можно добавлять нужные параметры. В нашем примере мы не использовали никаких параметров, поэтому в скобки ничего не добавляем. При этом сами скобки можно опустить:

CALL calculate_average_salary;

В итоге мы получим среднюю зарплату сотрудников с помощью готовой процедуры:

average_salary
103000

Далее рассмотрим пример с параметрами.

Хранимые процедуры с параметрами

Создавать процедуры возможно и с параметрами. Добавим в нашем примере возможность передавать в процедуру параметр, определяющий минимальный уровень зарплаты, от которого нужно вычислять средний заработок сотрудников:

DELIMITER //
CREATE PROCEDURE calculate_average_salary (salary_param INT)
BEGIN
    SELECT ROUND(AVG(salary)) AS average_salary
    FROM employees
    WHERE salary >= salary_param;
END //
DELIMITER ;

После имени процедуры в скобках мы добавили параметр salary_param типа INT. В теле процедуры мы используем этот параметр, применяя условие выборки (WHERE salary >= salary_param). В новом примере мы высчитываем средний заработок сотрудников, у которых зарплата выше либо равна передаваемому параметру salary_param.

Создадим эту процедуру и проверим ее работу, передав в качестве параметра значение 100000:

CALL calculate_average_salary(100000);

В расчет среднего значения будут включены только заработки от 100000:

calculate_average_salary
118333

На текущий момент мы разобрали, как создавать и вызывать хранимые процедуры. Далее рассмотрим, как их можно удалять.

Удаление хранимых процедур

Для удаления процедур используется инструкция DROP PROCEDURE, после которой идет имя удаляемой процедуры:

DROP PROCEDURE calculate_average_salary;

Однако, если процедуры, которую удаляют, не существует, будет выведена ошибка. Поэтому рекомендуется вышеуказанной команде добавлять оператор IF EXISTS:

DROP PROCEDURE IF EXISTS calculate_average_salary;

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

Что касается изменения хранимых процедур, то MySQL не позволяет изменять тело процедуры, а только некоторые настройки. Поэтому рекомендация в этом случае — удалить процедуру и создать новую.

Условные операторы и циклы в хранимых процедурах

Хранимые процедуры SQL поддерживают условные операторы и циклы, которые позволяют вам управлять потоком выполнения внутри процедуры. Условные операторы позволяют выполнять определенные блоки кода в зависимости от того, является ли условие истинным или ложным. В процедурах вы можете использовать операторы IF, CASE и WHILE для реализации условной логики.

Приведем пример с оператором IF:

DELIMITER //
CREATE PROCEDURE calculate_salary_expense (threshold INT)
BEGIN
    DECLARE total_salary INT;
    SELECT SUM(salary) INTO total_salary FROM employees;
    IF total_salary > threshold THEN
      SELECT CONCAT('Общие расходы на оплату труда $', total_salary, ' превышают порог $', threshold) AS message;
    ELSE
      SELECT CONCAT('Общие расходы на оплату труда $', total_salary, ' находятся в пределах порога $', threshold) AS message;
    END IF;
END //
DELIMITER ;

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

CALL calculate_salary_expense(500000);

Результат:

message
Общие расходы на оплату труда $515000 превышают порог $500000

Напишем еще один пример для обновления зарплат сотрудников исходя из их рейтинга. Будем использовать цикл WHILE:

DELIMITER //
CREATE PROCEDURE update_salaries()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE current_id INT;
    DECLARE current_rating INT;
    DECLARE current_salary INT;
    DECLARE cur CURSOR FOR SELECT id, rating, salary FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
    OPEN cur;
  
    WHILE done = 0 DO
      FETCH cur INTO current_id, current_rating, current_salary;
      IF done = 0 THEN
        IF current_rating = 1 THEN
          UPDATE employees SET salary = current_salary * 1.05 WHERE id = current_id;
        ELSEIF current_rating = 2 THEN
          UPDATE employees SET salary = current_salary * 1.10 WHERE id = current_id;
        ELSEIF current_rating = 3 THEN
          UPDATE employees SET salary = current_salary * 1.15 WHERE id = current_id;
        ELSEIF current_rating = 4 THEN
          UPDATE employees SET salary = current_salary * 1.20 WHERE id = current_id;
        ELSE
          UPDATE employees SET salary = current_salary * 1.25 WHERE id = current_id;
        END IF;
      END IF;
    END WHILE;
  
    CLOSE cur;
  
    SELECT 'Зарплаты были успешно обновлены' AS message;
END //
DELIMITER ;

Запустим процедуру:

CALL update_salaries();

После получения сообщения об успешном обновлении зарплат можно проверить новые значения с помощью обычной выборки SELECT:

SELECT * from employees;

id	first_name	last_name	rating	salary
1	Andy	Brown	3	115000
2	Sarah	Murray	1	73500
3	Derek	Wilson	4	150000
4	Martha	Anderson	1	94500
5	William	Podolsky	4	156000

В этом примере хранимая процедура использует курсор для перебора столбцов идентификатора, рейтинга и зарплаты в таблице сотрудников и обновляет заработки на основе оценок производительности сотрудников с помощью цикла WHILE. Зарплата сотрудников с определенным рейтингом увеличивается на заданный процент. Чем выше рейтинг, тем выше процент.

Далее поговорим о преимуществах процедур.

Преимущества хранимых процедур

Хранимые процедуры в SQL обладают рядом преимуществ, которые делают их ценным инструментом управления базами данных. Некоторые из этих преимуществ включают в себя следующее:

• Улучшенная производительность: хранимые процедуры предварительно компилируются и сохраняются в базе данных. Это означает, что они могут выполняться намного быстрее, чем запрос, который необходимо компилировать каждый раз при его выполнении. Это может привести к значительному повышению производительности, особенно в базах данных с большими объемами данных.
• Повышенная безопасность: позволяя пользователям выполнять хранимую процедуру, а не напрямую обращаться к базе данных, можно контролировать, кто имеет доступ к данным и какие действия они могут выполнять. Это помогает защитить конфиденциальные данные и предотвратить несанкционированный доступ.
• Повторное использование кода. Сохраняя команды SQL в хранимой процедуре, разработчики могут повторно использовать один и тот же код несколько раз и вносить изменения кода в одном месте, а не обновлять каждый запрос, использующий одни и те же операторы SQL. Это способствует уменьшению дублирование кода и упрощает его обслуживание и обновление.
• Уменьшение сетевого трафика: хранимые процедуры могут уменьшить сетевой трафик, отправляя по сети только имя процедуры и входные параметры, а не всю инструкцию SQL. Это может быть особенно полезно в средах с медленными или ненадежными сетевыми соединениями.

Заключение

В заключение отметим, что хранимые процедуры в SQL являются хорошим способом оптимизации кода, повышения производительности и безопасности. Инкапсулируя сложную логику в одну процедуру, мы можем упростить свой код и снизить риск ошибок. Кроме того, хранимые процедуры также можно использовать для управления доступом к конфиденциальным данным, добавляя дополнительный уровень безопасности базе данных.