ДЗЛекция

1. Задание: Дописать лекцию по хранимым процедурам и представлениям. В ДЗ кидаете архив с фотками

2. Хранимые процедуры в реляционных базах данных

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

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

4. Основные характеристики хранимых процедур

1. Хранение на стороне сервера: Код хранимой процедуры
сохраняется в базе данных, что исключает необходимость
повторной передачи SQL-запросов с клиента на сервер, уменьшая
трафик и повышая производительность.
2. Повторное использование: Процедуры можно вызывать
многократно без необходимости переписывания одного и того же
кода. Это делает их особенно удобными для выполнения
стандартных операций, таких как вычисления или массовые
изменения данных.

5. Основные характеристики хранимых процедур

3. Поддержка параметров: Хранимые процедуры могут принимать
входные и возвращать выходные параметры, что позволяет гибко
настраивать их выполнение в зависимости от переданных данных.
4. Транзакционность: В хранимых процедурах можно управлять
транзакциями, что позволяет выполнять операции "всё или
ничего", гарантируя целостность данных при ошибках.
5. Безопасность и управление доступом: Права доступа к
хранимым процедурам могут быть настраиваемыми, что позволяет
ограничивать или предоставлять доступ к выполнению процедур
без прямого доступа к таблицам.

6. Создание хранимой процедуры

CREATE PROCEDURE AddEmployee
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50),
@Salary DECIMAL(10,2)
AS
BEGIN
INSERT INTO Employees (FirstName, LastName, Salary)
VALUES (@FirstName, @LastName, @Salary);
END;

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

EXEC AddEmployee 'John', 'Doe', 5500.00;

8. Преимущества хранимых процедур

1. Повышение производительности: Хранимые процедуры уменьшают
сетевой трафик, так как код выполняется на стороне сервера базы
данных. Это особенно важно для сложных операций и большого
объёма данных.
2. Безопасность: Процедуры позволяют предоставлять пользователям
доступ к конкретным операциям без прямого доступа к таблицам,
что улучшает безопасность данных.
3. Повторное использование кода: Процедуры можно вызывать из
разных частей системы, что упрощает обслуживание и уменьшает
дублирование кода.
4. Управление транзакциями: Процедуры могут объединять несколько
SQL-запросов в одну транзакцию, что позволяет обеспечить
целостность данных.

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

1. Зависимость от СУБД: Код хранимых процедур часто
специфичен для конкретной СУБД (MySQL, PostgreSQL, Oracle и
т.д.), что может затруднить миграцию системы на другую
платформу.
2. Усложнение отладки: Отладка хранимых процедур может быть
сложнее по сравнению с приложениями, написанными на
традиционных языках программирования, так как не все СУБД
поддерживают удобные средства отладки.
3. Поддержка: Со временем большое количество процедур может
усложнить сопровождение системы, особенно если код
процедур не документирован или не стандартизирован.

10. Основные отличия триггеров от хранимых процедур

1. Способ вызова.
2. Назначение.
3. Время выполнения.
4. Взаимодействие с данными.
5. Использование параметров.
6. Поддержка сложной логики и транзакций.
7. Область применения.

11. Представления в реляционных базах данных

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

• Представление (view) в реляционных базах данных — это
виртуальная таблица, которая представляет собой результат
выполнения заранее определённого SQL-запроса. В отличие от
обычных таблиц, представления не хранят данные физически.
Вместо этого они отображают данные из одной или нескольких
таблиц базы данных в зависимости от запроса, который их
определяет.

13. Основные характеристики представлений

• 1. Виртуальность: Представление — это не самостоятельная таблица, а
виртуальный набор данных, определяемый SQL-запросом. Оно
отображает данные, хранящиеся в других таблицах, но само по себе не
содержит данных.
• 2. Абстракция данных: Представление позволяет скрыть сложные
запросы и объединения данных за простым именем, что упрощает
доступ к информации. Пользователи могут работать с представлением,
как с обычной таблицей, не зная деталей его реализации.
• 3. Обновляемость: В некоторых случаях представления могут быть
обновляемыми, то есть поддерживать операции INSERT, UPDATE и
DELETE. Однако это возможно только при выполнении определённых
условий (например, когда представление построено на основе одной
таблицы и не использует агрегирующие функции).

14. Основные характеристики представлений

