Оптимизация SQL
Полезные скрипты
Использование PGAdmin
Сквозной пример в PostgreSQL
Видеомонтаж
Использование нейросетей
Интерактивные материалы
Офисные приложения

Хранимые процедуры в PostgreSQL: реализация бизнес-логики на уровне базы данных

Информация о материале
Автор: Андрей К.
Категория: Scripts
Просмотров: 461

В PostgreSQL хранимые процедуры представляют собой эффективный инструмент для реализации сложной бизнес-логики непосредственно в базе данных. Если вы уже знакомы с функциями, процедуры откроют новые возможности для структурирования и защиты операций с данными.

Чем процедуры отличаются от функций?

Ключевые различия между процедурами и функциями:

Аспект Функции Процедуры
Возврат значения Всегда возвращают значение Не возвращают значения
Использование Могут быть частью SELECT-запросов Вызываются отдельно командой CALL
Транзакции Не могут управлять транзакциями Могут управлять транзакциями
Цель Преобразование или вычисление данных Выполнение действий с данными
Практический пример: процедура для обновления зарплаты

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

Шаг 1: Создание процедуры update_employee_salary
sql
CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_employee_id INTEGER,
    p_new_salary DECIMAL(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Проверка существования активного сотрудника
    IF NOT EXISTS (
        SELECT 1 
        FROM employees 
        WHERE employee_id = p_employee_id 
          AND is_active = TRUE
    ) THEN
        RAISE EXCEPTION 'Активный сотрудник с ID % не найден', p_employee_id;
    END IF;

    -- Проверка корректности новой зарплаты
    IF p_new_salary <= 0 THEN
        RAISE EXCEPTION 'Зарплата должна быть положительным числом';
    END IF;

    IF p_new_salary > 1000000 THEN
        RAISE EXCEPTION 'Зарплата не может превышать 1 000 000';
    END IF;

    -- Обновление зарплаты
    UPDATE employees 
    SET salary = p_new_salary,
        updated_at = CURRENT_TIMESTAMP
    WHERE employee_id = p_employee_id;

    -- Логирование операции
    INSERT INTO salary_change_log (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        change_date
    )
    SELECT 
        p_employee_id,
        (SELECT salary FROM employees WHERE employee_id = p_employee_id),
        p_new_salary,
        CURRENT_USER,
        CURRENT_TIMESTAMP;
    
    -- Сообщение об успешном выполнении
    RAISE NOTICE 'Зарплата сотрудника ID % успешно обновлена до %', 
        p_employee_id, p_new_salary;
END;
$$;
Разбор структуры процедуры:

  1. CREATE OR REPLACE PROCEDURE — создание новой или замена существующей процедуры

  2. update_employee_salary — имя процедуры

  3. Параметры:

    • p_employee_id INTEGER — идентификатор сотрудника

    • p_new_salary DECIMAL(10, 2) — новая зарплата

  4. LANGUAGE plpgsql — язык программирования процедуры

  5. Логика процедуры:

    • Проверка существования активного сотрудника

    • Валидация новой зарплаты

    • Обновление данных

    • Логирование изменений

    • Информирование о результате

Шаг 2: Выполнение процедуры
Подготовительные действия

Перед вызовом процедуры убедимся в наличии необходимых структур данных:

sql
-- Таблица для логирования изменений зарплаты
CREATE TABLE IF NOT EXISTS salary_change_log (
    log_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    changed_by VARCHAR(100),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Просмотр текущих данных
SELECT employee_id, first_name, last_name, salary, is_active
FROM employees
WHERE is_active = TRUE
ORDER BY employee_id;
Пример данных:
text
 employee_id | first_name | last_name | salary | is_active
-------------|------------|-----------|--------|-----------
     1       | Иван       | Иванов    | 50000  | true
     2       | Мария      | Петрова   | 55000  | true
     3       | Алексей    | Сидоров   | 60000  | true
Вызов процедуры для обновления зарплаты:
sql
-- Обновление зарплаты сотрудника с ID 1
CALL update_employee_salary(1, 65000);

-- Проверка результата
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 1;

Результат:

text
 employee_id | first_name | last_name | salary
-------------|------------|-----------|--------
     1       | Иван       | Иванов    | 65000
Обработка ошибок:
sql
-- Попытка обновления несуществующего сотрудника
CALL update_employee_salary(999, 70000);

Ожидаемая ошибка:

text
ERROR: Активный сотрудник с ID 999 не найден
sql
-- Попытка установить отрицательную зарплату
CALL update_employee_salary(1, -1000);

Ожидаемая ошибка:

text
ERROR: Зарплата должна быть положительным числом
Управление транзакциями в процедурах
Автоматическое управление транзакциями

Процедуры в PostgreSQL автоматически обрабатывают транзакции:

sql
CREATE OR REPLACE PROCEDURE complex_employee_update(
    p_employee_id INTEGER,
    p_new_salary DECIMAL,
    p_new_position VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Обновление зарплаты
    UPDATE employees 
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;

    -- Обновление должности
    UPDATE employee_positions
    SET position_title = p_new_position
    WHERE employee_id = p_employee_id
      AND is_current = TRUE;

    -- Логирование обоих изменений
    INSERT INTO audit_log (action, details, performed_by)
    VALUES ('COMPLEX_UPDATE', 
            format('Обновлен сотрудник %s: зарплата=%s, должность=%s', 
                   p_employee_id, p_new_salary, p_new_position),
            CURRENT_USER);
    
    -- Если произойдет ошибка на любом этапе, все изменения откатятся
END;
$$;
Явное управление транзакциями

Для сложных сценариев можно использовать явное управление:

sql
CREATE OR REPLACE PROCEDURE transactional_update()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Начало транзакции
    BEGIN
        -- Операция 1
        UPDATE table1 SET ...;
        
        -- Операция 2
        INSERT INTO table2 VALUES (...);
        
        -- Фиксация транзакции
        COMMIT;
        
    EXCEPTION WHEN OTHERS THEN
        -- Откат при ошибке
        ROLLBACK;
        RAISE;
    END;
END;
$$;
Расширенные возможности процедур
Процедура с несколькими параметрами и сложной логикой
sql
CREATE OR REPLACE PROCEDURE promote_employee(
    p_employee_id INTEGER,
    p_new_position_id INTEGER,
    p_salary_increase_percent DECIMAL(5, 2),
    p_effective_date DATE DEFAULT CURRENT_DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_salary DECIMAL(10, 2);
    v_new_salary DECIMAL(10, 2);
    v_department_id INTEGER;
BEGIN
    -- Получение текущих данных сотрудника
    SELECT salary, department_id 
    INTO v_current_salary, v_department_id
    FROM employees 
    WHERE employee_id = p_employee_id 
      AND is_active = TRUE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Сотрудник с ID % не найден или неактивен', p_employee_id;
    END IF;

    -- Проверка доступности должности в отделе
    IF NOT EXISTS (
        SELECT 1 
        FROM positions 
        WHERE position_id = p_new_position_id 
          AND department_id = v_department_id
    ) THEN
        RAISE EXCEPTION 'Должность % не доступна в отделе сотрудника', p_new_position_id;
    END IF;

    -- Расчет новой зарплаты
    v_new_salary := v_current_salary * (1 + p_salary_increase_percent / 100);

    -- Проверка лимитов зарплаты для должности
    IF v_new_salary > (
        SELECT max_salary 
        FROM positions 
        WHERE position_id = p_new_position_id
    ) THEN
        RAISE EXCEPTION 'Новая зарплата превышает максимальную для этой должности';
    END IF;

    -- Обновление данных сотрудника
    UPDATE employees 
    SET salary = v_new_salary
    WHERE employee_id = p_employee_id;

    -- Обновление истории должностей
    UPDATE position_history 
    SET date_finish = p_effective_date - INTERVAL '1 day'
    WHERE employee_id = p_employee_id 
      AND date_finish IS NULL;

    INSERT INTO position_history (
        employee_id,
        position_id,
        date_start,
        salary_at_start
    ) VALUES (
        p_employee_id,
        p_new_position_id,
        p_effective_date,
        v_new_salary
    );

    -- Уведомление об успешном выполнении
    RAISE NOTICE 'Сотрудник % повышен. Новая зарплата: %', 
        p_employee_id, v_new_salary;
END;
$$;
Процедура с циклом и условной логикой
sql
CREATE OR REPLACE PROCEDURE annual_salary_review(
    p_review_year INTEGER DEFAULT EXTRACT(YEAR FROM CURRENT_DATE)
)
LANGUAGE plpgsql
AS $$
DECLARE
    emp_record RECORD;
    v_increase_percent DECIMAL(5, 2);
    v_new_salary DECIMAL(10, 2);
BEGIN
    -- Проверка корректности года
    IF p_review_year > EXTRACT(YEAR FROM CURRENT_DATE) THEN
        RAISE EXCEPTION 'Год пересмотра не может быть в будущем';
    END IF;

    -- Цикл по всем активным сотрудникам
    FOR emp_record IN 
        SELECT employee_id, salary, performance_score, years_of_service
        FROM employees 
        WHERE is_active = TRUE
        ORDER BY employee_id
    LOOP
        -- Определение процента повышения на основе показателей
        v_increase_percent := CASE
            WHEN emp_record.performance_score >= 90 THEN 10.0
            WHEN emp_record.performance_score >= 75 THEN 7.5
            WHEN emp_record.performance_score >= 60 THEN 5.0
            ELSE 3.0
        END;

        -- Дополнительный бонус за стаж
        IF emp_record.years_of_service >= 5 THEN
            v_increase_percent := v_increase_percent + 2.0;
        END IF;

        -- Расчет новой зарплаты
        v_new_salary := emp_record.salary * (1 + v_increase_percent / 100);

        -- Обновление зарплаты
        UPDATE employees 
        SET salary = v_new_salary
        WHERE employee_id = emp_record.employee_id;

        -- Логирование изменения
        INSERT INTO salary_review_log (
            employee_id,
            review_year,
            old_salary,
            new_salary,
            increase_percent,
            performance_score
        ) VALUES (
            emp_record.employee_id,
            p_review_year,
            emp_record.salary,
            v_new_salary,
            v_increase_percent,
            emp_record.performance_score
        );

        RAISE NOTICE 'Обработан сотрудник %: повышение на %%, новая зарплата %',
            emp_record.employee_id, v_increase_percent, v_new_salary;
    END LOOP;

    RAISE NOTICE 'Ежегодный пересмотр зарплат за % год завершен', p_review_year;
END;
$$;
Управление процедурами в pgAdmin
Создание процедуры через интерфейс

  1. Откройте pgAdmin и подключитесь к базе данных

  2. В дереве объектов разверните:

    • База данных → Schemas → public → Procedures

  3. Создание новой процедуры:

    • Правой кнопкой на Procedures → Create → Procedure

    • Заполните параметры в диалоговом окне

    • Напишите код в редакторе

    • Сохраните процедуру

Просмотр и редактирование существующих процедур
sql
-- Просмотр всех процедур в базе данных
SELECT 
    proname AS procedure_name,
    prosrc AS procedure_code,
    proowner::regrole AS owner
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
  AND prokind = 'p'
ORDER BY proname;

-- Просмотр зависимостей процедуры
SELECT 
    refobjid::regprocedure AS referenced_procedure,
    objid::regprocedure AS dependent_object
FROM pg_depend
WHERE refobjid = 'update_employee_salary'::regprocedure;
Практические примеры использования
Пример 1: Процедура для онбординга нового сотрудника
sql
CREATE OR REPLACE PROCEDURE onboard_new_employee(
    p_first_name VARCHAR,
    p_last_name VARCHAR,
    p_email VARCHAR,
    p_position_id INTEGER,
    p_start_salary DECIMAL,
    p_hire_date DATE DEFAULT CURRENT_DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_employee_id INTEGER;
    v_department_id INTEGER;
BEGIN
    -- Проверка уникальности email
    IF EXISTS (SELECT 1 FROM employees WHERE email = p_email) THEN
        RAISE EXCEPTION 'Сотрудник с email % уже существует', p_email;
    END IF;

    -- Получение отдела по должности
    SELECT department_id INTO v_department_id
    FROM positions WHERE position_id = p_position_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Должность с ID % не найдена', p_position_id;
    END IF;

    -- Создание записи сотрудника
    INSERT INTO employees (
        first_name,
        last_name,
        email,
        hire_date,
        salary,
        department_id,
        is_active
    ) VALUES (
        p_first_name,
        p_last_name,
        p_email,
        p_hire_date,
        p_start_salary,
        v_department_id,
        TRUE
    )
    RETURNING employee_id INTO v_employee_id;

    -- Запись в историю должностей
    INSERT INTO position_history (
        employee_id,
        position_id,
        date_start,
        salary_at_start
    ) VALUES (
        v_employee_id,
        p_position_id,
        p_hire_date,
        p_start_salary
    );

    -- Создание учетной записи
    INSERT INTO system_accounts (
        employee_id,
        username,
        account_status
    ) VALUES (
        v_employee_id,
        LOWER(SPLIT_PART(p_email, '@', 1)),
        'ACTIVE'
    );

    -- Логирование
    INSERT INTO onboarding_log (
        employee_id,
        onboard_date,
        onboarded_by
    ) VALUES (
        v_employee_id,
        CURRENT_TIMESTAMP,
        CURRENT_USER
    );

    RAISE NOTICE 'Новый сотрудник добавлен: ID %, % %', 
        v_employee_id, p_first_name, p_last_name;
END;
$$;
Пример 2: Процедура для увольнения сотрудника
sql
CREATE OR REPLACE PROCEDURE terminate_employee(
    p_employee_id INTEGER,
    p_termination_date DATE DEFAULT CURRENT_DATE,
    p_reason VARCHAR DEFAULT 'По собственному желанию'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_position_id INTEGER;
BEGIN
    -- Проверка существования активного сотрудника
    IF NOT EXISTS (
        SELECT 1 
        FROM employees 
        WHERE employee_id = p_employee_id 
          AND is_active = TRUE
    ) THEN
        RAISE EXCEPTION 'Сотрудник с ID % не найден или уже уволен', p_employee_id;
    END IF;

    -- Получение текущей должности
    SELECT position_id INTO v_current_position_id
    FROM position_history
    WHERE employee_id = p_employee_id
      AND date_finish IS NULL;

    -- Обновление истории должностей
    UPDATE position_history 
    SET date_finish = p_termination_date
    WHERE employee_id = p_employee_id 
      AND date_finish IS NULL;

    -- Деактивация сотрудника
    UPDATE employees 
    SET 
        is_active = FALSE,
        termination_date = p_termination_date,
        termination_reason = p_reason,
        updated_at = CURRENT_TIMESTAMP
    WHERE employee_id = p_employee_id;

    -- Деактивация системных учетных записей
    UPDATE system_accounts 
    SET account_status = 'INACTIVE',
        deactivated_on = CURRENT_TIMESTAMP
    WHERE employee_id = p_employee_id;

    -- Логирование
    INSERT INTO termination_log (
        employee_id,
        termination_date,
        termination_reason,
        terminated_by,
        last_position_id
    ) VALUES (
        p_employee_id,
        p_termination_date,
        p_reason,
        CURRENT_USER,
        v_current_position_id
    );

    RAISE NOTICE 'Сотрудник ID % уволен. Дата: %, Причина: %', 
        p_employee_id, p_termination_date, p_reason;
END;
$$;
Безопасность и управление доступом
Настройка прав доступа к процедурам
sql
-- Предоставление прав на выполнение процедуры
GRANT EXECUTE ON PROCEDURE update_employee_salary TO hr_manager;

-- Создание роли с ограниченными правами
CREATE ROLE salary_updater;

-- Предоставление прав только на определенные действия
GRANT EXECUTE ON PROCEDURE update_employee_salary(INTEGER, DECIMAL) 
TO salary_updater;

-- Отзыв прав
REVOKE EXECUTE ON PROCEDURE update_employee_salary FROM public;
Процедура с проверкой прав доступа
sql
CREATE OR REPLACE PROCEDURE secure_salary_update(
    p_employee_id INTEGER,
    p_new_salary DECIMAL
)
LANGUAGE plpgsql
SECURITY DEFINER  -- Выполняется с правами владельца
AS $$
DECLARE
    v_current_user VARCHAR := CURRENT_USER;
    v_user_role VARCHAR;
    v_max_allowed_increase DECIMAL(5, 2);
BEGIN
    -- Получение роли пользователя
    SELECT role_name INTO v_user_role
    FROM user_roles 
    WHERE username = v_current_user;

    -- Определение лимитов в зависимости от роли
    v_max_allowed_increase := CASE v_user_role
        WHEN 'HR_DIRECTOR' THEN 50.0
        WHEN 'DEPARTMENT_MANAGER' THEN 20.0
        WHEN 'TEAM_LEAD' THEN 10.0
        ELSE 5.0
    END;

    -- Проверка прав на изменение конкретного сотрудника
    IF v_user_role != 'HR_DIRECTOR' THEN
        IF NOT EXISTS (
            SELECT 1 
            FROM employee_managers 
            WHERE employee_id = p_employee_id 
              AND manager_username = v_current_user
        ) THEN
            RAISE EXCEPTION 'У вас нет прав для изменения зарплаты этого сотрудника';
        END IF;
    END IF;

    -- Проверка размера повышения
    IF (p_new_salary - (SELECT salary FROM employees WHERE employee_id = p_employee_id)) 
       / (SELECT salary FROM employees WHERE employee_id = p_employee_id) * 100 
       > v_max_allowed_increase 
    THEN
        RAISE EXCEPTION 'Превышен максимально допустимый процент повышения для вашей роли';
    END IF;

    -- Вызов основной процедуры
    CALL update_employee_salary(p_employee_id, p_new_salary);
END;
$$;
Тестирование и отладка процедур
Создание тестовых сценариев
sql
-- Тестовая процедура для проверки логики
CREATE OR REPLACE PROCEDURE test_salary_update()
LANGUAGE plpgsql
AS $$
DECLARE
    v_test_employee_id INTEGER;
    v_initial_salary DECIMAL;
BEGIN
    -- Создание тестового сотрудника
    INSERT INTO employees (
        first_name, last_name, email, hire_date, salary, is_active
    ) VALUES (
        'Тестовый', 'Сотрудник', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.', CURRENT_DATE, 50000, TRUE
    )
    RETURNING employee_id, salary INTO v_test_employee_id, v_initial_salary;

    RAISE NOTICE 'Создан тестовый сотрудник ID: %, начальная зарплата: %', 
        v_test_employee_id, v_initial_salary;

    -- Тест 1: Успешное обновление
    BEGIN
        CALL update_employee_salary(v_test_employee_id, 55000);
        RAISE NOTICE 'Тест 1 пройден: зарплата успешно обновлена';
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Тест 1 не пройден: %', SQLERRM;
    END;

    -- Тест 2: Отрицательная зарплата
    BEGIN
        CALL update_employee_salary(v_test_employee_id, -1000);
        RAISE NOTICE 'Тест 2 не пройден: ожидалась ошибка';
    EXCEPTION 
        WHEN OTHERS THEN
            RAISE NOTICE 'Тест 2 пройден: корректно обработана ошибка (%)', SQLERRM;
    END;

    -- Очистка тестовых данных
    DELETE FROM employees WHERE employee_id = v_test_employee_id;
    DELETE FROM salary_change_log WHERE employee_id = v_test_employee_id;

    RAISE NOTICE 'Тестирование завершено';
END;
$$;

-- Запуск тестов
CALL test_salary_update();
Мониторинг выполнения процедур
sql
-- Таблица для мониторинга
CREATE TABLE procedure_execution_log (
    log_id SERIAL PRIMARY KEY,
    procedure_name VARCHAR(100),
    parameters JSONB,
    executed_by VARCHAR(100),
    execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    execution_duration INTERVAL,
    status VARCHAR(20),
    error_message TEXT
);

-- Процедура с логированием
CREATE OR REPLACE PROCEDURE logged_salary_update(
    p_employee_id INTEGER,
    p_new_salary DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_start_time TIMESTAMP := clock_timestamp();
    v_end_time TIMESTAMP;
    v_status VARCHAR := 'SUCCESS';
    v_error_message TEXT;
BEGIN
    INSERT INTO procedure_execution_log (
        procedure_name,
        parameters,
        executed_by,
        status
    ) VALUES (
        'logged_salary_update',
        jsonb_build_object(
            'employee_id', p_employee_id,
            'new_salary', p_new_salary
        ),
        CURRENT_USER,
        'STARTED'
    );

    BEGIN
        CALL update_employee_salary(p_employee_id, p_new_salary);
    EXCEPTION WHEN OTHERS THEN
        v_status := 'ERROR';
        v_error_message := SQLERRM;
        RAISE;
    END;

    v_end_time := clock_timestamp();

    UPDATE procedure_execution_log 
    SET 
        execution_duration = v_end_time - v_start_time,
        status = v_status,
        error_message = v_error_message
    WHERE log_id = (SELECT MAX(log_id) FROM procedure_execution_log);

END;
$$;
Заключение

Хранимые процедуры в PostgreSQL предоставляют надежный инструмент для реализации бизнес-логики на уровне базы данных. Их основные преимущества:

  1. Инкапсуляция сложной логики — скрытие деталей реализации за простым интерфейсом

  2. Гарантия целостности данных — автоматическое управление транзакциями

  3. Повышение безопасности — контроль доступа через права выполнения

  4. Централизация логики — единое место для бизнес-правил

  5. Улучшение производительности — уменьшение сетевого трафика между приложением и БД

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

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

Picture background

Статья подготовлена по материалам обучающего видео для вашего удобства.

Поиск по материалам сайта
Видео по созданию процедур

PostgreSQL: Создание процедур (Procedures)

БОЛЬШЕ СМОТРЕТЬ ТУТ

 Picture background