Объединение результатов UNION

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

Рис. 5.36. Результаты выполнения запроса №36

37. Получить таблицу из двух полей, где первое поле – название детали, материала, поставщика или дата поставки, а второе поле – длина строки из первого поля (рис. 5.37).

Рис. 5.37. Результаты выполнения запроса №37

 

Задание: создайте 10 запросов на выборку данных для своей базы. В отчет поместите их описание, код и результат вызова в форме копии экрана.

Контрольные вопросы

1. Что является результатом выполнения команды SELECT?

2. Какие опции команды SELECT являются обязательными?

3. Каково предназначение опции DISTINCT?

4. В какой форме задается выражение в опции WHERE?

5. Что такое декартово произведение отношений? Какую роль оно играет в процессе навигации по реляционной базе данных?

6. Какую опцию используют при подведении промежуточных итогов посредством агрегативных функций?

7. Когда используется опция HAVING?

8. Для сортировки результатов выборки по убыванию какую опцию следует использовать в запросе?

9. Когда удобно использовать опцию CASE в запросе?

10. Какую опцию следует использовать для формирования пользовательских имен полей в результирующей таблице?

11. В какой последовательности выполняются вложенные запросы?

12. В каком случае используется опция EXISTS?

13. Охарактеризуйте внутреннее соединение таблиц. Какой опцией оно реализуется?

14. Что такое левое и правое соединение таблиц? Какими опциями они реализуются?

15. Когда удобно использовать объединение таблиц? Какой опцией оно реализуется и каковы требования к объединяемым таблицам?

16. Когда удобно использовать пересечение таблиц? Какой опцией оно реализуется?

17. Когда удобно использовать вычитание таблиц? Какой опцией оно реализуется?

Организация бизнес-логики

СУБД, MS SQL Server предоставляет возможность частично или полностью перенести бизнес-логику приложения на уровень сервера баз данных. Для решения подобных задач служат механизмы триггеров, хранимых процедур и пользовательских функций.

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

Хранимая процедура – это упорядоченная последовательность операторов SQL, оформленных в виде единого логического модуля.

В хранимых процедурах допускается использование переменных и параметров, а также операторов управления ходом выполнения и циклических конструкций.

Общий синтаксис создания хранимой процедуры:

CREATE PROC[EDURE] <Имя процедуры>

[@<Имя параметра> <Тип данных параметра>

[ = <Значение пар-ра по умолчанию>]

[OUTPUT]] [,...n]

AS <SQL-выражения, составляющие тело процедуры>

Общий синтаксис вызова хранимой процедуры:

EXEC[UTE] <Имя процедуры>

[[<Имя входного пар-ра> = ]<Значение входного пар-ра>] [,…n]

Среда Management Studio предоставляет два способа создания хранимой процедуры.

Первый способ – использование рассмотренного выше окна редактора запросов.

Второй – в ветке нужной базы данных (правая часть окна программы) выбрать пункт «Программирование / Хранимые процедуры» (рис. 6.1). При этом будет сгенерирован шаблон новой хранимой процедуры, снабженный подробными комментариями, которые призваны помочь в написании текста процедуры (рис. 6.2).

Рис. 6.1. Создание новой хранимой процедуры

Рис. 6.2. Автоматически генерируемый шаблон хранимой процедуры

Примеры:

1. Хранимая процедура, возвращающая отчет о поставках деталей (рис. 6.3).

Рис. 6.3. Текст хранимой процедуры №1 и результаты ее работы

2. Хранимая процедура, возвращающая отчет о поставках деталей за определенный интервал времени (рис. 6.4).

Рис. 6.4. Текст хранимой процедуры №2 и результаты ее работы

3. Процедура, добавляющая новую деталь в таблицу деталей (рис. 6.5).

Рис. 6.5. Текст хранимой процедуры №3 и результаты ее работы

4. Процедура, возвращающая название материала, из которого изготовлено больше всего деталей (рис. 6.6).

Рис. 6.6. Текст хранимой процедуры №4 и результаты ее работы

5. Процедура, реализующая функцию каскадного удаления на связь между таблицами материалов и деталей (рис. 6.7).

Рис. 6.7. Текст хранимой процедуры №5 и результаты ее работы

Задание: создайте две хранимые процедуры для своей базы. При разработке алгоритмов бизнес-логики используйте результаты анализа требований к создаваемой информационной системе. В отчет поместите описание хранимых процедур, код создания, код вызова, результат вызова.

Триггеры

Триггеры, как и хранимые процедуры, являются механизмом организации функционала системы в теле сервера баз данных. В табл. 6.1 приведено сравнение триггеров и хранимых процедур.

Таблица 6.1. Сравнение триггеров и хранимых процедур
Хранимые процедуры	Триггеры
Являются самостоятельным объектом базы данных	Привязываются к таблице или виду
Явно вызываются на выполнение командой EXECUTE	Реагируют на события, связанные с таблицей-владельцем
Могут иметь входные и / или выходные параметры, коды возврата	Не имеют входных / выходных параметров и кодов возврата
Могут выполняться в отдельной транзакции	Выполняются в той же транзакции, в которой осуществляются изменения таблицы-владельца

