Функции MySQL и пользовательская логика

Введение

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

Шаги включают подключение к серверу MySQL, создание базы данных (при необходимости), определение функции calculate_rectangle_area с входными параметрами для длины и ширины, а также указание типа возвращаемых данных. Тело функции вычисляет площадь и возвращает результат. Последующие шаги будут охватывать использование этой функции в запросе SELECT, тестирование ее вывода с использованием образца данных и, наконец, удаление функции.

Подключение к MySQL и создание базы данных

На этом этапе вы подключитесь к серверу MySQL и создадите базу данных, в которой будете создавать и использовать свою пользовательскую функцию.

Сначала откройте терминал и подключитесь к серверу MySQL от имени пользователя root. Выполните следующую команду один раз в начале лабораторной работы:

sudo mysql -u root

Эта команда использует sudo для запуска клиента mysql от имени пользователя root. Поскольку у пользователя root в этой среде не установлен пароль, вы будете подключены напрямую к оболочке MySQL. Вы увидите приглашение mysql>.

Оставайтесь в оболочке MySQL для всех последующих шагов. Не выходите до конца лабораторной работы.

Теперь давайте создадим базу данных с именем my_functions_db. Эта база данных будет использоваться для хранения нашей таблицы и функции. Выполните следующую SQL-команду в приглашении mysql>:

CREATE DATABASE IF NOT EXISTS my_functions_db;

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

USE my_functions_db;

Вы должны увидеть сообщение о том, что база данных была изменена.

Теперь вы подключены к базе данных my_functions_db и готовы к созданию своей пользовательской функции на следующем шаге.

Создание функции для расчета площади прямоугольника

На этом этапе вы создадите пользовательскую функцию в MySQL для расчета площади прямоугольника. Эта функция будет принимать длину и ширину в качестве входных данных и возвращать рассчитанную площадь.

Убедитесь, что вы все еще находитесь в оболочке MySQL и уже выбрали базу данных my_functions_db, как описано в предыдущем шаге.

Теперь давайте создадим функцию с именем calculate_rectangle_area. Эта функция будет принимать два целочисленных параметра, length и width, и возвращать целое число, представляющее площадь.

Выполните следующую SQL-команду в оболочке MySQL:

DELIMITER //

CREATE FUNCTION calculate_rectangle_area (length INT, width INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE area INT;
  SET area = length * width;
  RETURN area;
END //

DELIMITER ;

Разберем эту команду:

• DELIMITER //: Это изменяет разделитель операторов с точки с запятой (;) по умолчанию на //. Это необходимо, поскольку определение функции содержит точки с запятой внутри своего тела.
• CREATE FUNCTION calculate_rectangle_area (length INT, width INT): Это определяет имя функции (calculate_rectangle_area) и ее входные параметры (length и width, оба типа INT).
• RETURNS INT: Это указывает, что функция будет возвращать целочисленное значение.
• DETERMINISTIC: Это ключевое слово указывает, что функция всегда будет выдавать одинаковый результат для одинаковых входных значений. Это помогает MySQL оптимизировать запросы, использующие функцию.
• BEGIN ... END: Этот блок содержит логику функции.
• DECLARE area INT;: Это объявляет локальную переменную с именем area типа INT внутри функции.
• SET area = length * width;: Это вычисляет площадь, умножая параметры length и width, и присваивает результат переменной area.
• RETURN area;: Это возвращает значение, хранящееся в переменной area, в качестве выходных данных функции.
• END //: Это отмечает конец определения функции, используя временный разделитель //.
• DELIMITER ;: Это возвращает разделитель обратно к точке с запятой (;) по умолчанию.

После выполнения этой команды функция calculate_rectangle_area будет создана в базе данных my_functions_db.

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

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

Эта команда отобразит информацию о функции, подтвердив ее существование.

Использование функции в запросе SELECT

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

Продолжайте работать в той же сессии оболочки MySQL и убедитесь, что вы по-прежнему используете базу данных my_functions_db.

Сначала давайте создадим таблицу с именем rectangles для хранения размеров различных прямоугольников. Выполните следующую SQL-команду:

CREATE TABLE IF NOT EXISTS rectangles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  length INT,
  width INT
);

