Основы хранимых процедур MySQL

Введение

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

Вы начнете с создания базы данных и таблицы с именем employees. Затем вы напишете хранимую процедуру с именем insert_employee для вставки данных в таблицу employees. Вы узнаете, как вызывать эту процедуру с помощью оператора CALL и как добавлять входные параметры в процедуру. Наконец, вы узнаете, как удалить процедуру с помощью оператора DROP PROCEDURE.

Примечание: Для этой лабораторной работы вам нужно будет войти в оболочку MySQL только один раз в начале и выйти в самом конце. Все SQL-команды в следующих шагах должны выполняться в рамках одной и той же сессии MySQL. Нет необходимости повторно подключаться или отключаться от MySQL между шагами.

Создание процедуры для вставки данных

На этом шаге вы научитесь создавать хранимую процедуру в MySQL, которая вставляет данные в таблицу. Хранимые процедуры — это предварительно скомпилированные SQL-инструкции, хранящиеся в базе данных. Их можно выполнять по имени, что может повысить производительность и безопасность.

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

sudo mysql -u root

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

После подключения вы окажетесь в оболочке MySQL. Теперь переключитесь на базу данных testdb, которая была создана во время настройки:

USE testdb;

Теперь, когда мы находимся в нужной базе данных, давайте создадим хранимую процедуру для вставки данных в таблицу employees. Хранимая процедура создается с помощью оператора CREATE PROCEDURE. Мы определим процедуру с именем insert_employee, которая вставляет новую запись сотрудника.

Вот SQL-код для хранимой процедуры:

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

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

• DELIMITER //: Это изменяет разделитель инструкций с ; на //. Это необходимо, потому что сама процедура содержит точки с запятой, и нам нужно сообщить MySQL, чтобы он рассматривал все определение процедуры как единую инструкцию.
• CREATE PROCEDURE insert_employee: Это объявляет создание хранимой процедуры с именем insert_employee.
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)): Это определяет входные параметры для процедуры. employee_name и employee_department — это имена параметров, а VARCHAR(255) — их тип данных. Ключевое слово IN указывает, что это входные параметры.
• BEGIN ... END: Этот блок содержит SQL-инструкции, которые будут выполняться при вызове процедуры.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department);: Это SQL-инструкция, которая вставляет новую строку в таблицу employees, используя значения, переданные в качестве входных параметров.
• DELIMITER ;: Это сбрасывает разделитель инструкций обратно на ;.

Чтобы выполнить этот код, скопируйте и вставьте его непосредственно в оболочку MySQL.

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

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

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

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

Вызов процедуры с помощью оператора CALL

На предыдущем шаге вы создали хранимую процедуру с именем insert_employee. На этом шаге вы научитесь вызывать эту процедуру с помощью оператора CALL.

Напоминание: Вы все еще должны находиться в оболочке MySQL и использовать базу данных testdb. Если это не так, переключитесь на нее с помощью:

USE testdb;

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

CALL procedure_name(argument1, argument2, ...);

В нашем случае имя процедуры — insert_employee, и она принимает два аргумента: имя сотрудника и отдел сотрудника.

Вызовем процедуру insert_employee для вставки нового сотрудника по имени "Alice Smith" в отдел "Engineering":

CALL insert_employee('Alice Smith', 'Engineering');

Эта инструкция выполнит процедуру insert_employee с указанными аргументами.

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

SELECT * FROM employees;

Вы должны увидеть новую строку в таблице с именем "Alice Smith" и отделом "Engineering". id будет назначен автоматически.

Вставим еще одного сотрудника, "Bob Johnson", в отдел "Marketing":

CALL insert_employee('Bob Johnson', 'Marketing');

Снова проверьте вставку, выполнив запрос к таблице employees:

SELECT * FROM employees;

Теперь вы должны увидеть две строки в таблице: одну для "Alice Smith" и одну для "Bob Johnson".

Теперь вы успешно вызвали хранимую процедуру insert_employee с помощью оператора CALL и проверили, что данные были вставлены правильно. Это демонстрирует, как хранимые процедуры могут использоваться для инкапсуляции и повторного использования SQL-логики.

Добавление входного параметра в процедуру

На предыдущих шагах вы создали и вызвали хранимую процедуру с именем insert_employee, которая принимает два входных параметра: employee_name и employee_department. На этом шаге вы научитесь добавлять в процедуру еще один входной параметр.

Давайте добавим параметр employee_salary в процедуру insert_employee. Это позволит нам указывать зарплату сотрудника при вставке новой записи.

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

DROP PROCEDURE IF EXISTS insert_employee;

Теперь давайте создадим измененную хранимую процедуру с новым входным параметром.

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department, salary) VALUES (employee_name, employee_department, employee_salary);
END //
DELIMITER ;

Разберем изменения:

• Мы добавили новый входной параметр IN employee_salary DECIMAL(10, 2) в определение процедуры. DECIMAL(10, 2) — это тип данных для зарплаты, который допускает до 10 цифр с 2 десятичными знаками.
• Мы изменили оператор INSERT, чтобы включить столбец salary и параметр employee_salary.

Теперь вызовем измененную процедуру insert_employee для вставки нового сотрудника по имени "Charlie Brown" в отдел "Finance" с зарплатой 60000.00:

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

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

SELECT * FROM employees;

Вы должны увидеть новую строку в таблице с именем "Charlie Brown", отделом "Finance" и зарплатой 60000.00.

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

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

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

Напоминание: Вы все еще должны находиться в оболочке MySQL и использовать базу данных testdb.

Оператор DROP PROCEDURE используется для удаления хранимой процедуры. Синтаксис выглядит следующим образом:

DROP PROCEDURE [IF EXISTS] procedure_name;

Предложение IF EXISTS является необязательным, но рекомендуется. Оно предотвращает возникновение ошибки, если процедура не существует.

В нашем случае имя процедуры — insert_employee. Удалим процедуру:

DROP PROCEDURE IF EXISTS insert_employee;

Эта инструкция удалит процедуру insert_employee из базы данных testdb.

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

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

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

В качестве альтернативы, если вы попытаетесь вызвать процедуру, вы получите ошибку:

CALL insert_employee('Test', 'Test', 1000);

Это приведет к сообщению об ошибке, похожему на: ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist.

Теперь вы успешно удалили хранимую процедуру insert_employee.

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

exit

На этом завершается лабораторная работа по созданию, вызову, изменению и удалению хранимых процедур в MySQL.

Резюме

В этой лабораторной работе вы изучили основы хранимых процедур MySQL, начав с создания базы данных и таблицы с именем employees. Затем вы определили хранимую процедуру с именем insert_employee, используя оператор CREATE PROCEDURE, которая вставляет данные в таблицу employees. Команда DELIMITER использовалась для обработки точек с запятой внутри определения процедуры.

Лабораторная работа также охватывала определение входных параметров для хранимой процедуры, указание их имен и типов данных. Это позволяет передавать значения в процедуру при ее вызове, делая ее более гибкой и повторно используемой. Вы практиковались в вызове хранимой процедуры с помощью оператора CALL и проверяли вставку данных. Наконец, вы узнали, как удалять хранимую процедуру с помощью оператора DROP PROCEDURE.