Управляющие конструкции в языке SQL

Лабораторная работа 7 — Разработка хранимых процедур

Варианты заданий

Содержание отчета

Выполнение лабораторной работы

По аналогии с примерами, приведенными в п. 2 реализовать запросы ж).. и), указанные в варианте задания. Самостоятельно предложить и реализовать запросы, демонстрирующие использование запросов с подзапросами, описанных в п. 1.

Содержание отчета:

— текст запросов на 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

Дата добавления: 2015-05-09