Файл: Методические указания к практическим занятиям для студентов направления подготовки 230100. 68.pdf

30 2. Владелец, марки его автомобилей, суммарная стоимость и вре- мя ремонта.
3. Автомеханик, разряд, суммарное время работы за месяц, сум- марная стоимость заказов.
4.
Марка автомобиля, число раз ремонта автомобилей в этой ав- томастерской, когда время ремонта превышало день.
Вариант 7. Предметная область «Сессия»
1. Факультет, курс, количество зачетов, количество экзаменов в летнюю сессию.
2.
Группа, название предмета, даты всех экзаменов и зачетов по этому предмету.
3.
ФИО преподавателя, название предмета, количество видов контроля по этому предмету для этого преподавателя.
4. Название предмета, кафедра, общее количество студентов, сдающих этот предмет в зимнюю сессию.
Вариант 8. Предметная область «Управление проектом»
1.
Проект, сотрудник, трудоемкость, дата выдачи, трудоемкость проекта.
2. Проект, ФИО работника, трудоемкость, процентное отношение трудоемкости работника к общей трудоемкости проекта.
3. ФИО работника, проекты за последний месяц, трудоемкость для каждого проекта, время выполнения.
4.
Название проекта, суммарное время работы над проектом за год – для проектов у которых не истек крайний срок в течении года.
Вариант 9. Предметная область «Поликлиника»
1. Врач, пациент, время лечения – только для вылечившихся па- циентов.
2. Социальный статус, месяц, диагноз, количество человек, выле- чившихся за этот месяц.
3.
Врач, количество пациентов за год, лечение которых закончи- лось успешно, общее количество пациентов за год.
4.
Год рождения, количество пациентов этого года рождения, ча- ще всего встречающийся у них диагноз.
Вариант 10. Предметная область «Сотовая связь»
1. Оператор, тариф, количество зарегистрированных абонентов для этого тарифа.

31 2. Название оператора, тарифный план, месяц, число подключе- ний в этот месяц.
3. ФИО абонента, количество тарифных планов, любимый опера- тор связи.
4.
Оператор связи, тариф, общая задолженность по тарифу за год, количество проданных за год тарифов.
Вариант 11. Предметная область «Спортивная статистика»
1. Вид спорта, соревнование, место, дата, мировой рекорд, кото- рый был установлен.
2.
Вид спорта, количество соревнований за год, количество попа- даний спортсменов из 'России' в призеры.
3.
Страна, количество спортсменов этой страны, общее количе- ство побед спортсменов этой страны за год на соревнованиях, в назва- ниях которых входит слово 'плавание'.
4. Вид спорта, рекорды, установленные в этом виде спорта за по- следние пять лет, место соревнований, на которых установлены ре- корды.
Вариант 12. Предметная область «Поставки»
1.
Предприятие, продукция, год, объем поставок в этом году
2. Предприятие, месяц, объем продаж за этот месяц, суммарная цена продаж.
3. Название продукции, предприятие, занимающееся продажей этой продукции, объем продаж за год.
4. Вид деятельности предприятия, количество предприятий, годо- вой бюджет отрасли.
Вариант 13. Предметная область «Городской транспорт»
1. Вид транспорта, № маршрута, начальный пункт, конечный пункт.
2.
Вид транспорта, количество машин на маршрутах, суммарный доход, средний доход на одну машину.
3. Маршрут, начальный пункт, конечный пункт, все возможные способы добраться не более чем с двумя пересадками в конечных пунктах.
4. Вид транспорта, среднее количество пассажиров в одной ма- шине, если за день машина делает 20 рейсов, средний доход от одного рейса.

32
Вариант 14. Предметная область «География»
1. Государство, национальность, численность, тип управления.
2. Государство, язык, количество населения, говорящего на этом языке, процент от всего общего количества населения.
3.
Государство, количество мужского населения, количество жен- ского населения, преобладающий язык, преобладающая националь- ность.
4. Национальность, изменение численности населения за год, преобладающая страна проживания.
Вариант 15. Предметная область «Домоуправление»
1.
Год, дом, квартира, задолженность по оплате.
2. Вид услуг, наличие счетчика, внесенная сумма оплаты за год, количество человек, оплативших услугу.
3. Владелец, услуга, затраты на услугу в год, средняя сумма опла- ты за услугу в месяц.
4.
Улица, номер дома, количество услуг, доступных жильцам, суммарная стоимость за месяц.
Контрольные вопросы
1. Объяснить, как работают написанные запросы.
2.
Рассказать о CHECK OPTION.
3. Рассказать о модификации данных через представления.
4.
Рассказать о вставке данных через представления.
5. Упростить один или несколько запросов.
6.
Продемонстрировать изменение и вставку данных через пред- ставления.
7. Написать или модифицировать запрос по сформулированному заданию

33
П р а к т и ч е с к о е з а н я т и е № 4
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ТРИГГЕРОВ
Цель работы: освоение способов создания триггеров в среде
Microsoft SQL Server.
Темы для предварительной проработки

