Язык управления потоками

Будучи языком запросов, SQL первоначально вспоминал для интерактивного применения довольно подготовленными пользователями, заинтересованными в немедленном получении информации посредством запросов к базе данных. Но мощность реляционного языка такова, что не нужно ограничиваться лишь таким его применением. Он имеет довольно широкие возможности, каковые возможно использовать к более классическим задачам программирования. Для этого, но, его нужно увеличить, дабы разрешить делать пара команд подряд, одну за второй, и включить в него управляющие структуры условных циклов и выражений. Такие возможности снабжает язык управления потоками.

В языке управления потоками имеется следующие элементы (представленные на рис. 2), каковые мы разглядим в данном разделе:

1. Команды BEGIN…END, задающие блоки команд SQL, разглядываемые как единое целое при исполнении.

2. Команды IF…ELSE для передачи управления в соответствии с условиям.

3. Команда WHILE для циклического исполнения команд.

4. Команды BREAK и CONTINUE, разрешающие выходить из цикла WHILE.

5. Команда DECLARE, определяющая локальные переменные.

6. Команда RETURN, разрешающая определять модули либо подпрограммы, каковые смогут вызываться вторыми модулями. (Команда RETURN употребляется с хранимыми процедурами)

7. Команда PRINT, выдающая сообщения пользователю.

8. Комментарии, разрешающие включать в программу дополнительные пояснения.

Рис. 2. Конструкции языка управления потоками

Команды BEGIN…END: блоки команд. Команды условий (IF) и циклов (WHILE) руководят исполнением одной команды либо блока команд. Блок команд обозначается следующим образом:

BEGIN

команда SQL

……………..

команда SQL

END.

Блок команд — это либо одна команда SQL, либо множество из двух либо более команд SQL, заключенных между BEGIN и END.

Команды IF…ELSE: передача управления в соответствии с условию. Команда IF в SQL Server имеет следующий синтаксис:

IF

[ELSE

].

IF-выражение. Выражение, исполнение которого зависит от истинности сформулированного условия.

Квадратные скобки, в каковые заключена ELSE-часть выражения, означают ее необязательность.

Структура данной команды не думается необыкновенной, но в действительности она такой есть. Условное выражение данной команды IF отличает ее от команд IF вторых языков программирования. В частности, условное выражение, которое должно принимать значения «истина» либо «неправда», будет довольно часто содержать подзапросы. Так, им возможно пользоваться для проверки условий, относящихся к реляционной таблице, к примеру, перед обновлением таблицы в целом. Разглядим пример.

Предположим, что мы желаем расширить на 50 центов ставки всех рабочих, в случае если средняя ставка не превышает 10 долларов в час. Тогда мы напишем такую команду:

IF (select avg (hrly_rate) from worker) 10.00

update worker

set hrly_rate = hrly_rate + .50

Мы выделили условное выражение представленной команды для дальнейших объяснений. Обратите внимание, что оно содержит подзапрос, вычисляющий среднюю ставку работников, и сравнивает итог подзапроса с величиной 10.00. Значительная отличие между этим условием и теми, которые в большинстве случаев употребляются в команде IF, пребывает в том, что в данном случае мы производим вычисление над всей реляционной таблицей worker, подсчитывая среднюю ставку, перед тем как решить, делать ли команду обновления. В большинстве случаев команда IF значения одного либо нескольких столбцов отдельного кортежа. Разрешая команде IF рассматривать таблицу в целом, мы существенно расширяем возможности языка. Разумеется, пользуясь выражением WHERE в отдельных командах SQL» мы можем так же, как и прежде принимать решения на основании значений отдельных кортежей и обновлять эти кортежи. Но сейчас у нас имеется дополнительная возможность пользоваться вычислениями над таблицами для принятия ответов.

Условное выражение, управляющее исполнением команды IF, может содержать константы, локальные переменные и подзапросы. Имена полей смогут находиться лишь в подзапросах. Каждые другие ссылки будут неоднозначными, поскольку неясно, на какой как раз кортеж производится ссылка.

Подзапрос может употребляться лишь в том случае, если перед ним стоит главное слово EXISTS (существует) либо его итог — одно значение (как в прошлом примере), и его, так, возможно сравнивать с константой либо значением переменной.

