Реальные кейсы применения PL/SQL

Кейсы применения PL/SQL

PL/SQL (Procedural Language for SQL) — это процедурное расширение языка SQL, созданное компанией Oracle.
Его ключевая задача — обеспечить возможность выполнять бизнес-логику прямо в базе данных, не перенося её на уровень приложений.

SQL отвечает за работу с данными (выборка, вставка, обновление), а PL/SQL добавляет переменные, условия, циклы, процедуры, функции, триггеры, обработку ошибок.
В итоге Oracle Database превращается не просто в хранилище данных, а в полноценную вычислительную платформу.

В этой статье собраны реальные кейсы применения PL/SQL в бизнесе: от расчёта зарплат до контроля качества данных и автоматизации e-commerce.

1. Автоматизация расчёта зарплаты

В HR-системах часто требуется рассчитывать зарплату с учётом бонусов и налогов.


CREATE OR REPLACE PROCEDURE calc_salary (p_emp_id IN NUMBER) IS
    v_base_salary employees.salary%TYPE;
    v_bonus   NUMBER := 0.1;  -- бонус 10%
    v_tax     NUMBER := 0.13; -- налог 13%
    v_result  NUMBER;
BEGIN
    SELECT salary INTO v_base_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    v_result := (v_base_salary + v_base_salary * v_bonus) * (1 - v_tax);

    UPDATE employees
    SET net_salary = v_result
    WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('Зарплата рассчитана для сотрудника ' || p_emp_id);
END;

исключает ошибки ручных расчётов;
централизует бизнес-логику в базе;
легко адаптируется при изменении налогов или бонусов.

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

Для аудита важно фиксировать все изменения данных.


CREATE OR REPLACE TRIGGER log_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_audit (emp_id, old_salary, new_salary, changed_at, changed_by)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE, USER);
END;

прозрачность изменений;
контроль действий пользователей;
удобство для бухгалтерий, аудиторов, compliance-служб.

3. Валидация данных

Ограничение на уровне БД предотвращает ошибки ввода.


CREATE OR REPLACE TRIGGER validate_employee
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Зарплата не может быть отрицательной'); END IF; IF :NEW.hire_date > SYSDATE THEN
        RAISE_APPLICATION_ERROR(-20002, 'Дата найма не может быть в будущем');
    END IF;
END;

защита целостности данных;
снижение числа «битых» записей;
универсальное правило для всех приложений.

4. Массовая обработка заказов (e-commerce)

Магазины обрабатывают тысячи заказов ежедневно.


CREATE OR REPLACE PROCEDURE process_orders IS
BEGIN
    FOR rec IN (SELECT order_id FROM orders WHERE status = 'NEW') LOOP
        UPDATE orders
        SET status = 'PROCESSED',
            processed_at = SYSDATE
        WHERE order_id = rec.order_id;

        DBMS_OUTPUT.PUT_LINE('Заказ ' || rec.order_id || ' обработан.');
    END LOOP;
    COMMIT;
END;

ускорение обработки;
централизованная логика;
снижение нагрузки на приложение.

5. Автоматическая генерация отчётов

Финансовые и страховые компании ежедневно формируют отчёты.


CREATE OR REPLACE PROCEDURE daily_report IS
    v_total NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_total
    FROM transactions
    WHERE trx_date = TRUNC(SYSDATE);

    INSERT INTO reports (report_date, trx_count, created_at)
    VALUES (TRUNC(SYSDATE), v_total, SYSDATE);

    DBMS_OUTPUT.PUT_LINE('Отчёт сформирован. Транзакций: ' || v_total);
END;

автоматизация рутинных задач;
возможность запуска по расписанию (Oracle Scheduler);
контроль активности бизнеса.

6. Управление безопасностью

Запрет на изменение собственных данных.


CREATE OR REPLACE TRIGGER salary_protection
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF USER = 'MANAGER' AND :OLD.employee_id = :NEW.employee_id THEN
        RAISE_APPLICATION_ERROR(-20003, 'Нельзя изменять собственную зарплату');
    END IF;
END;

защита от злоупотреблений;
прозрачный контроль прав;
часть политики безопасности.

7. Кеширование справочников

Часто используемые данные можно хранить в пакетах.


CREATE OR REPLACE PACKAGE country_cache AS
    TYPE t_country IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
    g_countries t_country;
    PROCEDURE init_cache;
END country_cache;
/

CREATE OR REPLACE PACKAGE BODY country_cache AS
    PROCEDURE init_cache IS
    BEGIN
        SELECT country_name BULK COLLECT INTO g_countries
        FROM countries;
    END;
END country_cache;

ускорение работы приложений;
уменьшение нагрузки на таблицы;
централизованный справочник.

8. Обработка ошибок и бизнес-исключений

Пример обработки бизнес-логики.


BEGIN
    UPDATE accounts
    SET balance = balance - 1000
    WHERE account_id = 123;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20004, 'Счёт не найден');
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Ошибка: ' || SQLERRM);
END;

безопасная работа с транзакциями;
предсказуемое поведение при сбоях;
контроль бизнес-исключений.

Заключение

PL/SQL — это не просто дополнение к SQL, а мощный инструмент, который позволяет:

централизовать бизнес-логику в базе данных;
снизить нагрузку на сеть и приложения;
обеспечить безопасность и контроль качества данных;
автоматизировать процессы в любой сфере — от HR до e-commerce и финансов.

На практике PL/SQL используется для:

расчётов (зарплата, налоги, бонусы);
валидации и аудита данных;
автоматической генерации отчётов;
обработки больших массивов заказов и транзакций;
обеспечения безопасности.

Подробнее см. в официальной документации:

Oracle PL/SQL Language Reference

Следующая статья:

Продолжение следует …