• 4. Безопасность: Представления могут ограничивать доступ к
определённым столбцам или строкам таблицы, предоставляя
пользователям только те данные, к которым у них есть права
доступа. Это даёт возможность создавать "ограниченные"
представления для разных ролей пользователей.
• 5.
Оптимизация
запросов:
Представления
помогают
оптимизировать работу с данными, так как сложные SQL-запросы
могут быть упрощены и выполнены более эффективно, особенно
если СУБД поддерживает материализованные представления,
которые сохраняют результаты запроса.

15. Применение представлений

1. Упрощение сложных запросов: Представления часто создаются
для абстрагирования сложных SQL-запросов. Например, если
запрос
объединяет
несколько
таблиц
или
включает
агрегированные данные, представление позволяет сократить
повторение этого запроса.
CREATE VIEW EmployeeSalaries AS
SELECT e.id, e.name, s.salary
FROM employees e
JOIN salaries s ON e.id = s.employee_id;

16.

• Теперь вместо того чтобы каждый раз писать сложный запрос,
можно обращаться к представлению:
SELECT * FROM EmployeeSalaries;

17. Применение представлений

2. Повышение безопасности: Представления могут ограничивать
доступ к данным. Пример представления, показывающего только
имя и зарплату сотрудников без других деталей:
CREATE VIEW PublicEmployeeData AS
SELECT name, salary
FROM employees;

18. Применение представлений

3. Логическая независимость: Представления помогают отделить
логическую структуру данных от физической. Изменения в
таблицах (например, добавление столбцов) могут быть незаметны
для пользователей, работающих с представлениями. Это облегчает
модификацию структуры базы данных без нарушения работы
приложений.

19. Применение представлений

4. Агрегация и расчёты: Представления позволяют выполнять
агрегацию данных, вычисления и отображать результаты в
удобном виде. Например, можно создать представление, которое
будет показывать среднюю зарплату по отделам:
CREATE VIEW DepartmentSalaries AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

20. Применение представлений

5. Объединение данных из нескольких таблиц: Представления
позволяют объединять данные из разных таблиц в одну
виртуальную таблицу, что упрощает работу с ними. Например,
можно создать представление, которое собирает данные о заказах
и клиентах:
CREATE VIEW CustomerOrders AS
SELECT customers.name, orders.order_id, orders.total
FROM customers
JOIN orders ON customers.id = orders.customer_id;

21. Типы представлений

1. Простые представления: Это представления, основанные на одной
таблице, которые не используют сложных операций, таких как
агрегирование или подзапросы. Такие представления иногда можно
обновлять (выполнять INSERT, UPDATE, DELETE).
2. Сложные представления: Включают несколько таблиц, агрегированные
данные, подзапросы и другие сложные конструкции. Обновление таких
представлений обычно невозможно.
3. Материализованные
представления:
В
отличие
от
обычных
представлений, которые выполняют запросы при каждом обращении,
материализованные представления сохраняют результаты запроса
физически на диске. Это может значительно повысить производительность
при работе с большими объёмами данных. Материализованные
представления периодически обновляются для синхронизации с
исходными таблицами.

22. Обновляемость представлений

Не все представления можно обновлять напрямую. Для того чтобы
представление было обновляемым, оно должно соответствовать
следующим условиям:
• Представление основано на одной таблице.
• В представлении не используются агрегирующие функции (такие
как SUM, AVG).
• В представлении нет подзапросов, объединений (JOIN) или
группировок (GROUP BY).

23. Преимущества представлений

1. Упрощение работы с данными: Представления позволяют абстрагировать
сложные запросы, делая работу с данными проще для пользователей.
2. Обеспечение безопасности: Они ограничивают доступ к определённым
данным, предоставляя пользователям доступ только к тем данным,
которые им нужны.
3. Повышение
производительности
(с
материализованными
представлениями): Сохраняя результаты запросов, материализованные
представления значительно ускоряют доступ к данным, особенно в
больших базах данных.
4. Логическая независимость: Представления позволяют изменять
физическую структуру базы данных без влияния на работу приложений,
так как пользователи работают с представлением, а не с реальными
таблицами.

24. Недостатки представлений

1. Ограниченная производительность (у обычных представлений):
Поскольку представления не хранят данные, каждый запрос к
представлению приводит к выполнению исходного запроса, что
может снизить производительность при сложных запросах.
2. Ограничения на обновление: Не все представления
поддерживают операции обновления, что может затруднить их
использование в некоторых сценариях.
3. Сложность
синхронизации
(для
материализованных
представлений): Материализованные представления требуют
периодической синхронизации с исходными данными, что
может быть затруднено при частом изменении данных.