ПИСиБДРВ практика 3.2

1. Представления. Триггеры. Процедуры. Функции

МИРЭА - Российский технологический университет
mirea.ru
Представления.
Триггеры. Процедуры.
Функции

2. Представления

МИРЭА - Российский технологический университет
mirea.ru
Представления
Представление — это виртуальная таблица,
содержащая не реальные данные, а результат
выполнения SQL-запроса. Оно не хранит данные
физически, а генерирует их динамически при
каждом запросе, основываясь на базовых таблицах,
из которых оно формируется. Представления могут
включать данные из одной или нескольких таблиц, а
также могут содержать агрегированные данные,
фильтры и другие SQL-конструкции.
Представление хранится как объект базы данных и
может использоваться в запросах так же, как
обычная таблица.
2

3. Синтаксис представления

МИРЭА - Российский технологический университет
mirea.ru
Синтаксис представления
CREATE VIEW имя_представления AS
SELECT список_полей
FROM имя_таблицы
[WHERE условие];
Представление, которое отображает список книг с их авторами, основываясь на
таблицах Book, Book_Author, и Author.
3

4. Вызов представления

МИРЭА - Российский технологический университет
mirea.ru
Вызов представления
Вызывается представление так же, как и обычная
таблица с использованием SELECT.
4

5. Встроенные функции SQL

МИРЭА - Российский технологический университет
mirea.ru
Встроенные функции SQL
В SQL существует множество встроенных функций,
которые помогают выполнять
различные операции над данными. Они могут быть
разделены на следующие категории.
Строковые функции:
• CONCAT(str1, str2, ...) – соединяет (склеивает) две
или более строк в одну;
• LENGTH(str) – возвращает длину строки в байтах;
• UPPER(str) и LOWER(str) – преобразует строку в
верхний или нижний регистр.
5

6. Встроенные функции SQL

МИРЭА - Российский технологический университет
mirea.ru
Встроенные функции SQL
Числовые функции:
• ABS(x) – возвращает абсолютное значение x;
• CEILING(x) или CEIL(x) – округляет число до
ближайшего большего или равного целого числа;
• FLOOR(x) – округляет x до ближайшего меньшего
целого или равного числа;
• ROUND(x, d) – округляет число до заданного
количества десятичных знаков. Если количество
десятичных знаков не указано, округляет число до
ближайшего целого.
6

7. Встроенные функции SQL

МИРЭА - Российский технологический университет
mirea.ru
Встроенные функции SQL
Даты и времени:
• CURDATE() – возвращает текущую дату;
• NOW() – возвращает текущие дату и время;
• YEAR(date) – извлекает год из даты;
• DATEDIFF(date1, date2) – возвращает разницу
между date1 и date2 в днях;
• TIMESTAMPDIFF (unit, datetime1, datetime2) –
возвращает разницу в указанных единицах
измерения.
7

8. Процедурный SQL

МИРЭА - Российский технологический университет
mirea.ru
Процедурный SQL
Процедурный SQL представляет собой расширение
стандартного SQL, которое включает в себя элементы
процедурного программирования. Эти расширения
позволяют создавать сложные логические
конструкции, управлять потоком выполнения кода,
использовать переменные, циклы, условия и
функции.
8

9. Переменные в процедурном языке SQL

МИРЭА - Российский технологический университет
mirea.ru
Переменные в процедурном языке SQL
Переменные используются для хранения промежуточных данных,
выполнения вычислений, управления потоком выполнения программы и
передачи данных между процедурами, функциями и триггерами.
Пользовательские переменные в SQL — это переменные, которые
объявляются и используются пользователями для хранения данных в ходе
выполнения программы. Эти переменные могут содержать различные
типы данных, такие как числа, строки, даты и т.д.
В MySQL пользовательские переменные создаются и используются в
запросах, процедурах и функциях. Они могут быть объявлены и
инициализированы с помощью оператора SET, а также могут быть
использованы в выражениях и других запросах. Имя переменной должно
начинаться с символа «@». После знака равенства указывается значение,
присваиваемое переменной. Оно может числовым, строковым или
выражением.
9