Эта команда создает таблицу с автоматически увеличивающимся id в качестве первичного ключа и столбцами для length и width.

Далее, вставьте некоторые примеры данных в таблицу rectangles:

INSERT INTO rectangles (length, width) VALUES (5, 10);
INSERT INTO rectangles (length, width) VALUES (7, 8);
INSERT INTO rectangles (length, width) VALUES (12, 3);

Эти команды добавляют три строки в таблицу rectangles с различными значениями длины и ширины.

Теперь вы можете использовать функцию calculate_rectangle_area в запросе SELECT для получения размеров прямоугольников и их рассчитанных площадей. Выполните следующую SQL-команду:

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

В этом запросе:

• SELECT id, length, width: Это выбирает столбцы id, length и width из таблицы rectangles.
• calculate_rectangle_area(length, width): Это вызывает функцию calculate_rectangle_area, передавая значения из столбцов length и width каждой строки в качестве аргументов.
• AS area: Это присваивает псевдоним area результату, возвращаемому функцией, делая выходной столбец более читаемым.
• FROM rectangles: Это указывает, что данные должны быть получены из таблицы rectangles.

Результат этого запроса покажет id, length, width и рассчитанную area для каждого прямоугольника в таблице. Вы должны увидеть вывод, похожий на этот:

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
+----+--------+-------+------+

Это демонстрирует, как эффективно использовать вашу пользовательскую функцию в стандартном SQL-запросе для выполнения вычислений над вашими данными.

Тестирование вывода функции с использованием примеров данных

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

Продолжайте работать в той же сессии оболочки MySQL и убедитесь, что вы по-прежнему используете базу данных my_functions_db.

Теперь вставьте дополнительные строки в таблицу rectangles с различными значениями длины и ширины:

INSERT INTO rectangles (length, width) VALUES (10, 10);
INSERT INTO rectangles (length, width) VALUES (2, 5);
INSERT INTO rectangles (length, width) VALUES (15, 4);

Эти команды добавляют еще три строки в таблицу rectangles.

Теперь снова выполните запрос SELECT, чтобы увидеть рассчитанные площади для всех прямоугольников, включая только что добавленные:

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

Вывод теперь будет включать площади для новых прямоугольников:

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
|  4 |     10 |    10 |  100 |
|  5 |      2 |     5 |   10 |
|  6 |     15 |     4 |   60 |
+----+--------+-------+------+

Просмотрите вывод и убедитесь, что рассчитанные площади для новых строк верны:

• Для длины 10 и ширины 10 площадь составляет 100 (10 * 10).
• Для длины 2 и ширины 5 площадь составляет 10 (2 * 5).
• Для длины 15 и ширины 4 площадь составляет 60 (15 * 4).

Этот шаг закрепляет ваше понимание того, как использовать функцию с различными данными, и подтверждает ее точность.

Удаление функции и таблицы

На этом заключительном этапе вы очистите базу данных, удалив функцию calculate_rectangle_area и таблицу rectangles. Это хорошая практика — удалять объекты, которые вам больше не нужны.

Продолжайте работать в той же сессии оболочки MySQL и убедитесь, что вы по-прежнему используете базу данных my_functions_db.

Сначала удалим функцию calculate_rectangle_area. Выполните следующую SQL-команду:

DROP FUNCTION IF EXISTS calculate_rectangle_area;

• DROP FUNCTION: Эта команда используется для удаления функции.
• IF EXISTS: Этот параметр предотвращает ошибку, если функция не существует.

Далее удалим таблицу rectangles. Выполните следующую SQL-команду:

DROP TABLE IF EXISTS rectangles;

• DROP TABLE: Эта команда используется для удаления таблицы.
• IF EXISTS: Этот параметр предотвращает ошибку, если таблица не существует.

После выполнения этих команд функция и таблица будут удалены из базы данных my_functions_db.

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

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

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

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

SELECT * FROM rectangles;

Эта команда должна вернуть ошибку, похожую на ERROR 1146 (42S02): Table 'my_functions_db.rectangles' doesn't exist, указывающую на то, что таблица удалена.

Теперь вы можете выйти из оболочки MySQL, набрав exit и нажав Enter.

Вы успешно создали, использовали, протестировали и удалили пользовательскую функцию и таблицу в MySQL.

Резюме

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

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

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

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