Перейти к содержанию

БД:Теория:Глава 5: различия между версиями

Материал из Мадока ВТ Вики
Следующая правка →
ВизуальныйВики-текст
Импорт
 
Импорт
Метки: очистка отменено
Строка 1: Строка 1:
В этой главе мы углубимся в команды для изменения данных (DML), рассмотрим мощный инструмент SQL — представления (Views), научимся создавать собственные функции и процедуры с использованием процедурного расширения PL/pgSQL, разберемся с триггерами для реализации сложной логики и автоматизации, а также подробно рассмотрим механизм транзакций для обеспечения целостности данных. Эта глава основана на материалах Лекции 5: “PL/pgSQL” и частично затрагивает DML из Лекции 4/5.


<span id="часть-1-манипулирование-данными-dml"></span>
=== Часть 1: Манипулирование Данными (DML) ===
DML (Data Manipulation Language) — это часть SQL, отвечающая за добавление, изменение и удаление данных ''внутри'' таблиц.
<ol style="list-style-type: decimal;">
<li><p>'''<code>INSERT</code>''': Добавляет одну или несколько новых строк в таблицу.</p>
<ul>
<li><p>'''Синтаксис 1 (Указание столбцов):'''</p>
<syntaxhighlight lang="sql">INSERT INTO имя_таблицы (столбец1, столбец2, ...)
VALUES (значение1, значение2, ...);</syntaxhighlight>
<p>''Порядок значений должен соответствовать порядку указанных столбцов.''</p></li>
<li><p>'''Синтаксис 2 (Без указания столбцов):'''</p>
<syntaxhighlight lang="sql">INSERT INTO имя_таблицы VALUES (значение1, значение2, ...);</syntaxhighlight>
<p>''Значения должны быть перечислены в том же порядке, в каком столбцы определены в таблице. Менее надежный способ, т.к. зависит от порядка столбцов.''</p></li>
<li><p>'''Синтаксис 3 (Вставка из <code>SELECT</code>):'''</p>
<syntaxhighlight lang="sql">INSERT INTO имя_таблицы (столбец1, столбец2, ...)
SELECT выражение1, выражение2, ...
FROM другая_таблица
WHERE условие;</syntaxhighlight>
<p>''Позволяет вставить в таблицу результат другого запроса. Количество и типы столбцов в <code>INSERT</code> и <code>SELECT</code> должны совпадать.''</p></li></ul>
<p>'''Пример (из слайдов):'''</p>
<syntaxhighlight lang="sql">-- Пример 1 и 2: Добавление студента
INSERT INTO student VALUES (123, 'Vasya', 345);
INSERT INTO student (StudID, GroupID, Name) VALUES (123, 345, 'Vasya');
-- Пример 3: Добавление студентов из временной таблицы по условию
INSERT INTO student (StudID, Name, GroupID) -- Указываем порядок для INSERT
SELECT ID, Name, 345 -- Указываем порядок в SELECT
FROM tmp_person
WHERE Exam > 70;</syntaxhighlight></li>
<li><p>'''<code>UPDATE</code>''': Изменяет существующие строки в таблице.</p>
<ul>
<li><p>'''Синтаксис:'''</p>
<syntaxhighlight lang="sql">UPDATE имя_таблицы
SET столбец1 = новое_значение1,
    столбец2 = новое_значение2,
    ...
