Процедуры в MySQL 5: Подробное описание и примеры применения

Процедуры (Stored Procedures) в MySQL 5 - это именованные наборы SQL-операторов, которые хранятся на сервере базы данных и могут быть вызваны по имени. Они позволяют инкапсулировать логику, повторно использовать код и повысить производительность за счет предварительной компиляции и оптимизации.

Основные преимущества использования процедур:

1. Повторное использование кода: Вместо многократного написания одного и того же SQL-кода, вы можете создать процедуру и вызывать её из разных частей вашего приложения.
2. Модульность: Процедуры помогают разбить сложную логику на более мелкие, управляемые части.
3. Безопасность: Вы можете ограничить доступ к таблицам, предоставив пользователям доступ только к процедурам, которые выполняют определенные действия. Это помогает предотвратить SQL-инъекции и несанкционированный доступ к данным.
4. Производительность: При первом вызове процедура компилируется и оптимизируется сервером. Последующие вызовы используют уже оптимизированный план выполнения, что может значительно повысить производительность, особенно для сложных запросов.
5. Уменьшение сетевого трафика: Вместо отправки множества SQL-запросов клиентом на сервер, вы можете отправить один вызов процедуры, который выполнит все необходимые действия на сервере.
6. Централизованное управление логикой: Изменения в логике приложения можно внести в процедуру на сервере, не затрагивая код клиентского приложения.

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

DELIMITER //  -- Изменяем разделитель (по умолчанию ';'), чтобы он не конфликтовал с ';' внутри процедуры.

CREATE PROCEDURE procedure_name ( [parameter_list] )
BEGIN
    -- SQL-операторы, составляющие тело процедуры
END //

DELIMITER ;  -- Возвращаем разделитель по умолчанию.

Элементы синтаксиса:

• DELIMITER: Позволяет изменить стандартный разделитель команд MySQL (обычно точка с запятой ;) на другой, чтобы избежать конфликтов внутри тела процедуры, где точка с запятой используется для разделения операторов.
• CREATE PROCEDURE: Ключевые слова для создания процедуры.
• procedure_name: Имя процедуры. Должно быть уникальным в пределах базы данных.
• parameter_list: Список параметров, которые процедура может принимать. Параметры могут быть входными (IN), выходными (OUT) или входными-выходными (INOUT).
  • IN parameter_name data_type: Входной параметр. Значение передается в процедуру, но изменения параметра внутри процедуры не видны вызывающей стороне.
  • OUT parameter_name data_type: Выходной параметр. Процедура устанавливает значение этого параметра, и это значение становится доступным вызывающей стороне.
  • INOUT parameter_name data_type: Входной-выходной параметр. Значение передается в процедуру, процедура может его изменить, и измененное значение возвращается вызывающей стороне.
• BEGIN ... END: Блок, содержащий тело процедуры – набор SQL-операторов.
• Внутри процедуры можно использовать: * SELECT, INSERT, UPDATE, DELETE и другие DML-операторы. * Условные операторы IF...THEN...ELSEIF...ELSE...END IF. * Циклы LOOP, WHILE, REPEAT. * Обработку ошибок с помощью DECLARE ... HANDLER. * Локальные переменные, объявленные с помощью DECLARE. * Вызовы других процедур и функций.

Вызов процедуры:

CALL procedure_name ( [argument_list] );

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

DROP PROCEDURE procedure_name;

Примеры применения процедур в MySQL 5:

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

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

INSERT INTO departments (name) VALUES ('Sales'), ('Marketing'), ('Engineering');

INSERT INTO employees (first_name, last_name, salary, department_id) VALUES
('John', 'Doe', 60000, 1),
('Jane', 'Smith', 75000, 2),
('Peter', 'Jones', 90000, 3),
('Alice', 'Brown', 55000, 1),
('Bob', 'Wilson', 80000, 2);

1. Простая процедура без параметров (выборка данных):

DELIMITER //

CREATE PROCEDURE GetEmployees()
BEGIN
    SELECT id, first_name, last_name, salary
    FROM employees;
END //

DELIMITER ;

CALL GetEmployees(); -- Вызывает процедуру и выводит список сотрудников.

2. Процедура с входным параметром (фильтрация данных):

DELIMITER //

CREATE PROCEDURE GetEmployeesByDepartment(IN id_department INT)
BEGIN
    SELECT id, first_name, last_name, salary, department_id
    FROM employees
    WHERE department_id = id_department;
