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