Возможность закрепления триггеров за конкретными операторами определяется тем, что в языке SQL предусмотрены три типа запросов, предназначенных для изменения данных – INSERT, UPDATE и DELETE. В связи с этим выделяют шесть видов триггеров (табл. 6.2).

Таблица 6.2. Виды триггеров
Объект	Действие	Триггер
Таблица / вид	Вставка строк INSERT	Вместо вставки строк INSTEAD OF
После вставки строк FOR (AFTER)
Модификация строк UPDATE	Вместо модификации строк INSTEAD OF
После модификации строк FOR (AFTER)
Удаление строк DELETE	Вместо удаления строк INSTEAD OF
После удаления строк FOR (AFTER)

Примечание: триггеры FOR и AFTER являются синонимами.

Независимо от используемого типа триггера для реализации действий, предусмотренных этим триггером в СУБД SQL Server, используются две виртуальные таблицы.

В одной из них хранятся копии всех вставляемых строк (поэтому данная таблица именуется INSERTED), а во второй хранятся копии всех удаляемых строк (эта таблица именуется DELETED) (табл. 6.3).

Таблица 6.3. Содержимое таблиц INSERTED и DELETED
Вид триггера	Таблицы INSERTED и DELETED
INSTEAD OF INSERT	INSERTED содержит вставляемые в таблицу записи. DELETED пуста
FOR INSERT	INSERTED содержит вставленные в таблицу записи. DELETED пуста
INSTEAD OF UPDATE	INSERTED содержит новые значения модифицируемых записей. DELETED содержит старые значения модифицируемых записей
FOR UPDATE	INSERTED содержит новые значения модифицированных записей. DELETED содержит старые значения модифицированных записей
INSTEAD OF DELETE	INSERTED пуста. DELETED содержит удаляемые записи
FOR DELETE	INSERTED пуста. DELETED содержит удаленные записи

 

На рис. 6.8 приведена схема запуска триггеров FOR (AFTER) и INSTEAD OF.

Рис. 6.8. Схема запуска триггеров FOR (AFTER) и INSTEAD OF

 

Наиболее характерные области применения триггеров – это обеспечение ссылочной и семантической целостности базы данных, создание журналов, поддержка сложных ограничений и проверок, реакция сервера в ответ на действия пользователя.

Создавать новые триггеры можно либо в редакторе запросов среды Management Studio, либо воспользовавшись специальным шаблоном (рис. 6.10), который открывается при выборе в «Обозревателе объектов» пункта «Создать триггер» у таблицы, к которой будет привязываться создаваемый триггер (рис. 6.9).

 

Рис. 6.9. Создание нового триггера

Рис. 6.10. Шаблон создания нового триггера

Общий синтаксис создания триггера:

CREATE TRIGGER <Имя триггера>

ON <Имя таблицы или вида>

{FOR|AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}

AS <SQL-выражения, составляющие тело триггера>

Примеры:

1. Триггер на поддержание актуального количества товаров на складе при добавлении поставок деталей (рис. 6.11).

Рис. 6.11. Текст триггера №1

2. Триггер на поддержание актуального количества товаров на складе при удалении поставок (рис. 6.12).

Рис. 6.12. Текст триггера №2

3. Триггер на поддержание актуального количества товаров на складе при отпуске деталей в цеха (рис. 6.13).

Рис. 6.13. Текст триггера №3

4. Проверка допустимости значения даты поставки при осуществлении новой поставки деталей (рис. 6.14).

Рис. 6.14. Текст триггера №4

5. Проверка допустимости значения даты поставки при редактировании существующей поставки деталей (рис. 6.15).

Рис. 6.15. Текст триггера №5

 

Задание: создайте два триггера для своей базы, в отчет поместите их описание и код создания.

 

Контрольные вопросы

1. Что такое бизнес-логика приложения в теле сервера БД?

2. Охарактеризуйте архитектуры информационных систем?

3. В чем преимущества и недостатки использования бизнес-логики приложения в теле сервера баз данных?

4. Что такое триггер? Как он активизируется в информационной системе?

5. Охарактеризуйте особенности работы с триггерами в СУБД MS SQL Server.

6. Приведите пример триггера на поддержание целостности БД.

7. Чем триггер отличается от хранимой процедуры?

8. Кто может вызывать триггер и хранимую процедуру?

9. Охарактеризуйте синтаксис языка хранимых процедур СУБД MS SQL Server.

10. Как значения параметров передаются в хранимую процедуру?

11. Приведите пример процедуры с возвращаемыми значениями параметров.

12. Приведите пример хранимой процедуры для организации отчета по информации, собранной в созданной БД.

13. Приведите пример хранимой процедуры для поддержания историчности регистрационных сведений в созданной БД.

14. Что такое вид над таблицей? Какой командой он создается?

15. Приведите пример использования вида в созданной БД.

16. Может ли триггер быть связан с видом?