END //

DELIMITER ;

CALL GetEmployeesByDepartment(1); -- Выводит сотрудников отдела Sales (department_id = 1).
CALL GetEmployeesByDepartment(2); -- Выводит сотрудников отдела Marketing (department_id = 2).

3. Процедура с выходным параметром (получение агрегированных данных):

DELIMITER //

CREATE PROCEDURE GetTotalSalary(OUT total DECIMAL(12, 2))
BEGIN
    SELECT SUM(salary) INTO total
    FROM employees;
END //

DELIMITER ;

CALL GetTotalSalary(@total_salary); -- Вызывает процедуру.
SELECT @total_salary;  -- Выводит общую сумму зарплат.

4. Процедура с входным и выходным параметрами (поиск и возврат данных):

DELIMITER //

CREATE PROCEDURE GetEmployeeById(IN emp_id INT, OUT emp_first_name VARCHAR(50), OUT emp_last_name VARCHAR(50))
BEGIN
    SELECT first_name, last_name INTO emp_first_name, emp_last_name
    FROM employees
    WHERE id = emp_id;
END //

DELIMITER ;

CALL GetEmployeeById(3, @first_name, @last_name);
SELECT @first_name, @last_name; -- Выводит имя и фамилию сотрудника с id = 3 (Peter Jones).

5. Процедура с INOUT параметром (изменение и возврат значения):

DELIMITER //

CREATE PROCEDURE IncreaseSalary(INOUT emp_id INT, IN increase_percentage DECIMAL(5, 2))
BEGIN
  DECLARE current_salary DECIMAL(10,2);

    -- Получаем текущую зарплату
    SELECT salary INTO current_salary FROM employees WHERE id = emp_id;

    -- Увеличиваем зарплату, если сотрудник найден
   IF current_salary IS NOT NULL THEN
        UPDATE employees
        SET salary = salary * (1 + increase_percentage / 100)
        WHERE id = emp_id;

        -- Возвращаем новую зарплату через тот же параметр emp_id (не лучшее решение, чисто для демонстрации INOUT)
        SELECT salary INTO current_salary FROM employees WHERE id = emp_id;
        SET emp_id = current_salary;
    ELSE
        -- Если сотрудник не найден, возвращаем -1 в качестве индикатора ошибки.
        SET emp_id = -1;
    END IF;
END //