[ WHERE условие ]; -- Если WHERE отсутствует, обновятся ВСЕ строки!</syntaxhighlight>
<p>''Условие <code>WHERE</code> определяет, какие строки нужно обновить.''</p></li></ul>
<p>'''Пример (из слайдов):'''</p>
<syntaxhighlight lang="sql">-- Установить всем студентам GroupID = 578
UPDATE student SET GroupID = 578;
-- Установить GroupID = 34 для студента с именем 'Ivan'
UPDATE student SET GroupID = 34 WHERE Name = 'Ivan';</syntaxhighlight></li>
<li><p>'''<code>DELETE</code>''': Удаляет существующие строки из таблицы.</p>
<ul>
<li><p>'''Синтаксис:'''</p>
<syntaxhighlight lang="sql">DELETE FROM имя_таблицы
[ WHERE условие ]; -- Если WHERE отсутствует, удалятся ВСЕ строки!</syntaxhighlight>
<p>''Условие <code>WHERE</code> определяет, какие строки нужно удалить.''</p></li></ul>
<p>'''Пример (из слайдов):'''</p>
<syntaxhighlight lang="sql">-- Удалить студентов с GroupID = 34
DELETE FROM student WHERE GroupID = 34;
-- Удалить ВСЕХ студентов
DELETE FROM student;</syntaxhighlight></li>
<li><p>'''<code>TRUNCATE</code>''': Быстро удаляет '''все''' строки из таблицы (или нескольких таблиц).</p>
<ul>
<li><p>'''Синтаксис:'''</p>
<syntaxhighlight lang="sql">TRUNCATE [TABLE] имя_таблицы1 [, имя_таблицы2 ...];</syntaxhighlight></li>
<li><p>'''Отличия от <code>DELETE</code> без <code>WHERE</code>:'''</p>
<ul>
<li>Обычно '''намного быстрее''' для больших таблиц, так как не сканирует таблицу построчно и не записывает удаление каждой строки в лог транзакций так же подробно.</li>
<li>Не запускает триггеры <code>DELETE</code> на уровне строк (<code>FOR EACH ROW</code>).</li>
<li>Сбрасывает счетчики последовательностей (<code>SERIAL</code> и т.п.), связанных с таблицей (в PostgreSQL, если указано <code>RESTART IDENTITY</code>).</li>
<li>Не возвращает количество удаленных строк.</li></ul>
</li>
<li><p>Используйте <code>TRUNCATE</code>, когда нужно быстро и полностью очистить таблицу.</p></li></ul>
<p>'''Пример (из слайдов):'''</p>
<syntaxhighlight lang="sql">-- Быстро очистить таблицу student
TRUNCATE TABLE student;
-- Быстро очистить несколько таблиц
TRUNCATE TABLE student, groups, exams;</syntaxhighlight></li></ol>
<span id="часть-2-выполнение-скриптов"></span>
=== Часть 2: Выполнение Скриптов ===
Часто SQL-код (особенно DDL для создания схемы или DML для наполнения данными) хранится в файлах (<code>.sql</code>). Такие файлы называются '''скриптами'''. Выполнить скрипт в PostgreSQL можно несколькими способами:
<ol style="list-style-type: decimal;">
<li><p>'''Через утилиту <code>psql</code> при запуске:'''</p>
<syntaxhighlight lang="bash">psql -U имя_пользователя -d имя_базы -f путь/к/файлу/script.sql</syntaxhighlight>
<ul>
<li><code>-U</code>: Имя пользователя PostgreSQL.</li>
<li><code>-d</code>: Имя базы данных, к которой подключаемся.</li>
<li><code>-f</code>: Путь к файлу скрипта.</li></ul>
</li>
<li><p>'''Внутри интерактивной сессии <code>psql</code>:''' Используются мета-команды (начинаются с <code>\</code>):</p>
<syntaxhighlight lang="sql">-- Подключаемся к базе
psql -U имя_пользователя -d имя_базы
-- Выполняем скрипт из файла
\i путь/к/файлу/script.sql
-- или эквивалентная команда
\include путь/к/файлу/script.sql</syntaxhighlight>
<p>''Это удобно для выполнения небольших скриптов или частей кода во время работы с БД.''</p></li></ol>
<span id="часть-3-представления-views"></span>
=== Часть 3: Представления (Views) ===
Мы уже кратко упоминали '''виртуальные таблицы'''. Основной их вид — это '''представления'''.
* '''Представление (View)''' — это сохраненный в базе данных '''именованный SQL-запрос''' (<code>SELECT</code>).
* С представлением можно работать почти так же, как с обычной таблицей (выполнять <code>SELECT</code>, иногда <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>, если представление обновляемое).
* Представление '''не хранит данные''' само по себе (за исключением материализованных представлений). Каждый раз при обращении к представлению СУБД выполняет лежащий в его основе <code>SELECT</code>-запрос к базовым таблицам.
'''Зачем нужны представления?'''
# '''Упрощение сложных запросов:''' Длинный и сложный запрос можно “спрятать” за простым именем представления.
# '''Сокрытие структуры данных:''' Можно предоставить пользователям доступ только к представлению, которое показывает лишь необходимые им столбцы и строки, скрывая сложность или конфиденциальность базовых таблиц.
# '''Логическая независимость данных:''' Если структура базовых таблиц меняется, можно изменить только определение представления, а запросы пользователей, обращающиеся к представлению, останутся прежними.
# '''Обеспечение обратной совместимости:''' При рефакторинге схемы можно создать представления, имитирующие старую структуру.
'''Создание Представления (<code>CREATE VIEW</code>)'''
<syntaxhighlight lang="sql">CREATE [ OR REPLACE ] VIEW имя_представления [ (имя_колонки1, имя_колонки2, ...) ]
AS
SELECT_запрос; -- Запрос, определяющий представление</syntaxhighlight>
* <code>OR REPLACE</code>: Если представление с таким именем уже существует, оно будет заменено.
* <code>[ (имя_колонки1, ...) ]</code>: Необязательный список имен для столбцов представления. Если опущен, используются имена столбцов из <code>SELECT_запрос</code>.
'''Пример (из слайдов):''' Создать представление, показывающее студентов факультета ПИКТ (предполагаем, что это группы, начинающиеся с ‘P3’).
<syntaxhighlight lang="sql">-- Создаем представление
CREATE VIEW PICTStudents AS
SELECT * -- Выбираем все колонки из student
FROM student
WHERE GroupID IN ( -- Выбираем студентов, чья группа в списке групп ПИКТ
    SELECT GroupID
    FROM groups
    WHERE GroupName LIKE 'P3%' -- Находим группы, чье имя начинается с 'P3'
);
-- Используем представление как обычную таблицу
SELECT st_name, GroupID FROM PICTStudents WHERE st_id > 100;</syntaxhighlight>
'''Пример (с переименованием колонок):'''
<syntaxhighlight lang="sql">CREATE VIEW PICTStudents2 (PICTId, pSurname) AS -- Задаем свои имена колонок
SELECT StudentID, Surname -- Выбираем нужные колонки из student
FROM student
WHERE GroupID IN (
    SELECT GroupID FROM groups WHERE GroupName LIKE 'P3%'
);
-- Используем представление с новыми именами
SELECT PICTId FROM PICTStudents2 WHERE pSurname LIKE 'Иван%';</syntaxhighlight>
'''Материализованные Представления (<code>MATERIALIZED VIEW</code>)'''
* В отличие от обычных представлений, материализованное представление '''хранит результат''' своего запроса физически в базе данных (как кэш).
* Обращение к материализованному представлению происходит очень быстро, так как не требует выполнения основного запроса каждый раз.
* '''Проблема:''' Данные в материализованном представлении могут '''устареть''', если данные в базовых таблицах изменились.
* '''Обновление:''' Данные нужно обновлять '''вручную''' (или по расписанию) с помощью команды <code>REFRESH MATERIALIZED VIEW</code>.
'''Создание и Обновление:'''
<syntaxhighlight lang="sql">-- Создаем материализованное представление
CREATE MATERIALIZED VIEW PICTStudents_MV (PICTId, pSurname) AS -- Добавляем MATERIALIZED
SELECT StudentID, Surname
FROM student
WHERE GroupID IN (
    SELECT GroupID FROM groups WHERE GroupName LIKE 'P3%'
);
-- Используем (быстро, читает сохраненные данные)
SELECT * FROM PICTStudents_MV;
-- Обновляем данные (выполняет основной SELECT и перезаписывает сохраненные данные)
REFRESH MATERIALIZED VIEW PICTStudents_MV;</syntaxhighlight>
''Материализованные представления полезны для сложных, ресурсоемких запросов, результаты которых нужны часто, а актуальность данных с точностью до секунды не критична (например, для отчетов).''
<span id="часть-4-введение-в-plpgsql"></span>
=== Часть 4: Введение в PL/pgSQL ===
SQL — декларативный язык. Иногда нам нужно выполнить последовательность действий, использовать циклы, условия, переменные — то есть написать ''процедурный'' код. Для этого в PostgreSQL (и других СУБД) существуют процедурные расширения языка.
* '''PL/pgSQL (Procedural Language / PostgreSQL SQL)''' — стандартный, блочно-структурированный процедурный язык для PostgreSQL. Его синтаксис во многом основан на языке Ada.
'''Зачем нужен PL/pgSQL?'''
* Создание '''пользовательских функций (UDF)''' и '''хранимых процедур'''.
* Реализация сложной бизнес-логики непосредственно в базе данных.
* Создание '''триггеров''' для автоматизации действий при изменении данных.
* Повышение производительности за счет уменьшения обмена данными между приложением и СУБД (логика выполняется на сервере БД).
'''Типы Пользовательских Функций:'''
В PostgreSQL можно создавать функции на разных языках:
# '''SQL-функции:''' Тело функции состоит из одного или нескольких SQL-запросов. Выполняются быстро, но возможности ограничены самим SQL.
# '''PL/pgSQL-функции:''' Тело функции написано на языке PL/pgSQL, позволяет использовать переменные, циклы, условия и т.д. Самый распространенный вариант для сложной логики.
# '''Функции на других языках (C, Python, Perl, Tcl и др.):''' Требуют установки соответствующих расширений (<code>CREATE EXTENSION plpython3u;</code>). Позволяют использовать возможности и библиотеки этих языков внутри БД.
<span id="часть-5-пользовательские-функции-и-процедуры"></span>
=== Часть 5: Пользовательские Функции и Процедуры ===
'''Создание Функции (<code>CREATE FUNCTION</code>)'''
<syntaxhighlight lang="sql">CREATE [ OR REPLACE ] FUNCTION имя_функции ( [ [имя_арг1] тип_арг1, [имя_арг2] тип_арг2, ...] )
RETURNS тип_возвращаемого_значения -- Или RETURNS TABLE(...) для возврата таблицы, или VOID если ничего не возвращает
AS $$ -- Или AS '...' - тело функции в $$ или одинарных кавычках
    -- Тело функции (SQL или PL/pgSQL код)
