Практическое занятие № 9
Тема: "Создание и использование хранимых процедур в разных СУБД"
Цель работы: получить практические навыки по созданию хранимых процедур и их использованию при выполнении запросов.
Ход работы
Работа в SQL Server
Пусть имеется БД "dbase" в формате SQL Server. Данная БД состоит из 6 таблиц со следующей структурой:
Таблицы связаны так:
| Главная таблица | Подчиненная таблица | Поле для связи | Тип связи |
| otdelen | spec | id_otd | 1 : ∞ |
| spec | grupy | id_spec | 1 : ∞ |
| grupy | students | id_grup | 1 : ∞ |
| students | ocenki | id_stud | 1 : ∞ |
| predmety | ocenki | id_predmet | 1 : ∞ |
Выполните подключение БД к серверу (если она отсутствует).
Указания: разархивируйте архив.
Запустите утилиту "Microsoft SQL Server Management Studio", пройдите регистрацию введя информацию: Параметры подлинности = "Проверка подлинности SQL Server", Имя пользователя=sa, пароль=111 (у Вас может быть свой пароль).
В дереве объектов вызовите контекстное меню папки "Базы данных" и выберите команду "Присоединить". В правой части появится окно, в котором с помощью кнопки "Добавить" укажите файл из архива и щелкните ОК.
В результате у вас на сервере будет зарегистрирована новая БД.
Для создания запроса выберите в левой части нужную БД и щелкните на кнопке
"Создать запрос" 
В правой части окна
введите текст запроса.
Для выполнения запроса щелкните на кнопке "Выполнить" 
.
В нижней части окна отобразится набор отобранных данных.
На основании информации из БД создайте несколько хранимых процедур и выполните их для проверки работы.
1. Вывести фамилию и имя студентов в алфавитном порядке (процедура без параметров)
2. Вывести список студентов заданной группы в алфавитном порядке (процедура с параметром)
3. Вывести фамилии, имена и средние баллы студентов с фамилией на заданные символы (процедура с параметром)
4. Вывести список студентов, у которых средний балл выше среднего балла по учебному заведению (процедура без параметров)
5. Вывести фамилии, имя и размер стипендии студентов заданного пола и заданного года рождения (процедура с двумя параметрами)
6. Вывести фамилии, имена и размер стипендии всех студентов, при условии, что оценка по физике не учитывается (процедура без параметров)
7. Вывести список групп, в которых имеются студенты заданного пола с заданной оценкой по заданному предмету (процедура с тремя параметрами)
8. Определите, сколько студентов заданного пола останется в учебном заведении, если не учитывать тех, кто родился в заданном году.
Указания:
1. Для создания первой процедуры введите команду вида:
create procedure proc1
as
begin
select fam, imya, otchfrom students
order by fam
end
Для вызова процедуры введите команду вида:
exec proc1
2. Для создания второй процедуры введите команду вида:
create procedure proc2
-- опишем входной параметр 'Название группы'
@gr varchar(20)
as
begin
select fam, imya, otch
from grupy inner join students
on grupy.id_grup=students.id_grup
where nazv_grup like @gr+'%'
order by fam
end
Для вызова процедуры введите команду вида:
exec proc2 '38'
3. Для создания третьей процедуры введите команду вида:
create procedure proc3
-- опишем входной параметр 'Фамилия'
@f varchar(15)
as
begin
-- во временную таблицу #bufer
-- отберем средние баллы студентов
select id_stud, avg(ocenka) as srbal
into #bufer
from ocenki
group by id_stud
-- свяжем временную таблицу с таблицей students
-- и решим задачу
select fam, imya, srbal
from students inner join #bufer
on students.id_stud=#bufer.id_stud
where fam like @f+'%'
order by fam
-- удалим временную таблицу
drop table #bufer
end
Для вызова процедуры введите команду вида:
exec proc3 'Ива'
4. Для создания четвертой процедуры введите команду вида:
create procedure proc4
as
begin
-- опишем переменную и запишем в нее
-- средний балл по учебному заведению
declare @srbal decimal(7,2)
set @srbal=(select avg(srbal)
from (select id_stud, avg(ocenka) as srbal
from ocenki
group by id_stud) as a)
-- во временную таблицу отберем средние баллы студентов
select id_stud, avg(ocenka) as srbal
into #bufer
from ocenki
group by id_stud
-- свяжем временную таблицу с таблицей students
-- и найдем студентов со средним баллом выше среднего
select fam, imya
from students inner join #bufer
on students.id_stud=#bufer.id_stud
where srbal>@srbal
-- удаляем временную таблицу
drop table #bufer
end
Для вызова процедуры введите команду вида:
exec proc4
5. Для создания пятой процедуры введите команду вида:
create procedure proc5
-- опишем входные параметры 'Пол' и 'Год рождения'
@p varchar(1),
@y int
as
begin
-- во временную таблицу отберем средние баллы студентов
select id_stud, avg(ocenka) as srbal
into #bufer
from ocenki
group by id_stud
--свяжем временную таблицу с таблицей students
-- и рассчитаем стипендию
select fam, imya, case when srbal=5 then 1240
when srbal>=4 then 1100
else 0 end as stip
from students inner join #bufer
on students.id_stud=#bufer.id_stud
where pol=@p and year(datar)=@y
order by fam
-- удаляем временную таблицу
drop table #bufer
end
Для вызова процедуры введите команду вида:
exec proc5 'м', 1997
6. Для создания шестой процедуры введите команду вида:
create procedure proc6
as
begin
-- во временную таблицу отберем средние баллы студентов
-- физика не учитывается
select id_stud, avg(ocenka) as srbal
into #bufer
from ocenki inner join predmety on ocenki.id_predmet=predmety.id_predmet
where nazv_predmet <> 'физика'
group by id_stud
-- свяжем временную таблицу с таблицей students
-- и рассчитаем стипендию
select fam, imya, case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end as stip
from students inner join #bufer
on students.id_stud=#bufer.id_stud
order by fam
-- удаляем временную таблицу
drop table #bufer
end
Для вызова процедуры введите команду вида:
exec proc6
7. Для создания седьмой процедуры введите команду вида:
create procedure proc7
-- опишем входные параметры 'Пол', 'Предмет', 'Оценка'
@p varchar(1),
@pr varchar(50),
@oc int
as
begin
select nazv_grup
from grupy where exists (select *
from students inner join ocenki on students.id_stud=ocenki.id_stud
inner join predmety on ocenki.id_predmet=predmety.id_predmet
where pol=@p and nazv_predmet=@pr and ocenka=@oc
and students.id_grup=grupy.id_grup)
end
Для вызова процедуры введите команду вида:
exec proc7 'м', 'Физика', 5
8. Для создания восьмой процедуры введите команду вида:
create procedure proc8
-- опишем входной параметр 'Год'
@y int
as
begin
select count(*) as kol_stud
from students
where year(datar)<>@y
end
Для вызова процедуры введите команду вида:
exec proc8 1995
Работа в MySQL
Скачайте исходную БД в формате MySQL. Выполните все рассмотренные выше запросы в этой СУБД.
Указания: распакуйте архив. В архиве находится файл со скриптом БД.
Запустите утилиту EMS SQL Manager for MySQL. В правой части окна программы выберите команду "Выполнить SQL скрипт". В новом окне выберите команду "Выполнить скрипт из файла". В диалоговом окне укажите распакованный файл скрипта. Если появится окно регистрации, то укажите Пользовать=root, Пароль=111. Если такого окна не появилось, то нажимайте в окне ОК. Появится сообщение, что скрипт выполнен успешно и появится окно для регистрации БД. Перенесите ее в правую часть окна и нажмите ОК. В результате БД будет установлена, зарегистрирована и готова к работе.
В левой части окна можете открыть БД, просмотреть ее таблицы.
Для выполнения запросов в окне выполните команду "Инструменты - Показать редактор SQL". В результате в правой части окна откроется поле для ввода тестов запросов.
1. Для создания первой процедуры введите команду вида:
create procedure proc1()
begin
select fam, imya, otchfrom students
order by fam;
end
Для вызова процедуры введите команду вида:
call proc1();
2. Для создания второй процедуры введите команду вида:
create procedure proc2
(gr varchar(20))
begin
select fam, imya, otch
from grupy inner join students on grupy.id_grup=students.id_grup
where nazv_grup like concat(gr,'%')
order by fam;
end
Для вызова процедуры введите команду вида:
call proc2('38');
3. Для создания третьей процедуры введите команду вида:
create procedure proc3
(f varchar(15))
begin
-- во временную таблицу bufer
-- отберем средние баллы студентов
create temporary table bufer
select id_stud, avg(ocenka) as srbal
from ocenki
group by id_stud;
-- свяжем временную таблицу с таблицей students
-- и решим задачу
select fam, imya, srbal
from students inner join bufer on students.id_stud=bufer.id_stud
where fam like concat(f,'%')
order by fam;
-- удалим временную таблицу
drop table bufer;
end
Для вызова процедуры введите команду вида:
call proc3('Ива');
4. Для создания четвертой процедуры введите команду вида:
create procedure proc4()
begin
-- опишем переменную и запишем в нее
-- средний балл по учебному заведению
declare srb decimal(7,2);
select avg(srbal) into srb
from (select id_stud, avg(ocenka) as srbal
from ocenki
group by id_stud) as a;
-- во временную таблицу отберем средние баллы студентов
create temporary table bufer
select id_stud, avg(ocenka) as srbal
from ocenki
group by id_stud;
-- свяжем временную таблицу с таблицей students
-- и найдем студентов со средним баллом выше среднего
select fam, imya
from students inner join bufer on students.id_stud=bufer.id_stud
where srbal>srb;
-- удаляем временную таблицу
drop table bufer;
end
Для вызова процедуры введите команду вида:
call proc4();
5. Для создания пятой процедуры введите команду вида:
create procedure proc5
(p varchar(1),
y int)
begin
-- во временную таблицу отберем средние баллы студентов
create temporary table bufer
select id_stud, avg(ocenka) as srbal
from ocenki
group by id_stud;
-- свяжем временную таблицу с таблицей students
-- и рассчитаем стипендию
select fam, imya, case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end as stip
from students inner join bufer on students.id_stud=bufer.id_stud
where pol=p and year(datar)=y
order by fam;
-- удаляем временную таблицу
drop table bufer;
end
Для вызова процедуры введите команду вида:
call proc5('м', 1995);
6. Для создания шестой процедуры введите команду вида:
create procedure proc6()
begin
-- во временную таблицу отберем средние баллы студентов
-- физика не учитывается
create temporary table bufer
select id_stud, avg(ocenka) as srbal
from ocenki inner join predmety on ocenki.id_predmet=predmety.id_predmet
where nazv_predmet <> 'физика'
group by id_stud;
-- свяжем временную таблицу с таблицей students
-- и рассчитаем стипендию
select fam, imya, case when srbal=5 then 1240
when srbal>=4 then 1100
else 0 end as stip
from students inner join bufer on students.id_stud=bufer.id_stud
order by fam;
-- удаляем временную таблицу
drop table bufer;
end
Для вызова процедуры введите команду вида:
call proc6();
7. Для создания седьмой процедуры введите команду вида:
create procedure proc7
(p varchar(1),
pr varchar(50),
oc int)
begin
select nazv_grup
from grupy where exists (select *
from students inner join ocenki on students.id_stud=ocenki.id_stud
inner join predmety on ocenki.id_predmet=predmety.id_predmet
where pol=p and nazv_predmet=pr and ocenka=oc
and students.id_grup=grupy.id_grup);
end
Для вызова процедуры введите команду вида:
call proc7('ж', 'Физика', 5);
8. Для создания восьмой процедуры введите команду вида:
create procedure proc8
(y int)
begin
select count(*) as kol_stud
from students
where year(datar)<>y;
end
Для вызова процедуры введите команду вида:
call proc8(1995);