- •Министерство образования и науки российской федерации
- •Лабораторная работа 1. Создание и заполнение таблиц базы данных. Часть 1.
- •Базы данных и субд
- •Таблицы
- •Типы данных sql
- •Ссылочная целостность
- •Выполнение лабораторной работы
- •Изменение данных
- •Удаление данных
- •Изменение определения таблицы
- •Выполнение лабораторной работы
- •Разработка запросов
- •Выполнение лабораторной работы
- •Директивы, используемые в условиях запросов
- •Выполнение лабораторной работы
- •Объединение, пересечение, разность запросов
- •Выполнение лабораторной работы
- •Экзистенциальные запросы
- •Выполнение лабораторной работы
- •Хранимые процедуры
- •Выполнение лабораторной работы
- •Содержание отчета
- •Варианты заданий
- •Лабораторная работа 8 — Разработка триггеров
- •Виды триггеров
- •Создание триггеров в диалекте Transact sql
- •Выполнение лабораторной работы
- •Рекомендации по выбору индексов
- •Операторы языка sql для создания и удаления индексов
- •Генерация тестовых данных
- •Анализ использования индексов
- •Выполнение лабораторной работы
- •Проблемы многопользовательского доступа к данным, их решение с помощью блокировок
- •Уровни изоляции транзакций
- •Конфигурирование блокировок, отчеты о блокировках
- •Выполнение лабораторной работы
- •Основные объекты и виды репликации
- •Организация репликации транзакций
- •Выполнение репликации транзакций
- •Выполнение лабораторной работы
- •Схемы хранилищ данных
- •Проектирование хранилища
- •Реализация хранилища
- •Выполнение лабораторной работы
- •Содержание отчета
- •Варианты заданий
- •Библиографический список
- •Содержание
Выполнение лабораторной работы
По аналогии с примерами, приведенными в п. 2 реализовать запросы ж) .. и), указанные в варианте задания. Самостоятельно предложить и реализовать запросы, демонстрирующие использование запросов с подзапросами, описанных в п. 1.
Содержание отчета
Содержание отчета:
— текст запросов на SQL;
— наборы данных, возвращаемые запросами.
Варианты заданий
Варианты заданий приведены в ПРИЛОЖЕНИИ.
Лабораторная работа 7 — Разработка хранимых процедур
Управляющие конструкции в языке SQL
Конструкции, обсуждавшиеся ранее, носили декларативный характер, они задавали критерии выполнения операций, а не алгоритм их выполнения. Вместе с тем в языке SQL присутствуют операторы, соответствующие процедурному подходу аналогичному принятому в языках программирования высокого уровня (последовательность, ветвление, цикл).
Управляющие конструкции предназначены для использования в:
— пакетных заданиях (ПЗ);
— хранимых процедурах (ХП);
— триггерах (Т).
ХП и Т будут обсуждаться ниже, ПЗ — это последовательность операторов, за которой следует директива go, передающаяся на SQL-сервер и выполняющаяся как один пакет. В качестве примера можно оформить как один пакет создание, заполнение и выборку из таблицы Дипл_с_отл:
create table Дипл_с_отл (Группа char(7), ФИО varchar(70), Ном_зач int)
insert into Дипл_с_отл (Группа, ФИО, Ном_Зач)
select Номер, ФИО, Студент.Ном_Зач
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Студент.Ном_Зач
having avg(Оценка) >= 4.75
order by Номер,ФИО
delete from Дипл_с_отл
where Ном_Зачin
(select Студент.Ном_Зач
from Студент, Успеваемость
where Студент.Ном_Зач = Успеваемость.Ном_Зач and Оценка = 3)
select * from Дипл_с_отл
go
В диалекте Transact SQL в качестве разделителя операторов используется перевод строки, MS SQL допускает так же использование в качестве разделителя точки с запятой. SQL-сервера могут накладывать ограничения на использование операторов в ПЗ, типовыми ограничениями являются:
(i) операторы create { default | function | procedure | rule | shema | trigger | view } нельзя совмещать с другими в одном ПЗ;
(ii) нельзя изменить объект и воспользоваться изменением в одном ПЗ, например следующее ПЗ приведет к ошибке:
alter table Дипл_с_отлaddСр_Баллreal
update Дипл_с_отл
set Ср_Балл =
(select avg(Оценка) from Успеваемость
where Дипл_с_отл.Ном_Зач = Успеваемость.Ном_Зач)
go
К числу управляющих операторов языка SQL относятся:
(i) составной оператор:
begin
<список операторов>
end
(ii) условный оператор:
if <условие>
< оператор>
[ else
< оператор> ]
(iii) оператор выбора:
case <исходное выражение>
when <выражение> then <результирующее выражение>
[ … ]
[ else <результирующее выражение> ]
end
или:
case
when <условное выражение> then <результирующее выражение>
[ … ]
[ else <результирующее выражение> ]
End
первый вариант синтаксиса предполагает, что значение для <исходное выражение> последовательно сравнивается с <выражение>, при совпадении значений возвращается значение для <результирующее выражение>, расположенное в соответствующей ветви when, если совпадений не найдено, выполняется ветвь else; во втором варианте синтаксиса последовательно вычисляются значения для <условное выражение>, если получена истина, возвращается значение для <результирующее выражение>, расположенное в соответствующей ветви when, если для всех <условное выражение> получено ложное значение, выполняется ветвь else;
(iv) оператор цикла:
while <условие>
< оператор>
в теле цикла могут использоваться операторы break для завершения цикла и передачи управления первому оператору за циклом, и continue для завершения текущей итерации цикла и передачи управления первому оператору в теле цикла;
(v) безусловный переход:
<метка>:
[ … ]
go to <метка>
(vi) возврат:
return [ <целочисленное выражение> ]
(vii) приостановка выполнения на заданный интервал или до наступления момента времени:
waitfor { delay <интервал> | time <время> ]
<интервал> и <время> должны относиться к типу данных datetime и могут указываться посредством локальных переменных.
В ПЗ, ХП и Т можно объявлять локальные переменные с помощью оператора:
declare <имя> <тип данных> [ , … ]
имя локальной переменной в качестве префикса должно содержать символ @. Присвоение значений локальным переменным осуществляют операторы:
set <имя> = <выражение>
select <имя> = <выражение>
второй способ белее универсален, так как некоторые SQL-сервера используют директиву set для задания свойств сервера и баз данных.
Проиллюстрируем сказанное примерами:
(i) объявление и использование переменных:
declare @x int, @y int
set @x = 1
select @y = 2
select @x + @y
go
при этом, если далее выполнить ПЗ:
select @y
go
будет получено сообщение об ошибке, так как после завершения предыдущего ПЗ переменная @y становится неопределена;
(ii) использование if — поиск претендентов на диплом с отличием:
if exists
(select *
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Студент.Ном_Зач
having avg(Оценка) >= 4.75)
begin
print 'Претенденты на диплом с отличием'
(select Номер, ФИО, Студент.Ном_Зач
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Студент.Ном_Зач
having avg(Оценка) >= 4.75)
end
else
print 'Претендентов на диплом с отличием нет'
go
(iii) использование case — оформление результатов вывода:
alter table Успеваемостьalter columnОценкаtinyint null
go
insert into Успеваемость (Ном_Зач, Оценка, Id_Дисциплины)
values (11, null, 1)
go
select Номер 'Группа', ФИО, Наименование 'Дисциплина', 'Оценка' =
caseОценка
when 5 then 'Отлично'
when 4 then 'Хорошо'
when 3 then 'Удовлетворительно'
else 'Неизвестно'
end
from Группа, Студент, Дисциплина, Успеваемость
where Группа.Id = Группа and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Дисциплина.Id = Id_Дисциплины
go
(iv) использование while — увеличение стипендий студентам на 10%, пока средняя не достигнет заданной величины:
alter table СтудентaddСтипендияsmallmoney
go
update Студент
set Стипендия = Группа * 1000
go
while (select avg(Стипендия) fromСтудент) < 10000
update Студент
set Стипендия = Стипендия + Стипендия * 0.1
go
(iv) тот же результат с помощью goto:
AddMoney:
update Студент
set Стипендия = Стипендия + Стипендия * 0.1
if (select avg(Стипендия) fromСтудент) > 10000
return
goto AddMoney
go