$$ LANGUAGE язык; -- язык: sql, plpgsql, plpython3u и т.д.</syntaxhighlight>
* <code>OR REPLACE</code>: Заменяет существующую функцию с тем же именем и типами аргументов.
* '''Аргументы:'''
** В старых версиях и в SQL-функциях часто используются позиционные параметры (<code>$1</code>, <code>$2</code>, …).
** В PL/pgSQL и современных SQL-функциях можно (и рекомендуется) использовать именованные аргументы.
* <code>RETURNS</code>: Указывает тип возвращаемого значения. <code>VOID</code> означает, что функция ничего не возвращает (похоже на процедуру, но это все еще функция).
* <code>AS $$ ... $$</code>: Тело функции. Использование <code>$$</code> (долларовое квотирование) предпочтительнее одинарных кавычек (<code>'...'</code>), так как позволяет легко использовать одинарные кавычки внутри тела функции без экранирования (<code>''</code>).
* <code>LANGUAGE</code>: Язык, на котором написано тело функции.
'''Пример SQL-функции (позиционные параметры):'''
<syntaxhighlight lang="sql">-- Функция для изменения группы студента
CREATE FUNCTION updateStudentGroup_v1(int, int) -- Аргументы: stud_id, group_id
RETURNS void -- Ничего не возвращает
AS '
    UPDATE student
    SET GroupID = $2 -- $2 - второй аргумент (group_id)
    WHERE StudID = $1; -- $1 - первый аргумент (stud_id)
' LANGUAGE sql;
-- Вызов функции
SELECT updateStudentGroup_v1(101, 2); -- Обновить группу для студента 101 на группу 2</syntaxhighlight>
'''Пример SQL-функции (именованные параметры, PostgreSQL 9.2+):'''
<syntaxhighlight lang="sql">CREATE FUNCTION updateStudentGroup_v2(
    p_stud_id INT, -- Имя аргумента p_stud_id, тип INT
    p_group_id INT -- Имя аргумента p_group_id, тип INT
)
RETURNS void
AS $$
    UPDATE student
    SET GroupID = p_group_id -- Используем имя параметра
    WHERE StudID = p_stud_id; -- Используем имя параметра
$$ LANGUAGE sql;
-- Вызов функции (можно по именам или по позиции)
SELECT updateStudentGroup_v2(p_stud_id := 102, p_group_id := 1);
SELECT updateStudentGroup_v2(103, 1);</syntaxhighlight>
'''Процедуры (<code>CREATE PROCEDURE</code>, PostgreSQL 11+)'''
Процедуры похожи на функции, возвращающие <code>VOID</code>, но имеют ключевое отличие: внутри процедур '''можно управлять транзакциями''' (<code>COMMIT</code>, <code>ROLLBACK</code>), а внутри функций — нельзя.
<syntaxhighlight lang="sql">CREATE [ OR REPLACE ] PROCEDURE имя_процедуры ( [ [имя_арг1] тип_арг1, ...] )
AS $$
    -- Тело процедуры (обычно PL/pgSQL)
$$ LANGUAGE язык;
-- Вызов процедуры
CALL имя_процедуры(значение1, ...);</syntaxhighlight>
'''Пример:'''
<syntaxhighlight lang="sql">CREATE PROCEDURE transfer_money(sender_acc INT, receiver_acc INT, amount NUMERIC)
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE acc_id = sender_acc;
    UPDATE accounts SET balance = balance + amount WHERE acc_id = receiver_acc;
    -- Здесь можно было бы добавить COMMIT или ROLLBACK, если бы это не было частью бОльшей транзакции