```sql
DELIMITER //

CREATE PROCEDURE IncreaseSalaryBetter(IN emp_id INT, INOUT emp_salary DECIMAL(10,2), IN increase_percentage DECIMAL(5, 2))
BEGIN
    -- Получаем текущую зарплату, если ID корректный
    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;

    -- Увеличиваем зарплату, если сотрудник найден
    IF emp_salary IS NOT NULL THEN
        SET emp_salary = emp_salary * (1 + increase_percentage / 100);
        UPDATE employees
        SET salary = emp_salary
        WHERE id = emp_id;
    ELSE
        -- Если сотрудник не найден, оставляем emp_salary без изменений (или можно присвоить NULL).
        SET emp_salary = NULL;
    END IF;

END //

DELIMITER ;

SET @salary = 0; -- Инициализируем переменную.
CALL IncreaseSalaryBetter(1, @salary, 10); -- Увеличиваем зарплату на 10%.
SELECT @salary; -- Выведет новую зарплату (66000).

CALL IncreaseSalaryBetter(999, @salary, 5); -- Пытаемся увеличить зарплату несуществующему сотруднику.
SELECT @salary; -- Выведет NULL (или предыдущее значение @salary, если оно не было NULL).

6. Процедура с условными операторами (логика ветвления):

DELIMITER //

CREATE PROCEDURE ClassifySalary(IN emp_id INT, OUT salary_class VARCHAR(20))
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);

    SELECT salary INTO emp_salary
    FROM employees
    WHERE id = emp_id;

    IF emp_salary IS NULL THEN
        SET salary_class = 'Employee not found';
    ELSEIF emp_salary > 80000 THEN
        SET salary_class = 'High';
    ELSEIF emp_salary > 60000 THEN
        SET salary_class = 'Medium';
    ELSE
        SET salary_class = 'Low';
    END IF;
END //

DELIMITER ;

CALL ClassifySalary(1, @classification);
SELECT @classification;  -- Выведет 'Medium'

CALL ClassifySalary(3, @classification);
SELECT @classification;  -- Выведет 'High'

CALL ClassifySalary(999, @classification);
SELECT @classification;  -- Выведет 'Employee not found'

7. Процедура с циклом (обработка данных в цикле):

DELIMITER //

CREATE PROCEDURE ApplyBonus(IN bonus_percentage DECIMAL(5, 2))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO emp_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE employees
        SET salary = salary * (1 + bonus_percentage / 100)
        WHERE id = emp_id;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

CALL ApplyBonus(5); -- Применяет бонус 5% ко всем сотрудникам.

8. Процедура с обработкой ошибок:

DELIMITER //

CREATE PROCEDURE InsertEmployee(
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_salary DECIMAL(10, 2),
    IN p_department_id INT,
    OUT p_success BOOLEAN
)
BEGIN
    -- Обработчик ошибок для случая дублирования ключа (например, если в будущем добавим UNIQUE constraint)
    DECLARE EXIT HANDLER FOR 1062 -- Код ошибки для дубликата ключа.
    BEGIN
        SET p_success = FALSE;
        -- Можно добавить запись в лог-файл или другую обработку ошибки.
        SELECT 'Error: Duplicate key violation.' AS ErrorMessage;
    END;

     -- Обработчик ошибок для случая, когда department_id не существует в таблице departments.
    DECLARE EXIT HANDLER FOR 1452  --  Код ошибки FK constraint violation.
    BEGIN
        SET p_success = FALSE;
        SELECT 'Error: Invalid department ID.' AS ErrorMessage;
    END;


    -- Обработчик для всех остальных SQL ошибок (кроме тех, что выше)
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
       SET p_success = FALSE;
       SELECT 'Error: An unexpected SQL error occurred.' AS ErrorMessage;
    END;

    INSERT INTO employees (first_name, last_name, salary, department_id)
    VALUES (p_first_name, p_last_name, p_salary, p_department_id);

    SET p_success = TRUE; -- Если дошли до сюда, значит, ошибок не было.
END //

DELIMITER ;

CALL InsertEmployee('Test', 'User', 50000, 1, @success); -- Успешная вставка.
SELECT @success; -- Выведет 1 (TRUE)

CALL InsertEmployee('Test', 'User', 60000, 1, @success); -- Попытка дублирования (если есть UNIQUE constraint).
SELECT @success; -- Выведет 0 (FALSE)

CALL InsertEmployee('Another', 'Test', 70000, 999, @success); -- Несуществующий department_id.
SELECT @success;  -- Выведет 0 (FALSE)

9. Вложенные процедуры (вызов одной процедуры из другой):

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

DELIMITER //

-- Процедура для получения имени отдела по ID.
CREATE PROCEDURE GetDepartmentName(IN dept_id INT, OUT dept_name VARCHAR(100))
BEGIN
    SELECT name INTO dept_name FROM departments WHERE id = dept_id;
    IF dept_name IS NULL THEN
      SET dept_name = 'Unknown Department';
    END IF;
END //

DELIMITER ;


DELIMITER //

-- Процедура для получения информации о сотруднике, включая имя отдела (использует GetDepartmentName).
CREATE PROCEDURE GetEmployeeInfo(IN emp_id INT, OUT emp_info TEXT)
BEGIN
    DECLARE emp_first_name VARCHAR(50);
    DECLARE emp_last_name VARCHAR(50);
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE dept_name VARCHAR(100);
    DECLARE dept_id INT;

    SELECT first_name, last_name, salary, department_id
    INTO emp_first_name, emp_last_name, emp_salary, dept_id
    FROM employees
    WHERE id = emp_id;

    IF emp_first_name IS NULL THEN
        SET emp_info = 'Employee not found.';
    ELSE
        -- Вызываем другую процедуру!
        CALL GetDepartmentName(dept_id, dept_name);

        SET emp_info = CONCAT(
            'ID: ', emp_id, CHAR(10),
            'Name: ', emp_first_name, ' ', emp_last_name, CHAR(10),
            'Salary: ', emp_salary, CHAR(10),
            'Department: ', dept_name
        );
    END IF;
END //

DELIMITER ;

CALL GetEmployeeInfo(1, @employee_details);
SELECT @employee_details;

CALL GetEmployeeInfo(999, @employee_details);
SELECT @employee_details;

10. Использование локальных переменных:

DELIMITER //

CREATE PROCEDURE CalculateStatistics(OUT avg_salary DECIMAL(10, 2), OUT max_salary DECIMAL(10, 2))
BEGIN
    -- Объявляем локальные переменные
    DECLARE total_salary DECIMAL(12, 2) DEFAULT 0;
    DECLARE num_employees INT DEFAULT 0;

    -- Вычисляем общую зарплату и количество сотрудников
    SELECT SUM(salary), COUNT(*) INTO total_salary, num_employees
    FROM employees;

    -- Вычисляем среднюю зарплату (избегаем деления на ноль)
    IF num_employees > 0 THEN
        SET avg_salary = total_salary / num_employees;
    ELSE
        SET avg_salary = 0;
    END IF;

```sql
    -- Находим максимальную зарплату
    SELECT MAX(salary) INTO max_salary FROM employees;
     IF max_salary IS NULL THEN  -- Если таблица пустая
        SET max_salary = 0;
     END IF;

END //

DELIMITER ;

CALL CalculateStatistics(@average, @maximum);
SELECT @average, @maximum;

Ключевые моменты и лучшие практики:

• Именование: Используйте осмысленные имена для процедур и параметров. Хорошая практика – использовать префиксы (например, sp_ для процедур) или следовать соглашению о наименовании, принятому в вашей команде.
• Комментарии: Добавляйте комментарии к вашим процедурам, чтобы объяснить их назначение, параметры и логику работы.
• Обработка ошибок: Всегда обрабатывайте возможные ошибки (например, деление на ноль, несуществующие данные, нарушения ограничений). Используйте DECLARE ... HANDLER для перехвата исключений.
• Транзакции: Если процедура выполняет несколько операций, которые должны быть атомарными (выполнены все вместе или не выполнены вообще), используйте транзакции (START TRANSACTION, COMMIT, ROLLBACK).
• Тестирование: Тщательно тестируйте ваши процедуры, проверяя различные входные данные и граничные условия.
• Безопасность: Не используйте динамический SQL (сборку SQL-запросов из строк) без крайней необходимости и должной защиты от SQL-инъекций. Предпочитайте параметризованные запросы. Предоставляйте пользователям доступ только к тем процедурам, которые им необходимы.
• Оптимизация: Анализируйте производительность ваших процедур. Используйте EXPLAIN для просмотра плана выполнения запросов и оптимизируйте запросы внутри процедур (добавляйте индексы, избегайте ненужных подзапросов и т.д.).
• Версионирование: При изменении процедуры, особенно если она используется в production-среде, важно следить за версиями. Можно добавлять комментарии с датой и описанием изменений, или использовать систему контроля версий (например, Git) для хранения кода процедур. Это поможет отслеживать изменения и откатываться к предыдущим версиям при необходимости.
• Разделение логики: Старайтесь, чтобы процедуры выполняли одну конкретную задачу. Если процедура становится слишком большой и сложной, разбейте ее на несколько более мелких процедур.
• Документация: Поддерживайте актуальную документацию по вашим процедурам. Это особенно важно в больших проектах, где с кодом работают несколько разработчиков. Документация должна описывать назначение процедуры, ее параметры, возвращаемые значения и примеры использования.

Различия между процедурами и функциями (UDF - User-Defined Functions):

В MySQL есть и процедуры, и функции. Вот основные отличия:

Характеристика	Процедура (Stored Procedure)	Функция (UDF)
Возвращаемое значение	Может возвращать несколько значений через выходные (OUT) или входные-выходные (INOUT) параметры. Может вообще не возвращать значений (если нет OUT или INOUT параметров).	Всегда возвращает одно значение. Тип возвращаемого значения указывается при создании функции.
Вызов	Вызывается с помощью оператора CALL.	Может использоваться в выражениях, как встроенные функции (например, SELECT my_function(column) FROM table).
Побочные эффекты	Может иметь побочные эффекты (изменять данные в таблицах, вызывать другие процедуры и т.д.).	В идеале функции не должны иметь побочных эффектов. Они предназначены для вычисления значения на основе входных данных, а не для изменения состояния базы данных. В MySQL 5, строго говоря, функции могут менять данные, но это плохая практика.
Использование	Используется для выполнения набора действий, инкапсуляции бизнес-логики, управления данными.	Используется для вычисления значений, которые могут быть использованы в других запросах.
DML	Может содержать любые DML операторы (INSERT, UPDATE, DELETE)	Внутри функций в MySQL 5 есть ограничения на использование DML. Лучше всего использовать функции только для чтения данных (SELECT). В более поздних версиях MySQL ограничения более строгие.

В заключение:

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