- •Предисловие
- •1. Анализ предметной области
- •1.1Анализ требований. Диаграмма вариантов использования
- •1.1.1Диаграмма вариантов использования
- •1.2Концептуальное моделирование предметной области
- •2. Проектирование базы данных
- •2.1Логическая модель базы данных
- •2.2Физическая модель базы данных
- •3.1Подключение к серверу ms sql Server 2008 r2
- •3.2Создание новой базы данных на сервере
- •3.3Создание новой таблицы на сервере
- •3. Создание базы данных
- •3.4Создание связей
- •3.5Создание диаграммы базы данных
- •3.6Ограничения целостности базы данных
- •3.7Сценарий создания базы данных
- •4. Заполнение базы данных
- •4.1Загрузка информации в базу данных средствами Management Studio
- •4.2Ведение базы данных средствами sql
- •4.3Sql-запросы на вставку, модификацию и удаление данных
- •4.3.1Добавление новых записей в таблицу (insert)
- •4.3.2Модификация существующих записей (update)
- •4.3.3Удаление записей из таблицы (delete)
- •5. Запросы на выборку данных
- •5.1Общий синтаксис запроса select
- •5.2Примеры реализации запросов
- •6. Организация бизнес-логики
- •6.1Хранимые процедуры
- •6.2Триггеры
- •7. Прикладной программный интерфейс субд
- •7.1Знакомство с основными объектами ado.Net
- •7.2Разработка клиентского приложения
- •7.3Пример разработки: иас «Оптовый заводской склад»
- •7.4Реализация основных функций Системы
- •7.4.1Ведение основных справочников и оформление поставок деталей
- •7.4.2Организация поиска поставок деталей
- •8. Администрирование сервера бд
- •8.1Инсталляция сервера
- •8.2Резервное копирование и восстановление бд
- •8.3Санкционирование доступа
- •Приложение а. Варианты заданий
- •1. Биржа труда
- •2. Проектная организация
- •3. Библиотечный фонд города
- •4. Турагентство
- •5. Регистратура городской поликлиники
- •6. Гибдд
- •7. Оператор сотовой связи
- •8. Аптека
- •9. Аудиторный фонд университета
- •10. Агентство недвижимости
- •11. Студенческий городок
- •12. Санаторий
- •13. Продуктовый магазин
- •14. Научные публикации вуза
- •15. Служба такси
- •16. Страховое агентство
- •17. Сайт купонов на скидки
- •18. Учебный план
- •19. Преподаватели университета
- •20. Расчет стипендии
- •21. Расписание занятий
- •Приложение б. Состав отчета
- •Список литературы
- •153003, Г. Иваново, ул. Рабфаковская, 34.
6. Организация бизнес-логики
При создании корпоративных информационных систем всё большую популярность приобретают решения на базе «тонких клиентов». Под «тонким клиентом» подразумевается персональный компьютер, подключаемый к сети, не выполняющий никаких вычислительных задач кроме отображения данных на экране и передачи вводимой информации на сервер, на котором выполняется основная функциональность системы.
Как и большинство современных СУБД, MS SQL Server предоставляет возможность частично или полностью перенести бизнес-логику приложения на уровень сервера баз данных. Для решения подобных задач служат механизмы триггеров, хранимых процедур и пользовательских функций.
6.1Хранимые процедуры
Хранимая процедура – это упорядоченная последовательность операторов SQL, оформленных в виде единого логического модуля.
В хранимых процедурах допускается использование переменных и параметров, а также операторов управления ходом выполнения и циклических конструкций.
Общий синтаксис создания хранимой процедуры:
CREATE PROC[EDURE] <Имя процедуры>
[@<Имя параметра> <Тип данных параметра>
[ = <Значение параметра по умолчанию>]
[OUTPUT]] [ ,...n ]
AS <SQL-выражения, составляющие тело процедуры>
Общий синтаксис вызова хранимой процедуры:
EXEC[UTE] <Имя процедуры>
[[<Имя входного параметра> = ]<Значение входного параметра>] [,…n]
Среда Management Studio предоставляет два способа создания хранимой процедуры. Первый способ – использование рассмотренного выше окна редактора запросов. Второй способ – в «Обозревателе объектов» в ветке нужной базы данных выбрать пункт «Программирование / Хранимые процедуры» (рис. 6.1). При этом будет сгенерирован шаблон новой хранимой процедуры, снабженный подробными комментариями, которые призваны помочь в написании текста процедуры (рис. 6.2).
Рис. 6.1. Создание новой хранимой процедуры
Рис. 6.2. Автоматически генерируемый шаблон хранимой процедуры
Примеры:
1. Хранимая процедура, возвращающая сведения о поставках деталей
Рис. 6.3. Текст хранимой процедуры №1 и результаты ее работы
2. Хранимая процедура, возвращающая сведения о поставках деталей за определенный интервал времени
Рис. 6.4. Текст хранимой процедуры №2 и результаты ее работы
3. Процедура, добавляющая сведения в таблицу деталей
Рис. 6.5. Текст хранимой процедуры №3 и результаты ее работы
4. Процедура, возвращающая название материала, из которого изготовлено больше всего деталей
Рис. 6.6. Текст хранимой процедуры №4 и результаты ее работы
5. Процедура, реализующая функцию каскадного удаления на связь между таблицами материалов и деталей
Рис. 6.7. Текст хранимой процедуры №5 и результаты ее работы
Задание: создайте 2 хранимые процедуры для своей базы, в отчет поместите их описание, код создания, код вызова, результат.
6.2Триггеры
Триггеры, как и хранимые процедуры, являются механизмом организации функционала системы в теле сервера баз данных. В таблице 6.2 приведено сравнение триггеров и хранимых процедур.
Таблица 6.2. Сравнение триггеров и хранимых процедур |
|
Хранимые процедуры |
Триггеры |
Являются самостоятельным объектом базы данных |
Привязываются к таблице или виду |
Явно вызываются на выполнение командой EXECUTE |
Реагируют на события, связанные с таблицей-владельцем |
Могут иметь входные и / или выходные параметры, коды возврата |
Не имеют входных / выходных параметров и кодов возврата |
Могут выполняться в отдельной транзакции |
Выполняются в той же транзакции, в которой осуществляются изменения таблицы-владельца |
Возможность закрепления триггеров за конкретными операторами определяется тем, что в языке SQL предусмотрены три типа запросов, предназначенных для изменения данных – INSERT, UPDATE и DELETE. В связи с этим выделяют шесть видов триггеров (табл. 6.3).
Таблица 6.3. Виды триггеров |
||
Объект |
Действие |
Триггер |
Таблица / вид |
Вставка строк INSERT |
Вместо вставки строк INSTEAD OF |
После вставки строк FOR (AFTER) |
||
Модификация строк UPDATE |
Вместо модификации строк INSTEAD OF |
|
После модификации строк FOR (AFTER) |
||
Удаление строк DELETE |
Вместо удаления строк INSTEAD OF |
|
После удаления строк FOR (AFTER) |
||
Примечание: триггеры FOR и AFTER являются синонимами.
Независимо от используемого типа триггера для реализации действий, предусмотренных этим триггером в СУБД SQL Server, используются две виртуальные таблицы.
В одной из них хранятся копии всех вставляемых строк (поэтому данная таблица именуется INSERTED), а во второй хранятся копии всех удаляемых строк (эта таблица именуется DELETED) (табл. 6.4).
Таблица 6.4. Содержимое таблиц 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. Текст триггера №1
2. Триггер на поддержание актуального количества товаров на складе при удалении поставок
Рис. 6.12. Текст триггера №2
3. Триггер на поддержание актуального количества товаров на складе при отпуске деталей в цеха
Рис. 6.13. Текст триггера №3
4. Проверка допустимости значения даты поставки при осуществлении новой поставки деталей
Рис. 6.14. Текст триггера №4
5. Проверка допустимости значения даты поставки при редактировании существующей поставки деталей
Рис. 6.15. Текст триггера №5
Задание: создайте 2 триггера для своей базы, в отчет поместите их описание и код создания.