Функции триггеров.

Синтаксис создания триггеров

Использование триггеров в целях аудита
Выполнение работы
Подготовить план создания триггера для контроля ограничений целостности, заданных в Вашем варианте индивидуального задания.
Сформулировать 2-4 запроса на добавление / изменение / удале- ние данных в таблицу, для которой будет назначен триггер. Составить
SQL-скрипты для выполнения этих запросов.
Темы для проработки
1.
Функции триггеров.
2. Типы триггеров и условия их срабатывания.
3.
Создание и использование триггеров в MS SQL Server.
Примеры
В качестве примера реализуем следующее ограничение целостно- сти, имеющее смысл для предметной области «музыкальные предпо- чтения»: пользователь может оценивать не более 5 песен в сутки. Со- здать такой триггер можно, например, следующим образом:
CREATE TRIGGER LimitLikes
ON [Like] AFTER INSERT AS
IF EXISTS(
SELECT [Like].UserID
FROM [Like]
WHERE
[Like].UserID IN (SELECT UserID FROM inserted) AND

34
CONVERT(VARCHAR, [Like].[Date], 105) IN
(SELECT CONVERT(VARCHAR, inserted.[Date], 105)
FROM inserted
WHERE inserted.UserID = [Like].UserID)
GROUP BY [Like].UserID, CONVERT(VARCHAR, [Like].[Date], 105)
HAVING COUNT([Like].SongID) > 5)
BEGIN
ROLLBACK
END
Этот триггер будет срабатывать после вставки записей в таблицу
Like (AFTER INSERT) и не позволит добавлять новые строки, если лимит в 5 оценок в сутки превышен. Проверим теперь работу триггера:
INSERT [Like] ([UserID], [SongID], [Score]) VALUES (10, 1, 5)
INSERT [Like] ([UserID], [SongID], [Score]) VALUES (10, 2, 5)
INSERT [Like] ([UserID], [SongID], [Score]) VALUES (10, 3, 5)
INSERT [Like] ([UserID], [SongID], [Score]) VALUES (10, 4, 5)
INSERT [Like] ([UserID], [SongID], [Score]) VALUES (10, 5, 5)
-- Шестая оценка превышает лимит
INSERT [Like] ([UserID], [SongID], [Score]) VALUES (10, 6, 5)
Msg 3609, Level 16, State 1, Line 7
The transaction ended in the trigger. The batch has been aborted.
Индивидуальные задания
Вариант 1 . Предметная область «Библиотека»
Триггер
: при удалении книги, удалять все записи, связанные с ней
Вариант 2. Предметная область «Университет»
Триггер: при добавлении нового семинара, по возможности, уменьшается число лекций по данному предмету
Вариант 3. Предметная область «Отдел продаж»
Триггер: при удалении товара отменяются все фьючерсные сделки с этим товаром.

35
Вариант 4. Предметная область «Производство»
Триггер: при удалении материала, в таблице "Спецификация изделий" ID материала меняется на ID альтернативы, если альтернативы нет, то записи удаляются.
Вариант 5. Предметная область «Кооперативы»
Триггер: при удалении/изменении размера вклада размер уставного капи- тала уменьшается на соответствующую сумму.
Вариант 6. Предметная область «Автомастерская»
Триггер
: стаж считается как число целых лет от первого ремонта до по- следнего и добавляется с каждым годом.
Вариант 7. Предметная область «Сессия»
Триггер
:
При прохождении 10 лет со дня первого экзамена группы из базы стираются все записи, связанные с ней
Вариант 8. Предметная область «Управление проектом»
Триггер:
При раздаче поручений учитывается, что сумма трудоемкостей поручений не должна превышать общей трудоемкости работы.
Вариант 9. Предметная область «Поликлиника»
Триггер: пациент в тяжелом состоянии направляется к наиболее опытному врачу.
Вариант 10. Предметная область «Сотовая связь»
Триггер
:
Количество зарегистрированных номеров не может превышать количество номеров у оператора.
Вариант 11. Предметная область «Спортивная статистика»
Триггер: исправление мирового рекорда при появлении соответствующего результата.

36
Вариант 12. Предметная область «Поставки»
Триггер: не допускать продажу просроченных продуктов
Вариант 13. Предметная область «Городской транспорт»
Триггер:
Пусть известна цена за литр топлива. Удалить все маршруты, у которых цена за проезд менее чем в 1.5 превышает расход на топливо.
И не допускать добавления таких маршрутов.
Вариант 14. Предметная область «География»
Триггер
: при изменении численности представителей национальности в одной стране изменяется общая численность для этой национально- сти.
Вариант 15. Предметная область «Домоуправление»
Триггер
: оплата не может производиться на срок более чем на 12 месяцев вперед
Контрольные вопросы
1. Объяснить принцип работы написанного триггера.
2.

Какие бывают типы триггеров?
3. Когда может срабатывать триггер?
4.

