Как создать хранимую процедуру?sql-24

Создание хранимой процедуры — это процесс определения именованного блока SQL-кода, который сохраняется в базе данных и может быть выполнен позднее. Рассмотрим детальный процесс создания с примерами для различных СУБД.

Базовый синтаксис создания процедуры

Общая структура создания хранимой процедуры:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [([parameter1 [type] [mode] [DEFAULT value], ...])]
AS
BEGIN
    -- Тело процедуры
    -- SQL-операторы
END;

Пошаговое создание процедуры

1. Определение параметров

Параметры могут быть:

  • IN (входные, по умолчанию)
  • OUT (выходные)
  • INOUT (входные-выходные)

Пример с параметрами:

CREATE PROCEDURE update_employee_salary(
    IN emp_id INT,
    IN salary_increase DECIMAL(10,2),
    OUT new_salary DECIMAL(10,2)
)
AS
BEGIN
    -- Логика процедуры
END;

2. Тело процедуры

Содержит исполняемые SQL-операторы:

CREATE PROCEDURE get_employee_details(IN emp_id INT)
AS
BEGIN
    -- Простой SELECT
    SELECT * FROM employees WHERE id = emp_id;

    -- Можно включать несколько запросов
    SELECT d.name AS department
    FROM departments d
    JOIN employees e ON d.id = e.department_id
    WHERE e.id = emp_id;
END;

3. Добавление логики управления

Пример с условиями и переменными:

CREATE PROCEDURE calculate_bonus(
    IN emp_id INT,
    OUT bonus DECIMAL(10,2)
)
AS
BEGIN
    DECLARE base_salary DECIMAL(10,2);
    DECLARE years_of_service INT;

    -- Получаем данные сотрудника
    SELECT salary, DATEDIFF(YEAR, hire_date, CURRENT_DATE)
    INTO base_salary, years_of_service
    FROM employees
    WHERE id = emp_id;

    -- Логика расчета бонуса
    IF years_of_service > 5 THEN
        SET bonus = base_salary * 0.15;
    ELSEIF years_of_service > 2 THEN
        SET bonus = base_salary * 0.10;
    ELSE
        SET bonus = base_salary * 0.05;
    END IF;
END;

Специфика для разных СУБД

MySQL/MariaDB

DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2),
    OUT status VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET status = 'Error occurred';
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - amount
    WHERE account_id = from_account;

    UPDATE accounts SET balance = balance + amount
    WHERE account_id = to_account;

    COMMIT;
    SET status = 'Transfer successful';
END //
DELIMITER ;

SQL Server

CREATE PROCEDURE dbo.UpdateProductPrice
    @ProductID INT,
    @PriceIncrease DECIMAL(10,2),
    @NewPrice DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Products
        SET Price = Price + @PriceIncrease
        WHERE ProductID = @ProductID;

        SELECT @NewPrice = Price
        FROM Products
        WHERE ProductID = @ProductID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

PostgreSQL

CREATE OR REPLACE PROCEDURE archive_old_orders(
    cutoff_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Перемещение старых заказов в архив
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE order_date < cutoff_date;

    -- Удаление перемещенных записей
    DELETE FROM orders
    WHERE order_date < cutoff_date;

    COMMIT;
END;
$$;

Лучшие практики создания процедур

  1. Именование:

    • Используйте понятные имена (глагол+существительное)
    • Соблюдайте соглашения об именах (например, sp_ префикс в SQL Server)

  2. Обработка ошибок:

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

  3. Документация:

    • Добавляйте комментарии о назначении процедуры
    • Описывайте параметры и возвращаемые значения

Пример хорошо документированной процедуры:

/**
 * Calculates and applies employee bonus based on performance
 * @param emp_id - Employee ID
 * @param year - Year for bonus calculation
 * @return New bonus amount
 */
CREATE PROCEDURE calculate_employee_bonus(
    IN emp_id INT,
    IN year INT,
    OUT bonus DECIMAL(10,2)
)
AS
BEGIN
    -- Логика расчета
END;

Модификация существующих процедур

Для изменения процедуры используйте:

ALTER PROCEDURE procedure_name [параметры] AS ...
-- Или (в некоторых СУБД)
CREATE OR REPLACE PROCEDURE procedure_name ...

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

DROP PROCEDURE [IF EXISTS] procedure_name;

Резюмируем: создание хранимых процедур требует понимания синтаксиса конкретной СУБД, правильного проектирования параметров и включения механизмов обработки ошибок. Хорошо написанные процедуры значительно упрощают работу с базой данных и повышают безопасность системы.

Вопросы

Собеседования