END;
$$ LANGUAGE plpgsql;
-- Вызов
CALL transfer_money(1, 2, 100.00);</syntaxhighlight>
<span id="часть-6-основы-plpgsql"></span>
=== Часть 6: Основы PL/pgSQL ===
PL/pgSQL — '''блочно-структурированный''' язык. Основной элемент — блок кода.
'''Структура блока:'''
<syntaxhighlight lang="sql">[ <<метка_блока>> ] -- Необязательная метка
[ DECLARE
    -- Объявление переменных
    имя_переменной тип_данных [ := начальное_значение ];
    ...
]
BEGIN
    -- Исполняемые операторы (SQL-запросы, присваивания, циклы, условия и т.д.)
    ...
    [ RETURN значение; ] -- Только для функций, возвращающих значение
END [ метка_блока ]; -- Метка конца блока (необязательно)</syntaxhighlight>
* Секция <code>DECLARE</code> необязательна, если переменные не нужны.
* <code>BEGIN</code>/<code>END</code> обязательны.
* Блоки могут быть вложенными.
* Метки используются для разрешения имен переменных во вложенных блоках (обращение к переменной внешнего блока: <code>метка_блока.имя_переменной</code>).
* Присваивание значения: <code>переменная := выражение;</code>.
* Выполнение SQL-запросов: Просто пишете SQL-запрос. Чтобы сохранить результат <code>SELECT</code> в переменную, используется <code>SELECT ... INTO переменная ...</code>.
* Вывод отладочной информации: <code>RAISE NOTICE 'Сообщение: %', переменная;</code> (<code>%</code> - место для подстановки значения переменной).
'''Пример блока (из слайда 33):'''
<syntaxhighlight lang="sql">CREATE FUNCTION somefunc() RETURNS integer AS $$ -- SQL command
<< outerblock >> -- Метка внешнего блока (pl/pgSQL)
DECLARE
    quantity integer := 30; -- Переменная внешнего блока
BEGIN
    -- Создаем вложенный блок
    DECLARE
        quantity integer := 80; -- Переменная внутреннего блока (то же имя!)
    BEGIN
        RAISE NOTICE 'Inner quantity = %', quantity; -- Выведет 80 (переменная внутреннего блока)
        RAISE NOTICE 'Outer quantity = %', outerblock.quantity; -- Выведет 30 (обращение к переменной внешнего блока по метке)
    END; -- Конец вложенного блока
    RAISE NOTICE 'Сейчас quantity = %', quantity; -- Выведет 30 (переменная внешнего блока, т.к. вышли из вложенного)
    RETURN quantity; -- Вернет 30
END;
$$ LANGUAGE plpgsql;
-- Вызов функции
SELECT somefunc();</syntaxhighlight>
'''Вывод в <code>NOTICE</code> при вызове:'''
<pre>NOTICE:  Inner quantity = 80
NOTICE:  Outer quantity = 30
NOTICE:  Сейчас quantity = 30</pre>
'''Результат <code>SELECT</code>:'''
<pre> somefunc
----------
      30
(1 row)</pre>
'''Анонимные блоки (<code>DO</code>)'''
Позволяют выполнить блок PL/pgSQL кода без создания функции или процедуры. Удобно для одноразовых задач или скриптов.
<syntaxhighlight lang="sql">DO $$
[ DECLARE ...]
BEGIN
    -- PL/pgSQL код
END;
$$ LANGUAGE plpgsql; -- Язык можно опустить, если используется plpgsql по умолчанию</syntaxhighlight>
'''Пример (из слайда 34):''' Посчитать и вывести количество студентов.
<syntaxhighlight lang="sql">DO $$
<<studentBlock>>
DECLARE
    studCount integer := 0;
BEGIN
    SELECT COUNT(*)
    INTO studCount -- Сохраняем результат COUNT(*) в переменную studCount
    FROM student;
    RAISE NOTICE 'Students: %', studCount; -- Выводим результат
