Метод_указания (543032), страница 6
Текст из файла (страница 6)
Если использовать только 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
Цель:
-
Практически освоить создание и использование элементарных триггеров.
-
Научиться использовать предопределенные таблицы в теле триггеров.
Справочный материал:
-
Конспект курса.
Упражнения:
Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему 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, содержащий либо неправильный код учебного заведения, либо неправильный шифр специальности, либо то и другое, например (ошибочные коды подчеркнуты):
в верхней области окна необходимо ввести:
insert poss1 values
insert poss1 values
(699991, 'ЖУРАВЛЕВ АНДРЕЙ АНДРЕЕВИЧ', '01','02','75', 'M', '0', '001991', '05', 0129999, '08', '93', '03', '98') [F5]
В нижней области окна будет выдано сообщение из триггера vuz_spec (см.рис. 34).
Рис. 34
Задание:
-
Создать триггер на проверку правильности ввода значений в одно из полей вашей информационной таблицы: «Год приема» (значения от ‘00’ до ‘99’), «Год окончания» (значения от ‘00’ до ‘99’), «Код категории обучения» (значения от ’01’ до ‘17’), «Код семейного положения» (значения от ‘0’ до ‘3’); значения этих полей должны быть заданы в отдельной таблице вашей БД;
-
Создать 3 отдельных триггера (для операторов INSERT, UPDATE и DELETE) для автоматического внесения изменений в таблицу VUZ (поля «Код вуза» и «Название вуза»), если вносятся такие же изменения в таблицу VUZ_GOROD.
ПРИЛОЖЕНИЕ I
ВАРИАНТЫ ЗАДАНИЙ НА ВЫПОЛНЕНИЕ ЛАБОРАТОРНОЙ РАБОТЫ №2
Задание 1
Выдать список студентов и аспирантов вашего государства только 1966 года рождения. Отсортировать данные по учебным заведениям и категориям обучения.
Задание 2
Выдать список выпускников 93 и 94 г.г. вашего государства по специальностям от 0401 до 0409. Отсортировать данные по специальностям и годам окончания.
Задание 3
Выдать список учащихся вашего государства, принятых на учебу в 89 и 91 г.г. только 67 года рождения. Отсортировать данные по годам приема и учебным заведениям.
Задание 4
Выдать список учащихся вашего государства по специальностям от 0601 до 0620 и только мужского пола. Отсортировать данные по специальностям и учебным заведениям.
Задание 5
Выдать список учащихся вашего государства только женского пола, незамужних и по специальности 0401. Отсортировать данные по учебным заведениям.
Задание 6
Выдать список выпускников 93 и 95 г.г. вашего государства и только 1964 года рождения. Отсортировать данные по годам окончания и учебным заведениям.
Задание 7
Выдать список студентов и стажеров вашего государства и только мужского пола. Отсортировать данные по категориям обучения и учебным заведениям.
Задание 8
Выдать список студентов вашего государства, принятых на учебу в 90 и 91 г.г. Отсортировать данные по учебным заведениям и годам приема.
Задание 9
Выдать список выпускников 94 года вашего государства по специальностям 0101, 0201 и 0401. Отсортировать данные по специальностям и учебным заведениям.
Задание 10
Выдать список учащихся вашего государства только мужского пола и с фамилиями, начинающимися на букву К. Отсортировать данные по учебным заведениям.
Задание 11
Выдать список учащихся 1965 года рождения вашего государства по специальностям от 0201 до 0230. Данные отсортировать по специальностям и учебным заведениям.
Задание 12
Выдать список учащихся вашего государства только женского пола и с именами, не заканчивающимися на букву Я. Данные отсортировать по специальностям.
Задание 13
Выдать список учащихся вашего государства, принятых на учебу в 1990 году и по всем специальностям, кроме 0101 и 0301. Данные отсортировать по учебным заведениям и специальностям.
Задание 14
Выдать список выпускников 95 и 96 г.г. вашего государства по всем годам рождения, кроме 1963 г. Данные отсортировать по годам окончания и учебным заведениям.
Задание 15
Выдать список студентов и аспирантов вашего государства и с фамилиями, начинающимися на букву М. Данные отсортировать по категориям обучения и учебным заведениям.
ПРИЛОЖЕНИЕ II
ВАРИАНТЫ ЗАДАНИЙ НА ВЫПОЛНЕНИЕ ЛАБОРАТОРНОЙ РАБОТЫ №3
Задание 1
Выдать список студентов и аспирантов вашего государства и с фамилиями, начинающимися на букву М. Отчет должен включать все поля, кроме полей НОМЕР и СЕМ_ПОЛОЖЕНИЕ. Поля КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по категориям обучения и учебным заведениям.
Задание 2
Выдать список выпускников 95 и 96 г.г. вашего государства по всем годам рождения, кроме 1963. Отчет должен включать все поля, кроме полей НОМЕР и СЕМ_ПОЛОЖЕНИЕ. Поля КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по годам окончания и учебным заведениям.
Задание 3
Выдать список учащихся вашего государства, принятых на учебу в 1990 году и по всем специальностям, кроме 0101 и 0301. Отчет должен включать все поля, кроме МЕСЯЦ_ПРИЕМА, ГОД_ПРИЕМА, НОМЕР. Поля СЕМ_ПОЛОЖЕНИЕ_К, КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по учебным заведениям и специальностям.
Задание 4
Выдать список учащихся вашего государства только женского пола и с именами, не заканчивающимися на букву А. Отчет должен включать все поля, кроме полей НОМЕР и ПОЛ. Поля СЕМ_ПОЛОЖЕНИЕ_К, ВУЗ_К и КАТ_ОБУЧЕНИЯ_К должны быть раскодированы. Данные отсортировать по специальностям.
Задание 5
Выдать список учащихся 1965 года рождения вашего государства по специальностям от 0201 до 0230. Отчет должен включать все поля, кроме полей НОМЕР и ДАТА_РОЖДЕНИЯ. Поля СЕМ_ПОЛОЖЕНИЕ_К, ВУЗ_К и КАТ_ОБУЧЕНИЯ_К должны быть раскодированы. Данные отсортировать по специальностям и учебным заведениям.
Задание 6
Выдать список учащихся вашего государства только мужского пола и с фамилиями, начинающимися на букву К. Отчет должен включать все поля, кроме полей НОМЕР и ПОЛ. Поля СЕМ_ПОЛОЖЕНИЕ_К, КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по учебным заведениям.
Задание 7
Выдать список выпускников 94 года вашего государства по специальностям 0101, 0201 и 0401. Отчет должен включать все поля, кроме полей НОМЕР, МЕСЯЦ_ОКОНЧАНИЯ и ГОД_ОКОНЧАНИЯ. Поля СЕМ_ПОЛОЖЕНИЕ_К, КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по специальностям и учебным заведениям.
Задание 8
Выдать список студентов и аспирантов вашего государства только 66 года рождения. Отчет должен включать все поля, кроме полей НОМЕР и ДАТА_РОЖДЕНИЯ. Поля СЕМ_ПОЛОЖЕНИЕ_К, КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по учебным заведениям и категориям обучения.
Задание 9
Выдать список выпускников 93 и 94 г.г. вашего государства по специальностям от 0401 до 0409. Отчет должен включать все поля, кроме полей НОМЕР и СЕМ_ПОЛОЖЕНИЕ_К. Поля КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по специальностям и годам окончания.
Задание 10
Выдать список учащихся вашего государства, принятых на учебу в 89 и 91 г.г. только 67 года рождения. Отчет должен включать все поля, кроме полей НОМЕР и ДАТА_РОЖДЕНИЯ. Поля СЕМ_ПОЛОЖЕНИЕ_К, ВУЗ_К и КАТ_ОБУЧЕНИЯ_К должны быть раскодированы. Данные отсортировать по годам приема и учебным заведениям.
Задание 11
Выдать список учащихся вашего государства по специальностям от 0601 до 0620 и только мужского пола. Отчет должен включать все поля, кроме полей НОМЕР и ПОЛ. Поля СЕМ_ПОЛОЖЕНИЕ_К, КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по специальностям и учебным заведениям.
Задание 12
Выдать список учащихся вашего государства только женского пола, незамужних и по специальности 0401. Отчет должен содержать все поля, кроме полей НОМЕР, ПОЛ, СЕМ_ПОЛОЖЕНИЕ_К и СПЕЦИАЛЬНОСТЬ. Поля КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по учебным заведениям.
Задание 13
Выдать список выпускников 93 и 95 г.г. вашего государства и только 1964 года рождения. Отчет должен включать все поля, кроме полей НОМЕР и ДАТА_РОЖДЕНИЯ. Поля СЕМ_ПОЛОЖЕНИЕ_К, КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по годам окончания и учебным заведениям.
Задание 14
Выдать список студентов и стажеров вашего государства и только мужского пола. Отчет должен включать все поля, кроме полей НОМЕР, ПОЛ и СЕМ_ПОЛОЖЕНИЕ_К. Поля КАТ_ОБУЧЕНИЯ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по категориям обучения и учебным заведениям.
Задание 15
Выдать список студентов вашего государства, принятых на учебу в 90 и 91 г.г. Отчет должен включать все поля, кроме полей НОМЕР и КАТ_ОБУЧЕНИЯ_К. Поля СЕМ_ПОЛОЖЕНИЕ_К и ВУЗ_К должны быть раскодированы. Данные отсортировать по учебным заведениям и годам приема.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
