Введение
Обычно при работе с реляционной базой данных вы используете операторы SQL для извлечения или обработки данных, такие как SELECT, INSERT, UPDATE или DELETE, непосредственно в коде приложения. Эти операторы работают непосредственно с таблицами базы данных и манипулируют ими. Если одни и те же операторы или группы операторов используются в нескольких приложениях, обращающихся к одной и той же базе данных, они часто повторяются в разных приложениях.
MySQL, как и многие другие системы управления реляционными базами данных, поддерживает использование хранимых процедур. Хранимые процедуры помогают группировать один или несколько SQL-операторов для повторного использования под общим именем и инкапсулировать общую бизнес-логику в самой базе данных. Такую процедуру можно вызвать из приложения, обращающегося к базе данных, для извлечения или обработки данных согласованным образом.
Используя хранимые процедуры, можно создавать повторно используемые процедуры для выполнения общих задач, которые можно применять в нескольких приложениях, проверять данные или обеспечивать дополнительный уровень безопасности доступа к данным, ограничивая пользователей базы данных от прямого доступа к базовым таблицам и выполнения произвольных запросов.
В этом уроке вы узнаете, что такое хранимые процедуры и как создавать простые хранимые процедуры, которые возвращают данные и используют входные и выходные параметры.
Предпосылки
Для работы с этим руководством вам понадобится компьютер с установленной системой управления реляционными базами данных (СУРБД) на базе SQL. Инструкции и примеры в этом руководстве были проверены в следующей среде:
- Сервер под управлением Ubuntu 20.04 с пользователем без прав root, обладающим правами администратора, и брандмауэром, настроенным с помощью UFW.
- MySQL установлен и защищен на сервере.
- Базовое введение в выполнение запросов SELECT для извлечения данных из базы данных
Примечание: Обратите внимание, что многие СУБД используют собственную уникальную реализацию SQL, а синтаксис хранимых процедур не входит в официальный стандарт SQL. Хотя команды, упомянутые в этом руководстве, могут работать и в других СУБД, хранимые процедуры специфичны для конкретной базы данных, поэтому точный синтаксис или выходные данные могут отличаться при тестировании в системе, отличной от MySQL.
Вам также понадобится пустая база данных, в которой можно создавать таблицы с помощью хранимых процедур. Рекомендуем вам ознакомиться с разделом «Подключение к MySQL и настройка образца базы данных» ниже, чтобы узнать подробности о подключении к серверу MySQL и создании тестовой базы данных, используемой в примерах этого руководства.
Подключение к MySQL и настройка образца базы данных
В этом разделе вы подключитесь к серверу MySQL и создадите образец базы данных, чтобы вы могли использовать примеры из этого руководства.
В этом руководстве вы будете использовать вымышленную коллекцию автомобилей. Вы будете хранить информацию об автомобилях, которыми владеете в настоящее время, включая их марку, модель, год выпуска и стоимость.
Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локальной машины:
ssh sammy@your_server_ip
Затем откройте командную строку сервера MySQL и замените Sami на свое имя пользователя MySQL:
mysql -u sammy -p
Создайте базу данных с именем «процедуры»:
CREATE DATABASE procedures;
Если база данных была создана успешно, вы получите следующий вывод:
Output
Query OK, 1 row affected (0.01 sec)Чтобы выбрать базу данных процедур, выполните следующий оператор USE:
USE procedures;
Вы получите следующий результат:
Output
Database changedВыбрав базу данных, вы можете создать в ней таблицы экземпляров. Таблица «Машины» будет содержать упрощённые данные о машинах в базе данных. Она содержит следующие столбцы:
- марка: в этом столбце представлена каждая принадлежащая ему машина, выраженная с использованием типа данных varchar с максимальной длиной 100 символов.
- Модель: этот столбец содержит название модели автомобиля, выраженное с использованием типа данных varchar с максимальной длиной 100 символов.
- год: в этом столбце хранится год выпуска автомобиля с типом данных int для хранения числовых значений.
- Значение: в этом столбце хранится стоимость автомобиля с использованием десятичного типа данных с максимум 10 цифрами и 2 цифрами после десятичной запятой.
Создайте образец таблицы с помощью следующей команды:
CREATE TABLE cars (
make varchar(100),
model varchar(100),
year int,
value decimal(10, 2)
);Если напечатан следующий вывод, таблица создана:
Output
Query OK, 0 rows affected (0.00 sec)Затем загрузите таблицу Cars образцами данных, выполнив следующую операцию INSERT INTO:
INSERT INTO cars
VALUES
('Porsche', '911 GT3', 2020, 169700),
('Porsche', 'Cayman GT4', 2018, 118000),
('Porsche', 'Panamera', 2022, 113200),
('Porsche', 'Macan', 2019, 27400),
('Porsche', '718 Boxster', 2017, 48880),
('Ferrari', '488 GTB', 2015, 254750),
('Ferrari', 'F8 Tributo', 2019, 375000),
('Ferrari', 'SF90 Stradale', 2020, 627000),
('Ferrari', '812 Superfast', 2017, 335300),
('Ferrari', 'GTC4Lusso', 2016, 268000);Операция INSERT INTO добавляет в таблицу десять образцов спортивных автомобилей: пять моделей Porsche и пять моделей Ferrari. Следующий вывод показывает, что все пять строк были добавлены:
Output
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0Теперь вы готовы приступить к изучению оставшейся части руководства и начать использовать хранимые процедуры в SQL.
Введение в хранимые процедуры
Хранимые процедуры в MySQL и многих других реляционных системах баз данных называются объектами, содержащими одну или несколько инструкций, которые выполняются последовательно при вызове базой данных. В самом простом примере хранимая процедура может хранить общее выражение в рамках многократно используемой процедуры, например, для извлечения данных из базы данных с часто используемыми фильтрами. Например, можно создать хранимую процедуру для извлечения клиентов интернет-магазина, сделавших заказы за последние несколько месяцев. В наиболее сложных сценариях хранимые процедуры могут представлять собой обширные программы, описывающие сложную бизнес-логику для мощных приложений.
Набор инструкций в хранимой процедуре может включать стандартные операторы SQL, такие как запросы SELECT или INSERT, которые возвращают или обрабатывают данные. Кроме того, хранимые процедуры могут использовать следующее:
- Параметры передаются в хранимую процедуру или возвращаются из нее.
- Переменные, объявленные для обработки полученных данных непосредственно в коде процедуры.
- Условные операторы, которые позволяют выполнять разделы кода хранимой процедуры в зависимости от определенных условий, например операторы IF или CASE.
- Такие циклы, как WHILE, LOOP и REPEAT, позволяют выполнять фрагменты кода несколько раз, например, для каждой строки в извлеченном наборе данных.
- Инструкции по обработке ошибок, такие как возврат сообщений об ошибках пользователям базы данных, имеющим доступ к процедуре.
- Вызов других хранимых процедур в базе данных.
Примечание: Обширный синтаксис, поддерживаемый MySQL, позволяет писать мощные программы и решать сложные задачи с помощью хранимых процедур. В данном руководстве рассматриваются только базовые аспекты использования хранимых процедур с SQL-операторами, заключёнными в тело хранимой процедуры, входные и выходные параметры. Условное выполнение кода, использование переменных, циклов и настраиваемая обработка ошибок выходят за рамки данного руководства. Рекомендуем вам ознакомиться с более подробной информацией о хранимых процедурах в официальной документации MySQL.
Когда процедура вызывается по имени, ядро базы данных выполняет ее так, как определено, инструкция за инструкцией.
Пользователь базы данных должен иметь соответствующие разрешения для выполнения данной процедуры. Это требование обеспечивает дополнительный уровень безопасности, запрещая прямой доступ к базе данных, но при этом предоставляя пользователям доступ к отдельным процедурам, выполнение которых безопасно.
Хранимые процедуры запускаются непосредственно на сервере базы данных, выполняя все вычисления локально и возвращая результаты вызывающему пользователю только после завершения.
Если вы хотите изменить поведение процедуры, вы можете обновить её в базе данных, и приложения, использующие её, автоматически получат новую версию. Все пользователи сразу начнут использовать новый код процедуры без необходимости вносить изменения в свои приложения.
Вот общая структура кода SQL, используемого для создания хранимой процедуры:
DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
instruction_1;
instruction_2;
. . .
instruction_n;
END //
DELIMITER ;Первая и последняя инструкции в этом фрагменте кода — DELIMITER // и DELIMITER ;. MySQL обычно использует символ точки с запятой (;) для разделения операторов и указания их начала и конца. Если вы запускаете несколько команд в консоли MySQL, разделенных точкой с запятой, они рассматриваются как отдельные команды и выполняются независимо, одна за другой. Однако хранимая процедура может содержать несколько команд, которые выполняются последовательно при вызове. Это вызывает проблему, когда вы пытаетесь указать MySQL на необходимость создания новой процедуры. Движок базы данных обнаруживает символ точки с запятой в теле хранимой процедуры и считает, что следует прекратить выполнение команды. В этой ситуации рассматриваемая команда представляет собой весь код создания процедуры, а не отдельную инструкцию в самой процедуре, поэтому MySQL неверно истолковывает ваше намерение.
Чтобы обойти это ограничение, используйте оператор DELIMITER, чтобы временно изменить разделитель с «;» на «/» на время вызова CREATE PROCEDURE. После этого все точки с запятой в теле хранимой процедуры будут отправлены на сервер без изменений. После завершения всей процедуры разделитель изменится на «;», при этом последний DELIMITER будет «;».
Основой кода создания новой процедуры является вызов CREATE PROCEDURE, за которым следует имя процедуры: в примере — имя_процедуры. За именем процедуры следует необязательный список параметров, которые она принимает. Последняя часть — это тело процедуры, заключённое в операторы BEGIN и END. Внутри кода процедуры находится то, что может содержать оператор SQL, например запрос SELECT, или более сложный код.
Команда END заканчивается //, временным разделителем, вместо обычной точки с запятой.
В следующем разделе вы создадите базовую хранимую процедуру без параметров, которая инкапсулирует запрос.
Создание хранимой процедуры без параметров
В этом разделе вы создадите свою первую хранимую процедуру, содержащую оператор SQL SELECT для возврата списка автомобилей, принадлежащих заказу, по марке и их стоимости в порядке убывания.
Начните с выполнения оператора SELECT, который вы хотите использовать:
SELECT * FROM cars ORDER BY make, value DESC;
База данных возвращает список автомобилей из таблицы «Cars», сначала по марке, а затем внутри марки по стоимости в порядке убывания:
Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)Самый дорогой Ferrari находится наверху списка, а наименее дорогой Porsche — внизу.
Предположим, этот запрос используется многократно в нескольких приложениях или несколькими пользователями, и вы хотите убедиться, что все используют один и тот же метод упорядочивания результатов. Для этого вы хотите создать хранимую процедуру, которая сохранит это выражение в именованной процедуре, допускающей повторное использование.
Чтобы создать эту хранимую процедуру, выполните следующий фрагмент кода:
DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;Как объяснялось в предыдущем разделе, первый и последний операторы (DELIMITER // и DELIMITER 😉) сообщают MySQL о необходимости игнорировать символ точки с запятой в качестве разделителя операторов во время создания процедуры.
За оператором SQL CREATE PROCEDURE следует имя процедуры get_all_cars, которое можно определить так, чтобы оно наилучшим образом описывало её действие. После имени процедуры идут скобки (), в которые можно добавить параметры. В этом примере процедура не использует параметры, поэтому скобки пустые. Затем, между операторами BEGIN и END, определяющими начало и конец блока кода процедуры, оператор SELECT, использованный ранее, записан дословно.
База данных ответит сообщением об успешном завершении:
Output
Query OK, 0 rows affected (0.02 sec)Процедура get_all_cars теперь хранится в базе данных и при вызове выполняет сохраненный оператор как есть.
Чтобы выполнить хранимую процедуру, используйте SQL-команду CALL, за которой следует имя процедуры. Попробуйте выполнить созданную процедуру следующим образом:
CALL get_all_cars;
Имя процедуры get_all_cars — это всё, что вам нужно для её использования. Вам больше не нужно вручную вводить какую-либо часть оператора SELECT, который вы использовали ранее. База данных обрабатывает результаты так же, как и выходные данные оператора SELECT:
Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)Вы успешно создали хранимую процедуру без параметров, которая возвращает все автомобили из таблицы «Cars», упорядоченные по определённому принципу. Этот метод можно использовать в различных приложениях.
В следующем разделе вы создадите процедуру, которая принимает параметры для изменения поведения процедуры в зависимости от ввода пользователя.
Создание хранимой процедуры с входными параметрами
В этом разделе вы добавляете входные параметры в определение хранимой процедуры, чтобы пользователи, запускающие процедуру, могли передавать ей данные. Например, пользователи могут задавать фильтры запросов.
Предыдущая хранимая процедура get_all_cars всегда извлекает все автомобили из таблицы cars. Давайте создадим ещё одну процедуру для поиска автомобилей заданного года выпуска. Для этого в определении процедуры необходимо указать параметр get_all_cars.
Выполните следующий код:
DELIMITER //
CREATE PROCEDURE get_cars_by_year(
IN year_filter int
)
BEGIN
SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;В код создания процедуры по сравнению с предыдущим разделом внесено несколько изменений.
Во-первых, имя get_cars_by_year описывает метод: извлечение автомобилей по году их выпуска.
Пустые скобки теперь содержат определение параметра: IN year_filter int. Ключевое слово IN сообщает базе данных, что параметр передаётся в процедуру вызывающим пользователем. year_filter — это произвольное имя параметра. Оно будет использоваться для ссылки на параметр в коде процедуры. Наконец, int — это тип данных. В данном случае год выпуска выражается числовым значением.
Параметр year_filter, определенный после имени процедуры, появляется в операторе SELECT в предложении WHERE year = year_filter и фильтрует таблицу автомобилей по году их выпуска.
База данных снова ответит сообщением об успешном завершении:
Output
Query OK, 0 rows affected (0.02 sec)Попробуйте запустить процедуру, не передавая ей никаких параметров, как и раньше:
CALL get_cars_by_year;
База данных MySQL возвращает сообщение об ошибке:
Error message
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0На этот раз хранимая процедура ожидает предоставления параметра, но он не был предоставлен. Чтобы вызвать хранимую процедуру с параметрами, вы можете указать значения параметров в скобках в том же порядке, в котором ожидает процедура. Чтобы получить список автомобилей, выпущенных в 2017 году, выполните следующую команду:
CALL get_cars_by_year(2017);
Теперь вызванная процедура выполняется корректно и возвращает список автомобилей за данный год:
Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
+---------+---------------+------+-----------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)В этом примере вы узнали, как передавать входные параметры в хранимые процедуры и использовать их в запросах внутри процедуры для предоставления параметров фильтра.
В следующем разделе вы будете использовать выходные параметры для создания процедур, возвращающих несколько различных значений за одно выполнение.
Создание хранимой процедуры с входными и выходными параметрами
В обоих предыдущих примерах созданные вами хранимые процедуры вызывали оператор SELECT для возврата набора результатов. Но в некоторых случаях вам может потребоваться хранимая процедура, которая возвращает несколько различных значений вместе, а не один набор результатов запроса.
Предположим, вы хотите создать процедуру, которая предоставляет сводную информацию об автомобилях определенного года, включая количество автомобилей в коллекции и их рыночную стоимость (минимальную, максимальную и среднюю).
Для этого можно использовать параметры OUT при создании новой хранимой процедуры. Как и параметры IN, параметры OUT имеют имя и тип данных. Однако вместо передачи данных в хранимую процедуру они могут быть заполнены данными из хранимой процедуры для возврата значений вызывающему пользователю.
Создайте процедуру get_car_stats_by_year, которая возвращает сводные данные по автомобилям заданного года выпуска, используя выходные параметры:
DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
IN year_filter int,
OUT cars_number int,
OUT min_value decimal(10, 2),
OUT avg_value decimal(10, 2),
OUT max_value decimal(10, 2)
)
BEGIN
SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
INTO cars_number, min_value, avg_value, max_value
FROM cars
WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;На этот раз, наряду с входным параметром year_filter, который используется для фильтрации автомобилей по году выпуска, в блоке в скобках определены четыре выходных параметра. Параметр cars_number представлен типом данных int и используется для возврата количества автомобилей в наборе. Параметры min_value, avg_value и max_value представляют рыночную стоимость и определяются как десятичные числа (10, 2) (аналогично столбцу value в таблице cars). Они используются для возврата информации о самом дешёвом и самом дорогом автомобиле в наборе, а также средней цене всех соответствующих автомобилей.
Оператор SELECT ищет четыре значения из таблицы автомобилей, используя математические функции SQL: COUNT для получения общего количества автомобилей и MIN, AVG и MAX для получения минимального, среднего и максимального значений из столбца значений.
Чтобы указать базе данных, что результаты этого запроса следует сохранить в выходных параметрах хранимой процедуры, введено новое ключевое слово INTO. После ключевого слова INTO перечислены имена четырёх параметров процедуры, связанных с полученными данными. Это приведёт к тому, что MySQL сохранит значение COUNT(*) в параметре cars_number, результат MIN(value) — в параметре min_value и так далее.
База данных подтверждает успешное создание процедуры:
Output
Query OK, 0 rows affected (0.02 sec)Теперь запустите новую процедуру, выполнив следующее:
CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
Четыре новых параметра начинаются с символа @. Это имена локальных переменных в консоли MySQL, которые можно использовать для временного хранения данных. При передаче этих переменных в созданную вами хранимую процедуру процедура вставляет значения в эти переменные.
База данных ответит следующим образом:
Output
Query OK, 1 row affected (0.00 sec)Это отличается от предыдущего поведения, где результаты сразу отображались на экране. Это связано с тем, что результаты хранимой процедуры сохраняются в выходных параметрах, а не возвращаются как результаты запроса. Чтобы получить доступ к результатам, вы можете выбрать их непосредственно в оболочке MySQL следующим образом:
SELECT @number, @min, @avg, @max;
С помощью этого запроса вы выбираете значения из локальных переменных, а не вызываете процедуру повторно. Хранимая процедура сохраняет свои результаты в этих переменных, и данные доступны до выхода из оболочки.
Примечание: Подробнее об использовании пользовательских переменных в MySQL см. в разделе «Пользовательские переменные» документации. При разработке приложений способы доступа к данным, возвращаемым хранимыми процедурами, различаются в зависимости от языка программирования и фреймворка. В случае сомнений обратитесь к документации по выбранному вами языку программирования и фреймворку.
На выходе отображаются значения переменных запроса:
Output
+---------+----------+-----------+-----------+
| @number | @min | @avg | @max |
+---------+----------+-----------+-----------+
| 2 | 48880.00 | 192090.00 | 335300.00 |
+---------+----------+-----------+-----------+
1 row in set (0.00 sec)Значения соответствуют количеству автомобилей, произведенных в 2017 году, а также минимальной, средней и максимальной рыночной стоимости автомобилей этого года выпуска.
В этом примере вы узнали, как использовать выходные параметры для возврата нескольких различных значений из хранимой процедуры для последующего использования. В следующем разделе вы узнаете, как удалять созданные процедуры.
Удалить хранимые процедуры
В этом разделе вы удалите хранимые процедуры из базы данных.
Иногда созданная вами процедура может больше не понадобиться. В других ситуациях вам может потребоваться изменить способ её работы. MySQL не позволяет изменять определение процедуры после её создания, поэтому единственный способ — сначала удалить процедуру, а затем создать её заново с необходимыми изменениями.
Удалим последнюю процедуру, get_car_stats_by_year. Для этого можно использовать оператор DROP PROCEDURE:
DROP PROCEDURE get_car_stats_by_year;
База данных подтверждает успешное удаление процедуры сообщением об успешном завершении:
Output
Query OK, 0 rows affected (0.02 sec)Вы можете убедиться, что процедура удалена, попытавшись вызвать её. Выполните:
CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
На этот раз вы увидите сообщение об ошибке, что процедура отсутствует в базе данных:
Error message
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not existВ этом разделе вы узнали, как удалять хранимые процедуры из базы данных.
Результат
Следуя этому руководству, вы узнали, что такое хранимые процедуры и как их использовать в MySQL для хранения многократно используемых операторов в именованных процедурах и их последующего выполнения. Вы создали хранимые процедуры без параметров и процедуры с входными и выходными параметрами, что сделало их более гибкими.
