Метод_указания (988813), страница 6

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 6)

deallocate y /* освобождение курсора */

return [F5]

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

zapr8 [F5]

Отчет будет представлен в нижней области окна (см. рис. 29).

Рис. 29

Вариант трехмерного статистического запроса к БД

Выдать статистику по всем учебным заведениям, специальностям и категориям обучения учащихся России

Трехмерный статистический запрос выполняется аналогично двумерному, за исключением того, что в этом запросе и в списке выборки, и в предложении group by добавляется еще одно поле (атрибут "специальность"). Другими словами, в этом запросе требуется, во-первых: выдать общее количество учащихся в России; во-вторых: распределить это количество по разным учебным заведениям, т.е. определить количественные характеристики для каждого учебного заведения; в-третьих: каждую количественную характеристику каждого учебного заведения распределить по разным шифрам специальностей, т.е. определить количественные характеристики каждой специальности в каждом учебном заведении; в-четвертых: каждую количественную характеристику каждого шифра специальности (в каждом учебном заведении) распределить по разным категориям обучения, т.е. определить количественные характеристики каждой категории обучения для каждого шифра специальности (в каждом учебном заведении).

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

В качестве предложения select для курсора берется следующее предложение:

select vuz.uch_zavedenie, spec, kat_obuch.kat_obucheniya,

count(*)

from poss, vuz, kat_obuch

where vuz_k=vuz.cod and kat_obuch_k=kat_obuch.cod

group by vuz. uch_zavedenie, spec, kat_obuch. kat_obucheniya

Данный курсор позволяет получать количественные характеристики разных категорий обучения (последний атрибут в списке выборки и в предложении group by). Если суммировать эти количественные характеристики, то можно получить и количественные характеристики соответствующих шифров специальностей, и количественные характеристики соответствующих учебных заведений, и общий итог по запросу.

Для создания процедуры в верхней области окна необходимо ввести текст процедуры (команда create procedure):

create procedure zapr9 as

declare @yz char (25) /* переменная для названия учебного заведения */

declare @sp char (6) /* переменная для шифра специальности */

declare @ko char (16) /*переменная для названия категории обучения */

declare @it1 int /* переменная для итога по полю категория обучения */

declare @it2 int /* переменная для итога по полю специальность */

declare @it3 int /* переменная для итога по полю учебное заведение */

declare @itall int /* переменная для итога по всему запросу */

declare @yz1 char (25)/*переменная для названия учебного заведения */

declare @sp1 char (6) /* переменная для шифра специальности */

/* объявление курсора для трехмерного статистического запроса */

declare y cursor for

select vuz.uch_zavedenie, spec,

kat_obuch.kat_obucheniya, count (*)

from poss join vuz on vuz_k=vuz.cod

join kat_obuch on kat_obuch_k=kat_obuch.cod

group by vuz.uch_zavedenie, spec, kat_obuch. kat_obucheniya

select @it2=0 /*начальное значение итога по полю специальность = 0 */

select @it3=0 /*начальное значение итога по полю учебное заведение=0*/

select @itall=0 /*начальное значение итога по запросу = 0 */

open y /* открытие курсора */

fetch y into @yz, @sp, @ko, @it1 /* считывание первого итога по полю категория обучения */

if (@@fetch_status=-2) begin

print 'Ошибка при выполнении первого FETCH'

close y /* закрытие курсора и останов процедуры в случае ошибки */

return

end

if (@@fetch_status=-1) begin

print 'Данные не найдены'

close y /* закрытие курсора и останов процедуры в случае отсутствия данных */

return /* по запросу */

end

select @yz1=@yz/*запоминание названия учебного заведения в @yz1 */

select @sp1=@sp /* запоминание шифра специальности в @sp1 */

print @ko + ' - ' + str(@it1) /* печать названия категории обучения и итога */

select @it2=@it2+@it1 /* подсчет итога по полю специальность */

select @it3=@it3+@it1 /* подсчет итога по полю учебное заведение */

select @itall=@itall+@it1 /* подсчет общего итога по запросу */

/* цикл обработки запроса */

while (@@fetch_status=0) begin

fetch y into @yz, @sp, @ko, @it1 /* считывание очередного итога по полю категория обучения */

if (@yz1!=@yz) begin /* если название учебного заведения поменялось, то печать */

print 'учебное заведение ' + @yz1 + ' - ' + str(@it3) /* старого названия учебного заведения и итога */

select @yz1=@yz /* присвоение нового названия учебного заведения */

select @it3=0 /* новый итог = 0 */

end

if (@sp1!=@sp) begin /*если шифр специальности поменялся, то печать*/

print 'специальность ' + @sp1 + ' - ' + str(@it2) /* старого шифра специальности и итога */

select @sp1=@sp /* присвоение нового шифра специальности */

select @it2=0 /* новый итог = 0 */

end

if (@@fetch_status=-1) break /* при окончании данных немедленный выход из цикла */

print @ko + ' - ' +str(@it1) /* печать очередных названия категории обучения и итога */

select @it2=@it2+@it1 /* подсчет итога по полю специальность */

select @it3=@it3+@it1 /* подсчет итога по полю учебное заведение */

select @itall=@itall+@it1 /* подсчет общего итога по запросу */

end

close y /* закрытие курсора по окончании цикла */

if (@@fetch_status=-2) begin

print 'Ошибка при выполнении FETCH'

return /* останов процедуры в случае ошибки */

end

print 'специальность ' + @sp + ' - ' + str(@it2) /* печать последнего итога по специальности */

print 'учебное заведение ' + @yz + ' - ' + str(@it3) /* печать последнего итога по учебн. заведению */