END studentBlock $$;</syntaxhighlight>
'''Вывод в <code>NOTICE</code>:''' (Зависит от количества студентов в таблице)
<pre>NOTICE:  Students: 15
DO</pre>
<span id="часть-7-триггеры"></span>
=== Часть 7: Триггеры ===
Триггеры — это специальные процедуры, которые '''автоматически''' выполняются (срабатывают) в ответ на определенные события, происходящие с таблицей (обычно это операции DML: <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>).
'''Зачем нужны триггеры?'''
# '''Реализация сложных ограничений целостности:''' Правила, которые сложно или невозможно выразить стандартными <code>CHECK</code>, <code>FOREIGN KEY</code> (например, проверка баланса перед списанием, сложные зависимости между таблицами).
# '''Аудит изменений:''' Автоматическая запись информации о том, кто, когда и какие данные изменил, в отдельную таблицу логов.
# '''Автоматическое обновление связанных данных:''' Например, пересчет итоговых сумм при изменении деталей заказа.
# '''Репликация данных''' (в некоторых случаях).
'''Как работает триггер?'''
# '''Событие:''' Происходит операция DML (<code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>) или DDL (для событийных триггеров) с таблицей, для которой определен триггер.
# '''Срабатывание:''' СУБД проверяет, есть ли триггеры, связанные с этим событием и таблицей.
# '''Выполнение:''' Если триггер найден, выполняется связанная с ним '''триггерная функция'''.
'''Создание триггера в PostgreSQL:'''
Процесс состоит из двух шагов:
<ol style="list-style-type: decimal;">
<li><p>'''Создание триггерной функции:''' Это обычная функция PL/pgSQL (или на другом языке), но со специфическими особенностями:</p>
<ul>
<li>Она '''не принимает аргументов'''.</li>
<li>Она должна возвращать специальный тип '''<code>TRIGGER</code>''' (для DML триггеров) или <code>EVENT_TRIGGER</code> (для DDL триггеров).</li>
<li>Внутри функции доступны специальные переменные (<code>NEW</code>, <code>OLD</code>, <code>TG_OP</code>, <code>TG_WHEN</code> и т.д.), содержащие информацию о событии и изменяемых данных.</li>
<li>Возвращаемое значение функции имеет значение (особенно для <code>BEFORE ROW</code> триггеров):
<ul>
<li>Возврат <code>NEW</code> (или измененной строки <code>NEW</code>): Операция продолжается с этой (возможно измененной) строкой.</li>
<li>Возврат <code>OLD</code> (для <code>UPDATE</code>/<code>DELETE</code>): Операция продолжается со старой строкой (редко используется).</li>
<li>Возврат <code>NULL</code>: '''Операция для данной строки отменяется''', последующие триггеры для этой строки не срабатывают. Позволяет “запретить” изменение.</li>
<li>Для <code>AFTER</code> триггеров возвращаемое значение игнорируется (операция уже произошла), но рекомендуется возвращать <code>NULL</code> или ту же запись (<code>NEW</code> или <code>OLD</code>).</li></ul>
</li></ul>
</li>
<li><p>'''Создание самого триггера:''' Связывает триггерную функцию с конкретной таблицей и событием.</p>
<p>'''Синтаксис <code>CREATE TRIGGER</code>:'''</p>
<syntaxhighlight lang="sql">CREATE TRIGGER имя_триггера
    { BEFORE | AFTER | INSTEAD OF } -- Когда срабатывать
    { event [ OR ... ] } -- На какое событие(я) (INSERT, UPDATE, DELETE, TRUNCATE)
    ON имя_таблицы
    [ FOR [ EACH ] { ROW | STATEMENT } ] -- Уровень срабатывания
    [ WHEN ( условие ) ] -- Дополнительное условие срабатывания
    EXECUTE PROCEDURE имя_триггерной_функции(); -- Какую функцию вызвать</syntaxhighlight>