Второй пример говорит о том, что мы можем применять пара команд select в одном условном выражении. Предположим, что мы желаем повысить ставку всех штукатуров, в случае если их средняя ставка более чем на американский доллар отстает от средней ставки кровельщиков:

IF (select avg (hrly_rate) from worker

where skill_type = ‘Штукатур’)

(select avg (hrly_rate) from worker

where skill_type = ‘Кровельщик’) +1.00

update worker

set hriy_rate = hriy_rate + .50

where skill_type = ‘Штукатур’

Мы опять выделили условное выражение. Как вы видите, оно содержит два подзапроса, любой из которых выдает среднюю ставку. Между ними производится сравнение, и в случае если неравенство выполнено, то выполняется команда обновления.

В третьем примере, иллюстрирующем оператор EXISTS (существует), предположим, что мы желаем поднять ставки тем, у кого Г.Риковер является менеджером:

IF EXISTS (select * from worker where supv_id in

(select worker_id from worker

where name = ‘Г.Риковер’))

BEGIN

update worker

set hrly_rate = hrly_rate + .50

print ‘Дружно отпразднуем это!!’

END

ELSE

BEGIN

update worker

set hrly_rate = hrly_rate — .50

print ‘Увы, парни, такова жизнь!’

END

Данный пример пара сложен, так что мы остановимся и дадим некоторые дополнительные объяснения. Условие содержит запрос с подзапросом.

(select * from worker where supv_id in

(select worker_id from worker

where name = ‘Г.Риковер’))

Подзапрос

(select worker_id from worker

where name = ‘Г.Риковер’)

даст нам worker_id Г.Риковера. После этого основной запрос выберет кортежи всех работников, менеджером у которых есть Р.Риковер. Наконец, выражение полностью

EXISTS (select * from worker where supv_id in

(select worker_id from worker

where name = ‘Г.Риковер’))

будет подлинным тогда и лишь тогда, в то время, когда Г.Риковер есть менеджером кого-либо из работников, что соответствует постановке задачи. Теперь, в случае если подчиненный Г.Риковера существует, то мы поднимем ставку каждого работника на 50 центов. В другом случае мы понизим всем ставки на 50 центов.

В этом примере мы продемонстрировали использование выражения ELSE команды IF,, команды print, и создание блока команд посредством BEGIN…END. Блок, следующий за ELSE, выполняется тогда, в то время, когда условное выражение ложно. В нашем случае мы понижаем все ставки на 0.50. Команда print выдает сообщение пользователю.

Сообщение содержится в апострофы. В случае если в сообщения видится апостроф, то он обозначается двумя апострофами.

Команду IF возможно вкладывать вовнутрь второй команды IF, помещая ее по окончании IF либо ELSE. Количество вложений не ограничено.

Команды SQL используются к реляционным таблицам полностью, исходя из этого логично, что в командах IF, управляющих их исполнением, должны использоваться условия, относящиеся к таблицам полностью. Приведенные примеры иллюстрируют их использование.

Команда WHILE: циклическое исполнение.

Синтаксис команды WHILE выглядит следующим образом:

WHILE

Команда WHILE. Команда, при помощи условия задающая циклическое исполнение блока команд.

Команда WHILE задает повторное исполнение блока команд , пока условное выражение действительно. Условные выражения в данной команде такие же, как и в команде IF. Разглядим пример.

Предположим, что мы желаем удваивать оплату каждого работника , пока средняя ставка штукатуров не превысит 20 долларов. В случае если средняя оплата штукатуров уже превышает 20 американских долларов, то мы ничего не предпринимаем, в другом случае мы удваиваем каждую ставку:

WHILE (select avg (hrly_rate) from worker

where skill_type = ‘Штукатур’) 20.00

BEGIN

print ‘Мы удваиваем вашу оплату!’

update worker

set hrly_rate = 2 * hrly_rate

END

Опять, как и при команды IF, условие, управляющее исполнением цикла WHILE, содержит подзапрос, создающий вычисление над таблицей worker полностью. , пока это условие выполнено, команды внутри, цикла WHILE будут выполняться. Так, предположим, что средняя ставка штукатуров равна 4.50 американского доллара. Тогда ставка каждого работника будет удвоена и будет выведено сообщение: «Мы удваиваем вашу оплату!». Сейчас средняя ставка штукатуров равна 9 долларам. Потому, что это меньше 20 американских долларов, ставка каждого работника опять будет удвоена, и опять будет выведено сообщение. Сейчас средняя ставка штукатуров равна 18 долларам. Это снова меньше 20, исходя из этого любая ставка будет удвоена в третий и последний раз. И, очевидно, в третий раз будет выведено сообщение

