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

Введение

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

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

Skills Graph

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

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

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

mysql -u root -p

Вам будет предложено ввести пароль для пользователя root. Введите пароль и нажмите Enter.

Теперь создайте базу данных с именем testdb:

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

Далее создайте таблицу с именем employees со следующей структурой:

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255)
);

Теперь, когда у нас есть таблица, создадим хранимую процедуру для вставки данных в нее. Хранимую процедуру создают с помощью оператора 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.

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

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

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

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

Вызов процедуры с использованием оператора CALL

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

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

mysql -u root -p

Введите пароль при запросе.

USE testdb;

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

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

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

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

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

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

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

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, если вы еще не подключены:

mysql -u root -p

Введите пароль при запросе.

USE testdb;

Удалите существующую процедуру:

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) VALUES (employee_name, employee_department);
    -- Add salary update after insert
    UPDATE employees SET salary = employee_salary WHERE name = employee_name AND department = employee_department;
END //
DELIMITER ;

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

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

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• Мы добавили оператор UPDATE для обновления зарплаты только что вставленного сотрудника. Поскольку оператор INSERT не поддерживает непосредственное установление зарплаты, мы сначала вставляем сотрудника, а затем обновляем зарплату на основе имени и отдела.

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

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

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

SELECT * FROM employees;

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

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

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

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

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

mysql -u root -p

Введите пароль при запросе.

USE testdb;

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

DROP PROCEDURE [IF EXISTS] procedure_name;

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

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

DROP PROCEDURE IF EXISTS insert_employee;

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

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

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.

Резюме

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

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