<ul>
<li>'''<code>BEFORE | AFTER | INSTEAD OF</code>''':
<ul>
<li><code>BEFORE</code>: Функция выполняется ''перед'' выполнением операции DML и перед проверкой ограничений. Позволяет изменить данные (<code>NEW</code>) или отменить операцию (вернув <code>NULL</code>).</li>
<li><code>AFTER</code>: Функция выполняется ''после'' выполнения операции DML и проверки ограничений. Не может изменить данные (операция уже прошла) или отменить ее. Используется для аудита, обновления связанных данных.</li>
<li><code>INSTEAD OF</code>: Специальный тип для '''представлений (Views)'''. Функция выполняется ''вместо'' операции DML над представлением, позволяя реализовать логику обновления базовых таблиц.</li></ul>
</li>
<li>'''<code>event</code>''': <code>INSERT</code>, <code>UPDATE [ OF column1, ... ]</code>, <code>DELETE</code>, <code>TRUNCATE</code>. Можно указать несколько через <code>OR</code>. <code>UPDATE OF</code> срабатывает только при изменении указанных колонок.</li>
<li>'''<code>FOR EACH ROW | STATEMENT</code>''':
<ul>
<li><code>ROW</code>: Функция выполняется '''для каждой строки''', затронутой операцией DML. Внутри доступны переменные <code>NEW</code> (для <code>INSERT</code>/<code>UPDATE</code>) и <code>OLD</code> (для <code>UPDATE</code>/<code>DELETE</code>).</li>
<li><code>STATEMENT</code> (По умолчанию): Функция выполняется '''один раз на всю операцию DML''', независимо от количества затронутых строк. Переменные <code>NEW</code> и <code>OLD</code> недоступны.</li></ul>
</li>
<li>'''<code>WHEN (условие)</code>''': Дополнительное условие (использующее значения <code>NEW</code>/<code>OLD</code>), которое проверяется перед вызовом функции (только для <code>ROW</code> триггеров). Если условие ложно, функция не вызывается.</li></ul>
</li></ol>
'''Пример: Аудит таблицы <code>EMPLOYEE</code> (из слайдов)'''
'''Задача:''' При добавлении нового сотрудника в таблицу <code>EMPLOYEE</code> автоматически записывать ID сотрудника и время добавления в таблицу <code>AUDIT</code>.
'''1. Таблицы:'''
<syntaxhighlight lang="sql">CREATE TABLE employee (
    id    INT PRIMARY KEY,
    name  TEXT NOT NULL,
    addr  CHAR(50),
    salary REAL
);
CREATE TABLE audit (
    emp_id    INT NOT NULL,
    entry_date TEXT NOT NULL -- Лучше использовать TIMESTAMPTZ
);</syntaxhighlight>
'''2. Триггерная функция:'''
<syntaxhighlight lang="sql">CREATE OR REPLACE FUNCTION auditfunc()
RETURNS TRIGGER AS $$
BEGIN
    -- Вставляем запись в таблицу аудита
    -- NEW.id - это ID из строки, которая сейчас вставляется в employee
    -- current_timestamp - встроенная функция PostgreSQL, возвращает текущее время
    INSERT INTO audit(emp_id, entry_date) VALUES (NEW.id, current_timestamp);
    -- Возвращаем NEW, чтобы операция INSERT продолжилась успешно
    -- Для AFTER триггера возвращаемое значение игнорируется, но хорошей практикой
    -- является возврат соответствующей строки или NULL.
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;</syntaxhighlight>
'''3. Создание триггера:'''
<syntaxhighlight lang="sql">CREATE TRIGGER employee_audit_insert
    AFTER INSERT ON employee -- Срабатывать ПОСЛЕ вставки
    FOR EACH ROW -- Для каждой вставляемой строки
    EXECUTE PROCEDURE auditfunc(); -- Вызвать нашу функцию</syntaxhighlight>
'''Проверка:'''
<syntaxhighlight lang="sql">INSERT INTO employee (id, name, salary) VALUES (1, 'Иван', 1000);
-- После этой команды в таблице audit появится запись: (1, <текущее время>)
INSERT INTO employee (id, name, salary) VALUES (2, 'Петр', 1500);
-- После этой команды в таблице audit появится вторая запись: (2, <текущее время>)</syntaxhighlight>
'''Событийные триггеры (Event Triggers):'''
* Срабатывают на DDL-команды (<code>CREATE TABLE</code>, <code>ALTER TABLE</code>, <code>DROP TABLE</code> и т.д.).
* Используются для аудита изменений схемы, запрета определенных DDL-операций и т.п.
* Триггерная функция должна возвращать <code>EVENT_TRIGGER</code>.
* Доступны специальные переменные (например, <code>tg_event</code>, <code>tg_tag</code>).
'''Пример (из слайдов):''' Логгирование DDL команд.
<syntaxhighlight lang="sql">-- Функция
CREATE OR REPLACE FUNCTION eventtest()
RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'DDL Event: %, Command Tag: %', tg_event, tg_tag; -- tg_tag содержит текст команды
END;
$$ LANGUAGE plpgsql;
-- Триггер
CREATE EVENT TRIGGER eventtest_trigger
    ON ddl_command_start -- Срабатывать перед началом любой DDL команды
    EXECUTE PROCEDURE eventtest();