По окончании того как блок команд в цикла WHILE начинает выполняться, в большинстве случаев он выполняется до конца. Кроме того в случае если условное выражение делается фальшивым перед тем, как блок команд выполнится полностью, исполнение длится. По окончании того как блок команд выполнен всецело, опять проверяется условие и, если оно выполнено, исполнение блока повторяется. В случае если условие не выполнено, то исполнение программы длится с команды, стоящей после цикла WHILE. В некоторых случаях, но, мы бы предпочли прервать исполнение цикла WHILE либо опять начать его перед тем, как блок команд закончится. Из следующего раздела вы определите, как это сделать.

Команды BREAK и CONTUNUE. Главное слово BREAK приказывает совокупности срочно выйти из цикла WHILE и начать исполнение программы с первой команды по окончании цикла WHILE. Команда CONTUNUE приказывает совокупности проигнорировать остаток блока команд, проверить истинность условного выражения и, в случае если условие выполнено, опять выполнить блок команд. Мы поясним это на подробном примере. Предположим, что мы желаем повторять блок команд , пока ставка каждого работника не превышает 40 долларов в час:

BREAK. Главное слово, передающее управление команде, расположенной по окончании цикла WHILE.

CONTUNUE. Главное слово, возвращающее управление первой команде цикла WHILE.

(I) WHILE (select max (hrly_rate) from worker) 40.00

BEGIN

(II) update worker

set hrly_rate = 1.1 * hrly_rate

(III) IF (select avg (hrly_rate) from worker) 20.00

CONTINUE

(IV) IF (select min (hrly_rate) from worker) 15.00

BREAK

(V) update worker

set hrly_rate = hrly_rate +1.00

where skill_type = ‘Штукатур’

END

(VI)….

Для простоты ссылок мы пометили серьёзные команды этого примера римскими цифрами. Цикл WHILE будет выполняться , пока максимальная ставка не превышает 40 долларов (команда (I)). Команда (II) до тех пор показывает, что мы желаем при каждом исполнении цикла повышать ставку каждого работника на 10 процентов. Выражение (III) контролирует, меньше ли средняя ставка, чем 20 долларов. В случае если это так, то выполняется команда CONTINUE, означающая, что все остальные команды цикла пропускаются и управление возвращается команде (I). В случае если средняя ставка достигла 20 долларов, выполняется выражение (IV). В случае если минимальная ставка больше 15 американских долларов, то выполняется команда BREAK и управление передается команде (VI), которая не указана. В случае если же минимальная ставка не превышает 15 американских долларов, то выполняется команда (V) и к ставке каждого штукатура прибавляется 1 американский доллар. По окончании исполнения команды (V) управление возвращается команде (I), где совокупность опять контролирует, меньше ли большая ставка, чем 40 долларов. В случае если это так, то цикл выполняется опять. В противном случае управление передается команде (VI).

Команда DECLARE и локальные переменные. Никакой язык программирования не имеет возможности обойтись без локальных переменных, в каковые помещаются временные значения. В языке SQL Server имеется возможность объявления переменных. Для переменной выбирается системный либо определенный пользователем тип. а после этого при помощи SELECT-выражений ей присваивается значение, она употребляется в вычислениях; и все это происходит в одной и той же процедуры. Разглядим пример применения локальной переменной, определяющей работу цикла WHILE.

Локальная переменная. Переменная, созданная для хранения временных размеров.

Мы определяем локальную переменную, имя которой должно начинаться с знака @:

declare @index int /* «int» свидетельствует целое число */

select @index = 3

WHILE @index 0

BEGIN

delete from worker where hrly_rate 20.00

update worker set hrly_rate = hrly_rate * 1.1

select @index = @index — 1

END

В этом примере цикл WHILE выполняется ровно три раза. Любой раз мы удаляем всех работников, получающих более 20 долларов в час, и после этого повышаем ставку всех остальных на 10 процентов. Первая строка кроме этого иллюстрирует, как вставлять в программу комментарии.

Хранимые процедуры

Хранимые процедуры — это программы SQL, скомпилированные при первом исполнении и после этого сохраненные для предстоящего применения. Они владеют определенными преимуществами перед программами, выполняемыми срочно и не предполагающими последующего применения:

