Хранимые процедуры — это процедуры, хранящиеся в базе данных MySQL, которые инкапсулируют операции SQL и логику программирования. Они действуют как мини-программы, которые можно вызывать для выполнения повторяющихся или сложных задач.
Они предлагают множество преимуществ, таких как модульность, повторное использование кода, повышенная безопасность и лучшая производительность. Поэтому освоение хранимых процедур имеет важное значение для любого разработчика или администратора MySQL.
Ниже мы рассмотрим, что именно они собой представляют, их преимущества, как создавать и использовать хранимые процедуры в MySQL шаг за шагом, а также несколько практических примеров, которые помогут вам извлечь из них максимальную пользу.
Что такое хранимые процедуры в MySQL?
Хранимые процедуры представляют собой наборы предварительно скомпилированные операторы SQL которые хранятся в базе данных MySQL с именем и входными и выходными параметрами.
Их можно вызывать по имени, передавая значения их параметров. При выполнении они выполняют определенные в них операции, такие как запросы, обновления, логику управления потоком и многое другое.
Они похожи на мини-программы, размещенные в базе данных, которые инкапсулируют сложные операции для:
- Упростите повторяющиеся административные задачи
- Централизуйте бизнес-логику на сервере
- Уменьшение клиент-серверного трафика
- Повышение производительности при предварительной компиляции запроса
При хранении на сервере они обеспечивают такие желаемые функции, как:
- Модульность: Они разбивают логику на управляемые части.
- Повторное использование: Устранение дублирования кода.
- Безопасность: Разрешения на уровне процедур.
- Скрытая логика: Клиент не видит внутренних деталей.
Короче говоря, хранимые процедуры — очень полезный инструмент для разработчиков MySQL. Давайте рассмотрим его основные преимущества более подробно.
Преимущества использования хранимых процедур
Хранимые процедуры обеспечивают важные преимущества:
1. Лучшая организация и поддержка кода
Они позволяют разбить сложные операции на отдельные, легко управляемые части. Код можно повторно использовать из нескольких мест, вызвав хранимую процедуру.
Это приводит к созданию более организованного и удобного для обслуживания кода с меньшим дублированием.
2. Уменьшение трафика между клиентом и сервером
Благодаря хранению на сервере сокращается передача информации, поскольку нет необходимости каждый раз отправлять SQL-запросы. Это повышает производительность.
3. Более быстрые операции и транзакции
MySQL компилирует процедуру только один раз и использует ее повторно. Это ускоряет выполнение, устраняя необходимость в повторной компиляции каждый раз.
Кроме того, их можно использовать для инкапсуляции сложных транзакций, которые будут выполняться быстрее.
4. Повышенная безопасность и разрешения
Разрешения и привилегии можно назначать на уровне хранимой процедуры, а не предоставлять полный доступ к таблицам базы данных.
Это обеспечивает больший контроль над данными и операциями, которые могут выполнять пользователи.
5. Простота использования и поддержки другими разработчиками
Поскольку они находятся в общем доступе в базе данных, любой разработчик, имеющий соответствующие разрешения, может выполнять процедуры, не зная их внутреннего кода.
Это упрощает обслуживание и снижает количество ошибок.
6. Переносимость между различными приложениями
Поскольку его логика содержится в базе данных, его можно вызвать из любого языка программирования, который его поддерживает, включая cоздание функций в MySQL.
Как создать хранимые процедуры в MySQL
Создать хранимую процедуру в MySQL просто, используя синтаксис SQL:
СОЗДАТЬ ПРОЦЕДУРУ имя_процедуры()
НАЧАТЬ
инструкции;
END
Процедура создается с уникальным именем, и между BEGIN – END мы поместим операторы SQL, которые будут выполнены при ее вызове.
Например, эта процедура регистрирует новую продажу:
СОЗДАТЬ ПРОЦЕДУРУ insert_sale(
p_value FLOAT,
p_client VARCHAR(50)
)
НАЧАТЬ
ВСТАВИТЬ В продажи(стоимость, клиент)
ЗНАЧЕНИЯ(p_value, p_client);
END
Эта процедура получает параметры p_value и p_customer, вставляет новую продажу в таблицу продаж и возвращает автоматически сгенерированный идентификатор.
Процедуры также поддерживают более продвинутый код, такой как локальные переменные, курсоры, обработку ошибок с помощью BEGIN-EXCEPTION-END и т. д.
Как использовать хранимые процедуры в MySQL
После создания хранимой процедуры ее можно вызвать в любом SQL-запросе с помощью CALL:
CALL имя_процедуры(значение_параметра1, значение_параметра2);
Например, чтобы вставить продажу:
CALL insert_sale(125.50, ‘Хуан Перес’);
Процедуры также можно вызывать из любого языка программирования, передавая значения параметрам. Например, в PHP:
$result = $mysqli->query(«CALL insert_sale(125.50, ‘Хуан Перес’)»);
И из Явы:
CallableStatement stmt = conn.prepareCall(«{CALL insert_sale(?, ?)}»);
stmt.setDouble(1, 125.50);
stmt.setString(2, «Джон Перес»);
stmt.executeUpdate();
Примеры хранимых процедур в MySQL
Давайте рассмотрим несколько практических примеров, чтобы лучше использовать эту мощную функцию MySQL:
Получите общий объем продаж на одного клиента
СОЗДАТЬ ПРОЦЕДУРУ total_sales_customer(IN p_customer VARCHAR(50))
НАЧАТЬ
ВЫБЕРИТЕ СУММУ(значение) КАК итог
ОТ продаж
ГДЕ клиент = p_client;
END
Используйте:
CALL total_sales_customer(‘Ана Вальдес’);
Список продуктов по заданной цене
СОЗДАТЬ ПРОЦЕДУРУ cheap_products(IN p_price DECIMAL(10,2))
НАЧАТЬ
ВЫБЕРИТЕ id, имя, цену ИЗ продуктов
ГДЕ цена <= p_price;
END
Используйте:
ВЫЗОВ cheap_products(500);
Удалить и повторно вставить записи
СОЗДАТЬ ПРОЦЕДУРУ regenerate_table()
НАЧАТЬ
УДАЛИТЬ ИЗ my_table;
— Вставить новые записи
КОНЕЦ;
Удобно для восстановления тестовых таблиц.
Вызов другой внутренней процедуры
СОЗДАТЬ ПРОЦЕДУРУ my_procedure()
НАЧАТЬ
ВЫЗОВ другой_процедуры();
— дополнительные инструкции
КОНЕЦ;
Позволяет разбирать на части, пригодные для повторного использования.
Перевод средств между счетами
СОЗДАТЬ ПРОЦЕДУРУ передача(
p_cuentaorigen INT,
p_cuentaDestino ИНТ,
p_decimal сумма(10,2)
)
НАЧАТЬ
НАЧАТЬ СДЕЛКУ;
ОБНОВЛЕНИЕ счетов
SET баланс = баланс – p_amount
ГДЕ id = p_cuentaOrigen;
ОБНОВЛЕНИЕ счетов
SET баланс = баланс + p_amount
ГДЕ id = p_cuentaDestino;
СОВЕРШИТЬ;
КОНЕЦ;
Инкапсулирует логику передачи в транзакцию.
Выводы по хранимым процедурам
Хранимые процедуры — очень мощная функция MySQL, которую должен освоить каждый разработчик.
Они позволяют модулировать сложные операции, повторно использовать код, сокращать трафик между клиентом и сервером, повышать производительность и безопасность.
Помимо хранимых процедур, MySQL предлагает множество функций, которые также необходимы любому разработчику. Эти функции позволяют выполнять конкретные и сложные операции эффективным и модульным образом. Некоторые из наиболее часто используемых функций в MySQL включают `CONCAT` для объединения строк, `SUBSTRING` для извлечения подстрок и математические функции, такие как `ROUND` и `CEIL` для округления. и вы также можете