-- Проверка
CREATE TABLE some_table (id int);</syntaxhighlight>
'''Вывод в <code>NOTICE</code>:'''
<pre>NOTICE:  DDL Event: ddl_command_start, Command Tag: CREATE TABLE
CREATE TABLE</pre>
'''Удаление триггера:'''
<syntaxhighlight lang="sql">DROP TRIGGER имя_триггера ON имя_таблицы;</syntaxhighlight>
<span id="часть-8-транзакции"></span>
=== Часть 8: Транзакции ===
Транзакция — это '''логическая единица работы''', состоящая из одной или нескольких операций SQL, которая должна быть выполнена '''атомарно'''.
'''Свойства ACID:'''
Транзакции в реляционных СУБД обычно гарантируют свойства ACID:
# '''Atomicity (Атомарность):''' Либо ''все'' операции внутри транзакции успешно выполняются и фиксируются, либо ''ни одна'' из них не оказывает влияния на базу данных (все изменения отменяются). “Все или ничего”.
# '''Consistency (Согласованность):''' Транзакция переводит базу данных из одного ''согласованного'' (целостного) состояния в другое ''согласованное'' состояние. Во время выполнения транзакции целостность может временно нарушаться, но к моменту фиксации она должна быть восстановлена.
# '''Isolation (Изолированность):''' Параллельно выполняющиеся транзакции не должны мешать друг другу. Каждая транзакция должна выполняться так, как будто других транзакций в системе нет. (На практике существуют разные ''уровни изоляции'', которые допускают те или иные аномалии для повышения производительности).
# '''Durability (Долговечность/Устойчивость):''' Если транзакция успешно завершена (зафиксирована), ее результаты должны быть сохранены постоянно и не должны быть потеряны даже в случае сбоев системы (например, отключения питания). Обычно достигается за счет записи изменений в '''журналы транзакций (WAL - Write-Ahead Log)''' перед применением их к основным файлам данных.
'''Управление транзакциями в SQL:'''
* '''<code>BEGIN</code>''' или '''<code>START TRANSACTION</code>''': Начинает новую транзакцию. В PostgreSQL многие команды DDL (как <code>CREATE TABLE</code>) не могут выполняться внутри явного блока <code>BEGIN...COMMIT</code>, они сами по себе являются транзакциями. DML команды (<code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>) могут быть сгруппированы. Если <code>BEGIN</code> не вызван явно, каждая отдельная DML команда часто выполняется в своей собственной неявной транзакции (режим autocommit, зависит от настроек клиента/СУБД).
* '''<code>COMMIT</code>''': Успешно завершает текущую транзакцию, делая все ее изменения видимыми для других транзакций и постоянными.
* '''<code>ROLLBACK</code>''': Отменяет все изменения, сделанные в текущей транзакции с момента ее начала (или с последней точки сохранения), и завершает транзакцию.
'''Пример (Банковский перевод):'''
<syntaxhighlight lang="sql">BEGIN; -- Начать транзакцию
-- Снять деньги со счета Алекса
UPDATE account SET balance = balance - 50.00 WHERE name = 'Alex';
-- Добавить деньги на счет Ивана
UPDATE account SET balance = balance + 50.00 WHERE name = 'Ivan';
-- Если обе операции прошли успешно:
COMMIT; -- Зафиксировать изменения
-- Если на каком-то этапе произошла ошибка (например, недостаточно средств),
-- нужно выполнить ROLLBACK вместо COMMIT:
-- ROLLBACK; -- Отменить все изменения с момента BEGIN</syntaxhighlight>
'''Точки сохранения (<code>SAVEPOINT</code>):'''
Позволяют установить “закладку” внутри транзакции, к которой можно будет откатиться, не отменяя всю транзакцию.
* '''<code>SAVEPOINT имя_точки;</code>''': Устанавливает точку сохранения.
* '''<code>ROLLBACK TO SAVEPOINT имя_точки;</code>''': Отменяет все изменения, сделанные ''после'' указанной точки сохранения. Сама точка сохранения остается активной.
* '''<code>RELEASE SAVEPOINT имя_точки;</code>''': Удаляет точку сохранения, но ''не'' отменяет изменения, сделанные после нее.
'''Пример с <code>SAVEPOINT</code>:'''
<syntaxhighlight lang="sql">BEGIN;
UPDATE account SET balance = balance - 50.00 WHERE name = 'Alex';
SAVEPOINT savepoint1; -- Установить точку сохранения
UPDATE account SET balance = balance + 50.00 WHERE name = 'Ivan';
-- Предположим, здесь возникла проблема или нужно отменить перевод Ивану
ROLLBACK TO SAVEPOINT savepoint1; -- Откат к состоянию после списания у Алекса
-- Теперь можно попробовать перевести деньги кому-то другому
UPDATE account SET balance = balance + 50.00 WHERE name = 'Ivan2';
COMMIT; -- Фиксируем результат (списание у Алекса и зачисление Ivan2)</syntaxhighlight>
''При откате к точке сохранения (<code>ROLLBACK TO</code>), все точки, созданные ''после* нее, автоматически удаляются.*
[[Category:БД]]
[[Category:БД:Теория]]

Версия от 10:40, 14 апреля 2025

Источник — https://xn--b1amah.xn--80aalyho.xn--d1acj3b/mediawiki/index.php?title=БД:Теория:Глава_5&oldid=39