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

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

Что такое хранимая процедура в SQL

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

Чем же полезны хранимые процедуры:

1. Оптимизация работы: Хранимые процедуры хранятся и выполняются непосредственно на стороне базы данных, что снижает вероятность задержек в работе.
2. Упрощение логики: Благодаря поддержке параметров и SQL-конструкций, таких, как циклы и условия, процедуры позволяют организовать сложную обработку данных, сводя работу к вызову одной команды.
3. Повторное использование кода: Один раз созданную процедуру можно многократно вызывать для разных наборов данных, что сокращает дублирование кода и делает приложение более надёжным.
4. Повышение производительности: Процедуры компилируются при создании, поэтому при каждом вызове не требуется повторная компиляция — это ускоряет выполнение.

Чем отличается хранимая процедура от функции

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

• Возвращаемое значение: Процедура может возвращать одно или несколько значений через OUT параметры или ничего не возвращать. Функция всегда возвращает одно значение.
• Использование в запросах: Функцию можно использовать в SELECT, WHERE и других SQL-запросах. Процедуру в запросах использовать нельзя — её вызывают отдельно.
• Изменение данных: Процедура может изменять данные (например, выполнять INSERT, UPDATE, DELETE). Функция обычно не изменяет данные в таблицах.
• Управление транзакциями: В процедуре можно управлять транзакциями (COMMIT, ROLLBACK). Функция этого не поддерживает.
• Основное назначение: Процедура выполняет задачи и операции над данными, а функция вычисляет и возвращает результат.

Общий синтаксис процедур

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

CREATE PROCEDURE procedure_name (parameter1 data_type, parameter2 data_type, ...)
BEGIN
    -- Тело процедуры
END;

Основные компоненты:

1. CREATE PROCEDURE: команда для создания процедуры.
2. procedure_name: имя процедуры, которое будет использоваться в дальнейшем для её запуска.
3. Параметры: в большинстве СУБД можно указать входные и выходные параметры, чтобы передавать данные в процедуру и получать результат. Параметры имеют направление (IN, OUT, INOUT) и тип данных.
4. Тело процедуры: блок, где прописаны SQL-инструкции и логика работы процедуры. Тут могут присутствовать различные функции, операторы, условные конструкции и циклы.

Как создать процедуру в SQL

Права доступа

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

Основные права доступа при создании процедур и работе с ними:

• Права на создание процедуры. Например, в MySQL это право CREATE ROUTINE, в PostgreSQL — CREATE, а в Oracle — CREATE PROCEDURE.
• Права на запуск процедуры (например, EXECUTE) позволяют определённым пользователям или группам выполнять процедуру. Эти права можно назначить с помощью команды GRANT EXECUTE ON procedure_name TO user_name;.
• Права на изменение и удаление процедуры. Пользователю необходимы привилегии на редактирование, такие как ALTER или DROP.
• Права на доступ к данным в теле процедуры. Когда процедура взаимодействует с таблицами и другими объектами базы данных, нужно удостовериться, что у неё есть права на выполнение операций с этими объектами (например, SELECT, INSERT, UPDATE).

Пример создания процедуры в MySQL

В MySQL процедуры создаются с использованием команды CREATE PROCEDURE, за которой следует имя процедуры и список параметров (если они есть). После этого указывается тело процедуры, заключенное в блок BEGIN ... END.

DELIMITER //
CREATE PROCEDURE имя_процедуры (параметры)
BEGIN
  -- SQL команды
  ...
END //
DELIMITER ;

DELIMITER используется для того, чтобы указать MySQL, что в этом контексте символом завершения команды будет //, а не ;, так как внутри процедуры часто используются несколько операторов, разделенных ;.

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

DELIMITER //
CREATE PROCEDURE GetEmployeesByAge (IN min_age INT)
BEGIN
  SELECT * FROM employees WHERE age > min_age;
END //
DELIMITER ;

IN min_age INT — входной параметр (см. ниже), который принимает минимальный возраст для фильтрации данных.

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

Пример создания процедуры в PostgreSQL

В PostgreSQL процедуры создаются с помощью команды CREATE PROCEDURE, но для них требуется указание языка (чаще всего это plpgsql).

CREATE PROCEDURE имя_процедуры (параметры)
LANGUAGE plpgsql
AS $
BEGIN
  -- SQL команды
  ...
END;
$;

В PostgreSQL можно создавать более сложные процедуры, включая работу с переменными и логикой выполнения. Рассмотрим пример процедуры, которая подсчитывает количество заказов для определенного клиента в таблице orders.

CREATE PROCEDURE CountCustomerOrders (IN customer_id INT)
LANGUAGE plpgsql
AS $
DECLARE
  total_orders INT;
