ЯЗЫК УПРАВЛЕНИЯ ПОТОКАМИ

Будучи языком запросов, 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, вычисляющую и возвращающую среднее число дней среди кортежей назначений работников этой специальности.