Создание хранимых процедур и сложных операций с данными

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

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

Создание хранимой процедуры

MySQL

DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(IN employee_id INT)
BEGIN
    SELECT * FROM employees WHERE id = employee_id;
END //

DELIMITER ;

В этом примере создается процедура GetEmployeeDetails, которая принимает идентификатор сотрудника и возвращает его данные.

PostgreSQL

CREATE OR REPLACE FUNCTION GetEmployeeDetails(employee_id INT)
RETURNS TABLE(id INT, name TEXT, position TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, position FROM employees WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Здесь создается функция GetEmployeeDetails, которая возвращает таблицу с деталями сотрудника.

Вызов хранимой процедуры

MySQL

CALL GetEmployeeDetails(1);

PostgreSQL

SELECT * FROM GetEmployeeDetails(1);

Сложные операции с данными

Транзакции

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

Начало транзакции:

START TRANSACTION;

Фиксация транзакции:

COMMIT;

Отмена транзакции:

ROLLBACK;

Пример:

START TRANSACTION;

INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1, '2024-08-15');
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;

COMMIT;

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

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

Создание и использование курсора (MySQL):

DELIMITER //

CREATE PROCEDURE ProcessOrders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM orders;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO order_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Выполнение операции с order_id
        UPDATE orders SET status = 'Processed' WHERE id = order_id;
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

Использование временных таблиц

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

Создание временной таблицы:

CREATE TEMPORARY TABLE temp_orders (
    order_id INT,
    order_date DATE
);

Вставка данных во временную таблицу:

INSERT INTO temp_orders (order_id, order_date)
SELECT id, order_date FROM orders WHERE status = 'Pending';

Использование данных из временной таблицы:

SELECT * FROM temp_orders;

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

DROP TEMPORARY TABLE temp_orders;

Работа с временными функциями и хранимыми процедурами

Иногда полезно создавать временные функции или процедуры для выполнения специфических задач.

Создание временной функции (MySQL):

DELIMITER //

CREATE FUNCTION CalculateDiscount(price DECIMAL(10, 2), discount_rate DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
BEGIN
    RETURN price * (discount_rate / 100);
END //

DELIMITER ;

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

SELECT CalculateDiscount(100.00, 10.00);

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