BEGIN
  SELECT COUNT(*) INTO total_orders FROM orders WHERE orders.customer_id = customer_id;
  RAISE NOTICE 'Total Orders: %', total_orders;
END;
$;

• DECLARE — блок объявления переменных, где мы объявляем переменную total_orders для хранения результата.
• RAISE NOTICE — вывод сообщения в лог (аналог вывода данных).

Входные и выходные параметры

Это ключевые элементы в хранимых процедурах, которые позволяют передавать данные в процедуру и возвращать из неё результаты.

Входные параметры (IN) позволяют передавать значения в процедуру, чтобы использовать их в логике выполнения.

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

CREATE PROCEDURE add_bonus_points(student_id INT, bonus_points INT)
AS $
BEGIN
    -- Добавляем дополнительные баллы к текущим баллам студента
    UPDATE students
    SET score = score + bonus_points
    WHERE id = student_id;
END;
$;

В этом примере:

• student_id — входной параметр, определяющий, какого студента обновить.
• bonus_points — входной параметр, указывающий, на сколько баллов увеличить текущие.

Выходные параметры (OUT) позволяют процедуре возвращать значения после выполнения. Например, создадим процедуру, которая возвращает имя студента по его ID:

CREATE PROCEDURE get_student_name(student_id INT, OUT student_name VARCHAR(100))
AS $
BEGIN
    -- Извлекаем имя студента и записываем его в выходной параметр student_name
    SELECT name INTO student_name
    FROM students
    WHERE id = student_id;
END;
$;

В этом примере:

• student_id — входной параметр для поиска студента.
• student_name — выходной параметр, куда сохраняется имя студента.

INOUT параметры могут быть как входными, так и выходными, позволяя передавать и изменять данные.

Предположим, у нас есть процедура, которая увеличивает баллы студента на определённую сумму и возвращает его новые баллы. Для этого мы будем использовать INOUT параметр, который и принимает исходные баллы, и возвращает обновленные.

CREATE PROCEDURE update_student_score(INOUT student_id INT, INOUT current_score INT, IN bonus INT)
AS $
BEGIN
    -- Увеличиваем баллы студента на указанную сумму
    UPDATE students
    SET score = current_score + bonus
    WHERE id = student_id;

    -- Обновляем значение current_score и возвращаем его через INOUT параметр
    SELECT score INTO current_score
    FROM students
    WHERE id = student_id;
END;
$;

• student_id — INOUT параметр, который передаёт ID студента в процедуру, а также может быть использован для возвращения изменений (например, после обновления баллов).
• current_score — INOUT параметр, который передаёт текущие баллы студента и обновляется на новые баллы в процессе работы процедуры.
• bonus — IN параметр, который передаёт количество баллов для увеличения.

Запуск процедуры

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

Для запуска процедуры в SQL обычно используется команда CALL:

CALL имя_процедуры(параметр1, параметр2, ...);

Разберем запуск процедуры на предыдущем примере - процедуре update_student_score:

-- Пример вызова процедуры, где передаем ID студента, текущие баллы и бонус
CALL update_student_score(1, 50, 10);  
-- ID студента = 1, текущие баллы = 50, бонус = 10

1. Передача параметров: Вызываем процедуру CALL update_student_score(1, 50, 10);, передавая student_id = 1, current_score = 50, и bonus = 10.
2. Выполнение логики процедуры: Процедура находит студента с ID 1 и обновляет его баллы, добавляя к current_score значение bonus. Баллы становятся 60.
3. Возврат нового значения: В обновленный current_score записывается новое значение 60 и возвращается из процедуры.

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

Удаление хранимой процедуры — это процесс, при котором удаляется сама процедура из базы данных. После удаления она становится недоступной для выполнения.

В PostgreSQL, MySQL и SQL Server для этой цели используется команда DROP PROCEDURE.

DROP PROCEDURE [IF EXISTS] procedure_name([parameter_types]);

• IF EXISTS — опциональный параметр, который позволяет избежать ошибки, если процедура с таким именем не существует.
• procedure_name — имя процедуры, которую нужно удалить.
• parameter_types — необязательный список типов параметров (если процедура перегружена, то для корректного удаления нужно указать параметры).

DROP PROCEDURE IF EXISTS update_student_score;
--Этот запрос удалит процедуру 

Изменение процедур

Изменение хранимых процедур в SQL обычно требует их удаления и создания новых версий, так как большинство СУБД не поддерживает прямое изменение.

• В MySQL используется DROP PROCEDURE, а затем создаётся новая версия процедуры с необходимыми изменениями.
• В PostgreSQL можно использовать команду CREATE OR REPLACE PROCEDURE, чтобы избежать удаления процедуры вручную.
• А в SQL Server для модификации уже существующей процедуры применяется команда ALTER PROCEDURE.

Заключение

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