В каком порядке срабатывают триггеры?
5. Можно ли менять порядок срабатывания триггеров?
6.

Сработает ли триггер, если оператор, выполненный пользова- телем, не затрагивает ни одну строку таблицы?
7. Модифицировать триггер каким-либо образом.

37
П р а к т и ч е с к о е з а н я т и е № 5
СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ
ХРАНИМЫХ ПРОЦЕДУР
Цель работы: научиться создавать и использовать хранимые процедуры на сервере БД.
Содержание работы:
1. Проработка всех примеров
2. Выполнение всех примеров и заданий по ходу лабораторной работы.
3. Выполнение индивидуальных заданий по вариантам.
Пояснения к выполнению работы
Для освоения программирования хранимых процедур используем пример базы данных с названием DB_Books, (см. Приложение). При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта.
Хранимые процедуры представляют собой набор команд, состоя- щий из одного или нескольких операторов SQL или функций и сохра- няемый в базе данных в откомпилированном виде.
Типы хранимых процедур
Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно ска- зать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами. Системные хра- нимые процедуры имеют префикс sp, хранятся в системной базе дан- ных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных.
Вследствие этого каждая хранимая процедура располагается в кон- кретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делят-

38 ся на локальные и глобальные. Локальные временные хранимые про- цедуры могут быть вызваны только из того соединения, в котором со- зданы. При создании такой процедуры ей необходимо дать имя, начи- нающееся с одного символа #. Как и все временные объекты, храни- мые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные вре- менные хранимые процедуры доступны для любых соединений серве- ра, на котором имеется такая же процедура. Для ее определения до- статочно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при за- крытии соединения, в контексте которого они были созданы.
Создание, изменение хранимых процедур
Создание хранимой процедуры предполагает решение следую- щих задач: планирование прав доступа. При создании хранимой про- цедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь; определение параметров храни- мой процедуры, хранимые процедуры могут обла- дать входными и выходными параметрами; разработка кода хранимой процедуры. Код процедуры может содержать последовательность лю- бых команд SQL, включая вызов других хранимых процедур.
Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:
{
CREATE
|
ALTER
}
PROC
[
EDURE
]
имя_процедуры
[
;номер
]
[{
@имя_параметра тип_данных
}
[
VARYING
]
[=
DEFAULT
][
OUTPUT
]
][,...
n
]
[
WITH
{
RECOMPILE
|
ENCRYPTION
|
RECOMPILE
,
ENCRYPTION
}]
[
FOR
REPLICATION
]
AS
sql_оператор
[...
n
]
Рассмотрим параметры данной команды.
Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из син- таксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных,

39 где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необхо- димо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объек- там, расположенным в других базах данных, указание имени базы данных обязательно.
Для передачи входных и выходных данных в создаваемой храни- мой процедуре имена параметров должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, раз- деленных запятыми. В теле процедуры не должны применяться ло- кальные переменные, чьи имена совпадают с именами параметров этой процедуры. Для определения типа данных параметров хранимой процедуры подходят любые типы данных SQL, включая определен- ные пользователем. Однако тип данных CURSOR может быть исполь- зован только как выходной параметр хранимой процедуры, т.е. с ука- занием ключевого слова OUTPUT.
Наличие ключевого слова OUTPUT означает, что соответствую- щий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой проце- дуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения па- раметра. Отметим, что при указании ключевого слова OUTPUT значе- ние соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается ис- пользование любых выражений или констант, допустимое для обыч- ных параметров. Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.
Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно не указывать явно значение соответствующего параметра.
Так как сервер кэширует план исполнения запроса и компилиро- ванный код, при последующем вызове процедуры будут использо- ваться уже готовые значения. Однако в некоторых случаях все же тре- буется выполнять перекомпиляцию кода процедуры. Указание ключе-

40 вого слова RECOMPILE предписывает системе создавать план выпол- нения хранимой процедуры при каждом ее вызове.
Параметр FOR REPLICATION востребован при репликации дан- ных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспе- чить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры. Ключевое слово AS размещается в нача- ле собственно тела хранимой процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процеду- ры. Выход из хранимой процедуры можно осуществить посредством команды RETURN.
Удаление хранимой процедуры
DROP
PROCEDURE
{
имя_процедуры
}
[,...
n
]
Выполнение хранимой процедуры
Для выполнения хранимой процедуры используется команда:
[[
EXEC
[
UTE
]
имя_процедуры
[
;номер
]
[[
@имя_параметра
=]{
значение
|
@имя_переменной
}
[
OUTPUT
]|[
DEFAULT
]][,...
n
]
Если вызов хранимой процедуры не является единственной ко- мандой в пакете, то присутствие команды EXECUTE обязательно. Бо- лее того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.
Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при со- здании процедуры с ключевым словом OUTPUT.
Когда же при вызове процедуры для параметра указывается клю- чевое слово DEFAULT, то будет использовано значение по умолча- нию. Естественно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.
Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае