Какие объекты баз данных представляют собой сохраненный код на SQL

Сохраненные объекты SQL в базах данных

В SQL-совместимых базах данных (таких как MS SQL Server, PostgreSQL, Oracle, MySQL) существует несколько ключевых типов объектов, которые представляют собой сохраненный, предварительно скомпилированный код SQL. Эти объекты хранятся на сервере базы данных, могут быть вызваны повторно и часто обладают повышенной производительностью и безопасностью по сравнению с выполнением динамического SQL.

Основные объекты сохраненного кода

1. Процедуры (Stored Procedures)

Это наиболее известный объект. Процедура представляет собой набор SQL-инструкций, логически объединенных для выполнения определенной задачи. Она может принимать входные параметры, возвращать выходные параметры или множественные наборы результатов.

-- Пример процедуры в PostgreSQL
CREATE OR REPLACE PROCEDURE update_product_price(
    product_id INT,
    new_price DECIMAL
)
LANGUAGE SQL
AS $$
    UPDATE products
    SET price = new_price
    WHERE id = product_id;
$$;

Ключевые особенности:

• Повторное использование и централизованное управление бизнес-логикой.
• Снижение сетевого трафика: клиент вызывает одну процедуру вместо отправки множества запросов.
• Улучшенная безопасность: можно предоставлять права на выполнение процедуры без прямого доступа к таблицам.
• Предварительная компиляция (в некоторых системах) для оптимизации выполнения.

2. Функции (Functions)

Функции похожи на процедуры, но обычно предназначены для возврата единственного значения (скалярная функция) или таблицы (табличная функция). Они часто используются в выражениях SELECT.

-- Пример скалярной функции в MS SQL Server
CREATE FUNCTION calculate_discount(
    @price DECIMAL,
    @discount_rate DECIMAL
)
RETURNS DECIMAL
AS
BEGIN
    RETURN @price * (1 - @discount_rate);
END;

Отличия от процедур (в некоторых системах):

• Функции могут использоваться в SQL выражениях (SELECT calculate_discount(price, 0.1) FROM products).
• Функции обычно не могут изменять данные базы (не содержат DML операторов типа INSERT/UPDATE) или имеют ограничения на это.
• Не поддерживают вывод результатов через параметры OUT.

3. Триггеры (Triggers)

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

-- Пример триггера аудита в PostgreSQL
CREATE OR REPLACE FUNCTION audit_product_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO product_audit_log(product_id, old_price, new_price, change_time)
    VALUES (OLD.id, OLD.price, NEW.price, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_product_update
    AFTER UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION audit_product_change();

Триггеры бывают:

• DML триггеры: для операций с данными.
• DDL триггеры: реагируют на изменения структуры базы (создание таблиц, etc.).
• LOGON триггеры: запускаются при подключении пользователя.

4. Представления (Views)

Представления — это виртуальные таблицы, определяемые запросом SELECT. Они не хранят данные физически, но хранят SQL-код запроса, который выполняется при обращении к представлению.

-- Пример представления
CREATE VIEW active_orders_summary AS
SELECT 
    o.id,
    c.name AS customer_name,
    SUM(p.price) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items i ON o.id = i.order_id
JOIN products p ON i.product_id = p.id
WHERE o.status = 'active'
GROUP BY o.id, c.name;

Materialized Views (в PostgreSQL, Oracle) — это разновидность, которая физически хранит результат запроса, периодически обновляясь. Они хранят как код, так и данные.

Преимущества использования сохраненного SQL кода

• Производительность: код компилируется и оптимизируется сервером, план выполнения может быть сохранен.
• Снижение дублирования: бизнес-логика централизована, изменения вносятся в одном месте.
• Сетевая эффективность: передается только вызов объекта, не весь код.
• Безопасность и контроль доступа: права можно назначать на уровне объектов, скрывая структуру таблиц.
• Абстракция данных: представления и функции скрывают сложность схемы данных от конечных пользователей.

Различия в диалектах SQL

• MS SQL Server: процедуры, функции, триггеры, представления, также хранимые запросы в виде планов выполнения.
• PostgreSQL: процедуры и функции (с поддержкой множества языков, например, PL/pgSQL), триггеры, представления и материализованные представления.
• Oracle: аналогичный набор, с сильным акцентом на PL/SQL для процедур и функций.
• MySQL: процедуры, функции, триггеры, представления, но с некоторыми ограничениями (например, триггеры не могут вызывать процедуры).

В контексте тестирования (QA), понимание этих объектов критически важно для тестирования хранимой логики, проверки целостности данных после выполнения процедур, тестирования триггеров на корректность реакций и обеспечения того, что представления возвращают корректные данные при изменениях в базовых таблицах. Часто требуются отдельные стратегии тестирования для каждого типа объекта, включая модульное тестирование процедур, проверку граничных значений для функций и тестирование интеграции для триггеров.