print 'всего по запросу -'+str(@itall)/*печать общего итога по запросу */

deallocate y /* освобождение курсора */

return [F5]

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

zapr9 [F5]

Отчет будет представлен в нижней области окна (см. рис. 30).

Варианты заданий на выполнение лабораторной работы № 8 приведены в приложении V.

ЛАБОРАТОРНАЯ РАБОТА № 9

Цель:

1. Практически освоить создание и использование элементарных триггеров.

2. Научиться использовать предопределенные таблицы в теле триггеров.

Справочный материал:

1. Конспект курса.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему MS SQL Server и SQL Query Analyser, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

Рис. 30

Упражнение 1

Разработать триггер, который срабатывает при выполнении любого из трех операторов: update, delete, insert в таблице vuz, с целью выдачи сообщения о необходимости выполнения такого же оператора в таблице vuz_gorod.

Для создания триггера в верхней области окна необходимо ввести:

create trigger vuztrig1 on vuz

for insert, update, delete as

select ‘Необходимо выполнить такую же коррекцию в таблице
vuz_gorod’ [F5]

Если при наборе этой команды будет допущена синтаксическая ошибка, то в нижней области окна будет выдано сообщение об ошибке.

Для того, чтобы убедиться, что триггер создан, можно воспользоваться командой sp_help. Чтобы проверить срабатывание этого триггера, необходимо ввести один из трех операторов (update, delete, insert) для таблицы vuz, например:

в верхней области окна необходимо ввести:

insert vuz values

(7962902, 'СТАВР. МЕД. ИНСТ.') [F5]

delete vuz where cod = 7962902 [F5]

В нижней области окна будет выдано сообщение из триггера vuztrig1 (см. рис. 31).

В теле триггеров можно использовать предопределенные таблицы с именами inserted и deleted (см. конспект курса).

Рис. 31

Упражнение 2

Разработать триггер, который срабатывает при добавлении новых записей в таблицу vuz_gorod, с целью выдачи сообщения о необходимости добавления новой записи в таблицу vuz.

Для создания триггера в верхней области окна необходимо ввести:

create trigger vuztrig2 on vuz_gorod

for insert as

if (select count(*) from inserted)>0

select ‘Необходимо добавить запись в таблицу vuz’ [F5]

Чтобы проверить срабатывание этого триггера, необходимо ввести оператор insert для таблицы vuz_gorod, например:

в верхней области окна необходимо ввести:

insert vuz_gorod values

(7115842, 'С.-П. ИИЖТ', '002', 'САНКТ-ПЕТЕРБУРГ', '01', 'РОССИЯ') [F5]

В нижней области окна будет выдано сообщение из триггера vuztrig2 (см. рис. 32).

Рис. 32

Упражнение 3

Разработать триггер, который срабатывает при удалении записей из таблицы vuz_gorod, с целью выдачи сообщения о необходимости удаления старой записи из таблицы vuz.

Для создания триггера в верхней области окна необходимо ввести:

create trigger vuztrig3 on vuz_gorod

for delete as

if (select count(*) from deleted)>0

select ‘Необходимо удалить старую запись из таблицы vuz’ [F5]

Чтобы проверить срабатывание этого триггера, необходимо ввести оператор delete для таблицы vuz_gorod, например:

в верхней области окна необходимо ввести:

delete vuz_gorod

where cod_vuza=7115842 [F5]

В нижней области окна будет выдано сообщение из триггера vuztrig3 (см. рис. 33).

Упражнение 4

Разработать триггер, который срабатывает при вводе в таблицу poss1 неправильных значений в поля vuz_k (учебное заведение - код) и spec (шифр специальности), с целью выдачи сообщений об ошибках в соответствующих полях.

Таблица poss1 имеет такую же структуру, что и таблица poss (за исключением поля “дата рождения”), и предназначена для накопления новых записей с последующим их копированием в таблицу poss (в таблицу poss необходимо вводить безошибочные записи).

Для создания этого триггера необходимо создать две таблицы, каждая из которых состоит из одного поля: 1) поле, содержащее все коды учебных заведений; 2) поле, содержащее все шифры специальностей. Другими

Рис. 33

словами, должны быть созданы домены (области допустимых значений) для этих двух полей.

Для создания таблицы только кодов учебных заведений необходимо в верхней области окна ввести:

create table codv (vkod integer) [F5]

а для создания таблицы только шифров специальностей в верхней области окна необходимо ввести:

create table cods (skod char(6)) [F5]

Для загрузки этих таблиц данными необходимо использовать файлы, содержащие пакеты из команд insert типа:

insert codv values (<код учебного заведения>)...

insert cods values ('<шифр специальности>')...

После этого для создания триггера в верхней области окна необходимо ввести:

create trigger vuz_spec on poss1

for insert as

if (select count(*) from codv, inserted

where codv.vkod=inserted.vuz_k)=0

select 'значение поля vuz_k не принадлежит домену'

if (select count(*) from cods, inserted

where cods.skod=inserted.spec)=0

select 'значение поля spec не принадлежит домену' [F5]

Триггер vuz_spec сравнивает введенное значение поля vuz_k или spec со всеми значениями поля в таблице codv (всеми кодами учебных заведений) или в таблице cods (всеми шифрами специальностей) и в случаях несовпадения выдает соответствующие сообщения.

Чтобы проверить срабатывание этого триггера, необходимо ввести оператор insert для таблицы poss1, содержащий либо неправильный код учебного заведения, либо неправильный шифр специальности, либо то и другое, например (ошибочные коды подчеркнуты):

Характеристики

Список файлов учебной работы