10. Системные переменные в процедурном языке SQL

МИРЭА - Российский технологический университет
mirea.ru
Системные переменные в процедурном
языке SQL
Системные переменные — это переменные,
предоставляемые самой СУБД. Они содержат информацию о
текущем состоянии системы, сеансе пользователя,
транзакциях и других аспектах работы базы данных.
Системные переменные могут быть использованы для
получения информации о контексте выполнения SQL-кода.
10

11. Локальные переменные в процедурном языке SQL

МИРЭА - Российский технологический университет
mirea.ru
Локальные переменные в процедурном
языке SQL
Локальные переменные объявляются в начале процедур,
функций или триггеров и используются только внутри этих
блоков. Они позволяют хранить промежуточные результаты
вычислений, управлять потоком выполнения кода и
обеспечивать логику обработки данных.
Локальные переменные объявляются с использованием
ключевого слова DECLARE и доступны только в пределах
того блока, где они объявлены.
11

12. Разделитель команд

МИРЭА - Российский технологический университет
mirea.ru
Разделитель команд
В СУБД команда DELIMITER используется для изменения
стандартного разделителя команд, который по умолчанию
является точкой с запятой (;). При создании хранимых
процедур или функций, код внутри процедуры может
содержать несколько SQL-команд, каждая из которых
заканчивается точкой с запятой. Если не изменить
разделитель, то СУБД воспримет первую точку с запятой как
окончание всей процедуры, что приведет к ошибке. Чтобы
избежать этого, используется команда DELIMITER, которая
позволяет временно изменить стандартный разделитель
команд на другой символ или набор символов (например, $$
или //). Это позволяет СУБД корректно интерпретировать код
процедуры или функции и завершить выполнение только
после ввода нового разделителя.
12

13. Хранимые процедуры

МИРЭА - Российский технологический университет
mirea.ru
Хранимые процедуры
Хранимая процедура – это программа, хранящаяся на сервере базы
данных и выполняющая набор SQL-операторов. Процедура
вызывается пользователем и может принимать входные
параметры. В своем самом базовом виде хранимая процедура
должна быть объявлена с использованием команды CREATE
PROCEDURE, после которой указывается уникальное имя
процедуры. Затем следует список параметров, определяющих
входные и выходные данные. Тело процедуры оформляется в виде
составного оператора, начинающегося с ключевого слова BEGIN и
завершающегося END. Внутри этого блока размещается исходный
код процедуры, который реализует заложенную в процедуру
логику и последовательность действий.
13

14. Синтаксис хранимых процедур

МИРЭА - Российский технологический университет
mirea.ru
Синтаксис хранимых процедур
Рассмотрим процедуру для автоматизированного добавления
новой книги в каталог. CREATE PROCEDURE — команда для создания
новой процедуры. В данном случае процедура называется
add_new_book. IN … — определение входных параметров
процедуры, которые будут использоваться для добавления данных.
Если требуется объявить выходной параметр, то используется
оператор OUT. INSERT INTO Book — основная команда для
добавления новой книги в таблицу Book.
14

15. Вызов хранимых процедур

МИРЭА - Российский технологический университет
mirea.ru
Вызов хранимых процедур
После создания процедуры ее необходимо вызвать. Для вызова хранимой
процедуры используется команда CALL. Это позволяет выполнить
процедуру, передав ей необходимые параметры, и получить результат ее
работы.
15

16. Примеры хранимых процедур

МИРЭА - Российский технологический университет
mirea.ru
Примеры хранимых процедур
Процедура update_user_subscription создана для обновления информации
о подписке пользователя, включая новую подписку и ее срок действия.
Процедура count_books_by_genre считает общее количество книг в
каталоге для заданного жанра.
16

17. Пользовательские функции

МИРЭА - Российский технологический университет
mirea.ru
Пользовательские функции
Пользовательская функция– это объект базы данных, который выполняет
вычисления и возвращает одно значение. Функция может принимать
параметры и вызываться внутри SQL-запросов. Функции создаются с
помощью команды CREATE FUNCTION.
Детерминированные функции в SQL — это функции, которые при каждом
вызове с одними и теми же входными значениями всегда возвращают
один и тот же результат.
Детерминированной считается функция, если:
• она не зависит от внешних факторов, таких как время, состояние
системы или данные в других таблицах;
• ее поведение полностью определяется переданными ей аргументами.
Примерами детерминированных функций могут быть математические
функции, такие как ABS(), а также функции для работы со строками, такие
как UPPER() или CONCAT().
В отличие от детерминированных, недетерминированные функции могут
возвращать разные результаты при одинаковых входных данных.
Примеры включают функции, зависящие от текущего времени (NOW(),
CURRENT_TIMESTAMP).
17

18. Синтаксис пользовательских функций

МИРЭА - Российский технологический университет
mirea.ru
Синтаксис пользовательских функций
Данная функция возвращает количество книг определенного жанра:
• CREATE FUNCTION — команда для создания функции;
• count_books_by_genre — имя функции, которая принимает
идентификатор жанра и возвращает количество книг в этом жанре;
• RETURNS INT — указывает, что функция возвращает целое число;
• DECLARE book_count INT — объявляется переменная для хранения
результата;
• RETURN book_count — возвращает результат вычисления.
18

19. Вызов пользовательских функций

МИРЭА - Российский технологический университет
mirea.ru
Вызов пользовательских функций
Пользовательские функции в вызываются
аналогично встроенным функциям – с
использованием конструкции SELECT.
19

20. Примеры пользовательских функций

МИРЭА - Российский технологический университет
mirea.ru
Примеры пользовательских функций
Функция avg_subscription_duration() вычисляет среднее количество книг в
подписке. В данном случае функция не имеет входного аргумента,
поэтому в скобках ничего не указывается.
Avg_subscription_duration_by_country возвращает среднюю
продолжительность подписок для пользователей из заданной страны.
20

21. Модификация хранимых процедур и функций

МИРЭА - Российский технологический университет
mirea.ru
Модификация хранимых процедур и функций
Для модификации существующих процедур и функций в SQL
используется конструкция ALTER.
Для удаления используется команда DROP. Однако перед
выполнением данной операции необходимо убедиться, что
удаляемая процедура или функция больше нигде не
используется в запросах или других процедурах. В противном
случае, это может привести либо к отказу системы в удалении
объекта, либо к сбоям в работе базы данных, связанным с
нарушением целостности зависимых компонентов.
21

22. Триггеры

МИРЭА - Российский технологический университет
mirea.ru
Триггеры
Триггер — это разновидность хранимой процедуры, которая автоматически
выполняется в ответ на определенные события в таблице, такие как вставка,
обновление или удаление записей. Триггеры помогают поддерживать
согласованность и целостность данных в таблицах базы данных. В
зависимости от типа события и момента его возникновения, триггеры делятся
на несколько категорий:
• BEFORE TRIGGER — выполняется до того, как событие произойдет;
• AFTER TRIGGER — выполняется после того, как событие) произошло;
• INSTEAD OF TRIGGER (только в некоторых СУБД, например, в PostgreSQL) —
выполняется вместо выполнения операции вставки, обновления или
удаления.
Каждый из этих триггеров может быть связан с одним из трех типов операций:
• INSERT — срабатывает при добавлении новой записи в таблицу;
• UPDATE — срабатывает при изменении существующей записи в таблице;
• DELETE — срабатывает при удалении записи из таблицы.
События BEFORE INSERT и AFTER INSERT возникают соответственно перед тем,
как новая строка попадет в таблицу, и после того, как эта строка будет
сохранена. События BEFORE UPDATE и AFTER UPDATE генерируются перед
началом и после завершения редактирования данных. Событие BEFORE
DELETE предшествует, а AFTER DELETE завершает операцию удаления данных
из таблицы.
22

23. Контекстные переменные NEW и OLD

МИРЭА - Российский технологический университет
mirea.ru
Контекстные переменные NEW и OLD
Для обеспечения доступа к новым и старым значениям
необходимо использование контекстных переменных NEW и
OLD. Контекстные переменные NEW и OLD позволяют
обращаться к значениям полей до и после выполнения
операции:
• NEW — используется в триггерах INSERT и UPDATE для
обращения к новым значениям, которые будут вставлены или
обновлены в таблице;
• OLD — используется в триггерах UPDATE и DELETE для
обращения к старым значениям, которые были до изменения
или удаления записи.
Триггеры не допускают операций по созданию (CREATE),
изменению (ALTER) и удалению (DROP) объектов базы данных.
Эти ограничения введены для предотвращения потенциально
опасных изменений структуры базы данных в ходе выполнения
триггеров, что могло бы привести к непредсказуемым
последствиям и нарушению работы системы в целом.
23

24. Синтаксис триггеров

МИРЭА - Российский технологический университет
mirea.ru
Синтаксис триггеров
Рассмотрим синтаксис на следующем примере. В сервисе предусмотрено
требование, что новая книга может быть добавлена в систему, если ее тираж не
менее 500 экземпляров.
Триггер отделяется от основного кода заменой разделителя. Далее идет создание
с помощью команды CREATE TRIGGER и указание уникального названия. Строка
BEFORE INSERT ON Book говорит о том, что триггер срабатывает перед тем, как
новая запись будет добавлена в таблицу Book. Строка FOR EACH ROW говорит о
том, что это тип триггеров, которые срабатывают для каждой строки, которая
подвергается изменению в таблице. Это означает, что триггер будет запущен
отдельно для каждой строки, которая была затронута операцией, а не один раз
для всей операции в целом. После ключевого слова BEGIN используется основное
тело триггера. IF NEW.circulation < 500 — проверяет новое значение circulation
(тиража). SIGNAL SQLSTATE '45001' — отмена вставки с выводом сообщения об
ошибке.
24

25. Проверка триггера

МИРЭА - Российский технологический университет
mirea.ru
Проверка триггера
Пробуем вставить новую строку, где тираж меньше 500
экземпляров, а именно 350. Триггер сработал верно и
запретил вставку с указанием ошибки.
Теперь добавим информацию о книге, которая выполняет
условия триггера. Книга с тиражом 600 экземпляров
успешно внесена в БД.
25

26. Примеры триггеров

МИРЭА - Российский технологический университет
mirea.ru
Примеры триггеров
Триггеры after_insert_book_log, after_update_book_log,
after_delete_book_log будет вносить записи в таблицу Book_log каждый
раз, когда будет происходить изменение таблицы Book.
26

27. Примеры триггеров

МИРЭА - Российский технологический университет
mirea.ru
Примеры триггеров
В этом примере триггер служит для каскадного удаления книг
при удалении соответствующего издателя. Важно учитывать,
что для выполнения каскадного удаления правила
поддержания ссылочной целостности у соответствующих
таблиц должно быть ON DELETE CASCADE.
27

28. Создание и редактирование объектов в Workbench

МИРЭА - Российский технологический университет
mirea.ru
Создание и редактирование объектов в Workbench
Создавать и редактировать все описанные SQL-объекты
можно как в области создания скриптов, так и с помощью
автоматизированных пользовательских функций.
28

29. Практическая работа

МИРЭА - Российский технологический университет
mirea.ru
Практическая работа
Для сдачи второй части 3-й практической работы необходимо
создать минимум одно представление, две хранимые
процедуры, две функции и шесть триггеров (по одному на
каждый тип). Все созданные объекты должны быть
выполнены по своей предметной области и иметь целевое
назначение.
29