1. Процедуры, каковые были скомпилированы ранее, выполняются более скоро.

2. Они смогут приобретать и возвращать параметры — переменные, используемые для передачи данных хранимым процедурам и от них — что разрешает создавать модули-утилиты либо иным методом разбивать на модули громадные и сложные программы.

Разглядим пример.

Параметр. Переменная, применяемая для передачи данных хранимой процедуре и получения данных от нее.

Предположим, что нам довольно часто приходится вычислять число работников в таблице worker. По окончании того, как мы определим локальную переменную @wo_count (число работников), мы можем написать запрос:

select @wo_count = count (*) from worker

Либо вместо этого мы можем создать процедуру:

create procedure count_workers @wo_count int output

as

select @wo_count = count (*) from worker

Первая строка этого выражения

create procedure count_workers @wo_count int output

присваивает процедуре имя, «count_workers» и сразу после этого определяет все параметры, задавая их типы данных и показывая, чем они являются — входными либо выходными параметрами. В отечественном примере

@wo_count — выходной параметр типа int (целое число). Сейчас посмотрим, как эта процедура будет трудиться.

В то время, когда процедура вызывается, то выполняется содержащийся в ней запрос

select @wo_count = count (*) from worker,

Итог помещается в выходную переменную @wo_count и возвращается вызывающей программе. Выходные параметры задаются словом «output» либо «out» по окончании типа данных. Входными параметрами будут все параметры, не помеченные как выходные. Главное слово «as» сигнализирует об начале определений определения и окончании параметров процедуры. Все, что стоит по окончании «as», образовывает выполняющуюся часть процедуры.

Сейчас разглядим, как выполняется процедура count_workers. Во-первых, мы определяем локальную переменную @worker_count. После этого мы выполняем процедуру:

declare @worker_count int

execute count_workers @worker_count output

Команда execute приведёт к выполнению ранее определенной процедуры count_workers; итог исполнения будет помещен в локальную измененийную @worker_count.

Вы имеете возможность видеть, что таким подходом пользоваться несложнее, чем каждый раз писать запрос

select @wo_count = count (*) from worker

Очевидно, преимуществ применения хранимых процедур намного больше, в то время, когда сама процедура шире и сложнее.

Во втором, более сложном примере, предположим, что мы желали бы сохранить процедуру, подсчитывающую среднюю ставку рабочих указанной профессии. Это указывает, что вызывающая процедуру программа передает ей тип профессии, а процедура возвращает величину средней ставки работников данной профессии. Мы создадим процедуру calc_wage_fcns следующим образом:

create procedure calc_wage_fcns @avg_hrly_rate money output,

@skill_type char (10)

as

select @avg_hrly_rate = avg (hrly_rate)

from worker

where skill_type = @skill_type

У данной хранимой процедуры имеется выходной параметр @avg_hrly_rate (средняя почасовая ставка) и входной — @skill_type. Вызывающая программа обязана задавать локальную переменную типа money (финансовые суммы) для фиксации выходного параметра, и значение типа профессии. Более того, в ней эти параметры должны быть указаны в том же порядке, в котором они перечислены при определений процедуры. Приведем пример вызова процедуры calc_wage_fcns, в котором все это сделано:

declare @avg_wage money

exec calc wage_fcns @avg_wage output, Штукатур

Обратите внимание, что команду «execute» возможно сократить до «ехес». Не смотря на то, что значение входного параметра (в нашем случае «Штукатур») есть символьной величиной, его не требуется заключать в кавычки, за исключением тех случаев, в то время, когда оно дополнено пробелами, содержит знаки препинания либо начинается с цифры. Процедура calc_wage_fcns подставит значение «Штукатур» в переменную @skill_type, и итог ее исполнения будет подобен результату исполнения запроса

select @avg_hrly_rate = avg (hrly_rate)

from worker

where skill_type = ‘Штукатур’.

Будет подсчитана средняя почасовая ставка штукатура, и в то время, когда значение будет возвращено вызывающей программе, оно будет помещено в переменную @avg_wage.

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

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

Значение параметра по умолчанию. Значение параметра, задаваемое совокупностью в том случае, если вызывающая программа опускает его значение.

create procedure calc_wage_fcns @avg_hrly_rate money output,

