Загрузка...
Обновлено: 05.12.2024

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

Алексанян Андрон CEO IT Resume

Хранимые процедуры в 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 и возвращается из процедуры.

Изучаете SQL?

Практики много не бывает - попробуйте решить эту задачу!
Решать бесплатно!

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

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

В 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, вы можете использовать их для улучшения своей работы с базами данных и оптимизации сложных операций!