@skill_type char (10) = null

as

if @skill_type is null

select @avg_hrly_rate = avg (hrly_rate)

from worker

else

select @avg_hrly_rate = avg (hrly_rate}

from worker

where skill_type = @skill_type

Если вы сравните эту версию с прошлой версией, то заметите, что значение по умолчанию определяется сразу после задания типа данных параметра:

@skill_type char (10) = null.

Помещая «null» по окончании определения параметра, мы говорим, что в случае если никакое значение параметру не передано, то параметр считается имеющим безлюдное значение. Делаемая часть процедуры поменяна, так, дабы учитывать такую возможность — тип профессии не передан вызывающей программой.

Использование команды RETURN. В то время, когда последняя команда процедуры выполнена, процедура завершается и возвращает управление вызывающей процедуре. Как быть, в случае если логика процедуры такова, что мы желаем выйти из нее раньше? Команда RETURN обрывает исполнение хранимой процедуры и срочно возвращает управление вызывающей программе. Предположим, что мы желаем придать одной хранимой процедуре пара различных функций. К примеру, мы желаем разрешить пользователю запрашивать большую, минимальную либо среднюю почасовую ставку из таблицы worker. Процедура, которая это делает, выглядит так:

create procedure calc_wage_fcns @fcn_type char (3),

@ret_value money output

as

if @fcn_type = ‘max’

begin

select @ret_vaiue = max (hrly_rate) from worker

return

end

if @fcn_type = ‘min’

begin

select @ret_value = min (hriy_rate) from worker

return

end

if @fcn_type = ‘avg’

begin

select @ret_value = avg (hrly_rate) from worker

return

end

В этом примере вызывающей программе требуется одна из трех функций. В случае если выбрана функция «max», то мы вычисляем значение и немедленно возвращаемся в исходную программу, поскольку не желаем вычислять остальные две функции. Из этого примера нетрудно осознать, как команда RETURN может использоваться в хранимых процедурах.

Потом разглядим разработку работы с хранимыми процедурами в SQL-сервере.

Воспользуемся возможностями утилиты SQL Server Enterprise Manager.

Все хранимые процедуры в базе данных находятся в специально отведенном перечне Stored Procedures, что видим при открытии базы данных. направляться обратить внимание на список системных процедур, используемых при работе SQL-сервера, перечень которых кроме этого находится в данной группе.

Наряду с этим в колонке Type около имени процедур находится главное слово System, которое показывает принадлежность данной процедуры к группе системных процедур. С другой стороны, все процедуры, создаваемые пользователем, помечаются главным словом User в колонке Type.

Для новой процедуры выберите команду New Stored Procedures меню Action, по окончании чего на экране отобрази диалоговое окно, в котором будет расположена область для ввода текста процедуры (рис. 3).

Введем текст вышерассмотренной процедуры:

create procedure count_workers @wo_count int output

as

select @wo_count = count (*) from worker.

Следующим этапом будет проверка работоспособности созданной процедуры. Для этого запустите утилиту SQL Server Query Analyzer, по окончании чего осуществите подключение к требуемому серверу баз данных. Выберите базу данных Premier в выпадающем перечне DB, по окончании чего введите следующую SQL-команду:

ЕХЕС count_workers

Посредством SQL-команды ЕХЕС осуществляется запуск хранимых процедур, причем целый, процесс их исполнения происходит на самом компьютере — сервере. Применение хранимых процедур значительно отражается на производительности компьютеров рабочих станций. Иначе говоря эта разработка разрешает применять компьютеры рабочих станций на уровне клавиатурного ввода, а все главные задачи наряду с этим перекладываются на компьютер-сервер.

ЗАДАНИЕ.

1. Создайте команды языка управления потоками длякаждого из следующих случаев:

а. В случае если средний status (статус) строения больше 1, то расширить почасовую ставку плотников на 1 американский доллар в час.

b. В случае если менеджер 1311 имеет более 30 дней работы на различных строениях, уменьшить число дней его работы на каждом строении на 1 сутки и повторять эту операцию , пока суммарное число дней его работы не станет меньше 25 дней.

2. Создайте хранимую процедуру, приобретающую в качестве параметра skill_type, вычисляющую и возвращающую среднее число дней среди кортежей назначений работников данной профессии.

Об управлении энергоинформационными потоками.mp4


Понравилась статья? Поделиться с друзьями: