2.23. Ход выполнения запроса

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

Для управления логикой в языке Transact-SQL есть следующие операторы:

Блок BEGIN..END этот элемент заключает последовательность операторов Transact-SQL так, что SQL Server трактует их как одну команду. Зачем это нужно? Чуть позже вы увидите, зачем это нужно.
Блок IF Условие Выражение1 ELSE Выражение2 эти элементы указывают, что SQL Server должен выполнить первое выражение, если определенное условие равно true (истина). Иначе он должен выполнить второе выражение.
Блок CASE – позволяет объединить несколько блоков сравнения в один оператор.
Конструкция WHILE выполняет операторы циклически так долго, пока определенное выражение истинно (true). Операторы BREAK и CONTINUE контролирую выполнение операторов внутри цикла WHILE.

Эти операторы являются особенностью Transact-SQL и могут не восприниматься серверами баз данных, отличных от MS SQL Server. А теперь рассмотрим все эти возможности более подробно.

2.23.1. Условный оператор IF

В главе 1, когда мы создавали и уничтожали объекты, то делали это на свой страх и риск. Допустим, что вы хотите создать новую таблицу. Если просто выполнить оператор CREATE TABLE, когда объект с таким именем уже существует, то произойдет ошибка и объект не будет создан. Чтобы объект создался, можно выполнить сначала удаление и сразу же создание. А если объект не существует? В этом случае при удалении произойдет ошибка, и сценарий снова прекратит выполнение, а оператор создания таблицы не будет выполнен.

Чтобы решить эту проблему, можно запускать с клиента два отдельных сценария. Первый будет удалять, а второй будет создавать базу. Но таким образом мы не сможем выполнить два оператора последовательно, и ошибка не будет отработана.

Намного элегантнее будет выглядеть код, который сначала проверит существование базы данных и если она есть, то удалит ее и потом создаст. Как проверить существование базы? В базе данных master есть таблица sysdatabases, в которой храниться описание всех созданных на сервере баз данных. В поле name этой таблицы находятся имена. Таким образом, следующий запрос отобразит информацию о базе данных с именем NewDB:

SELECT *
    FROM Master..sysdatabases
    WHERE Name='NewDB'

Но как теперь написать логику типа: «если запрос вернул строку, то удалить базу данных»? Для этого существует оператор IF, который имеет следующий вид:

IF Условие
    Команда 1
[ ELSE 
    Команда 2 ]

Если условие возвращает истинное значение (TRUE), то выполняется первая команда. Можно добавить оператор ELSE и вторую команду, которая будет выполняться в случае ложного результата проверки условия, но это уже не является обязательным. Оператор IF самостоятелен и используется вне запроса SELECT.

Теперь наша проблема с созданием базы данных может быть решена следующим образом:

-- Проверка существования базы
IF EXISTS(SELECT *
    FROM Master..sysdatabases
    WHERE Name='NewDB'
   )
 DROP DATABASE NewDB

-- Создаем базу данных
CREATE DATABASE NewDB

Самое интересное кроется в после оператора IF. Здесь идет проверка EXISTS, которая возвращает истинное значение, если запрос в скобках после оператора возвращает хотя бы одну строку, то есть база с именем NewDB существует. В этом случае база с таким именем будет удалена с помощью оператора DROP DATABASE. В случае ложного результата (нет записей в системной таблице), операция удаления будет пропущена и сервер сразу же перейдет к выполнению следующей строки, где происходит создание.

Давайте улучшим вывод запроса и сделаем его более информативным:

-- Проверка существования базы
IF EXISTS(SELECT *
    FROM Master..sysdatabases
    WHERE Name='NewDB'
   )
 DROP DATABASE NewDB
ELSE 
 print 'База данных NewDB не существует, создаем...'

-- Создаем базу данных
CREATE DATABASE NewDB

В этом примере, если запрос не вернул строк, и условие оказалось ложным, выполниться строка SQL кода после оператора ELSE. А давайте посмотрим на следующий пример:

IF EXISTS(SELECT *
    FROM Master..sysdatabases
    WHERE Name='NewDB'
   )
 DROP DATABASE NewDB
ELSE 
 print 'База данных NewDB не существует'
 print 'Создаем новую базу данных'

Как вы думаете, когда на экране появиться надпись «Создаем новую базу данных»? Если условие ложное? Если внимательно читать определение оператора IF, то вы увидите, что ответ лежит на поверхности – в любом случае. Это сообщение выводиться во втором операторе print, а после операторов IF и ELSE выполняется только один оператор. Это значит, что в случае ложного условия, выполниться первый print, а второй уже не относиться к условию и выполниться в любом случае, вне зависимости от условия.

Если необходимо, чтобы выполнилось два оператора, то вы должны объединить их с помощью BEGIN и END. Например:

IF Условие
 BEGIN
  -- Любое количество операторов
 END
ELSE 
 BEGIN
  -- Любое количество операторов
 END

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

IF EXISTS(SELECT *
    FROM Master..sysdatabases
    WHERE Name='NewDB'
   )
 DROP DATABASE NewDB
ELSE 
 BEGIN
  print 'База данных NewDB не существует'
  print 'Создаем новую базу данных'
 END

Рассмотрим еще один пример и заодно познакомимся с одной глобальной переменной MS SQL Server. У SQL сервера есть одна очень интересная глобальная переменная – @@ROWCOUNT. В ней храниться количество строк, которые были получены последним запросом. Давайте найдем количество работников, с отчеством Палыч и выведем на экран соответствующее сообщение о количестве:

SELECT * 
FROM tbPeoples
WHERE vcSurName LIKE '%ПАЛЫЧ'

DECLARE @rows_count int
SET @rows_count=@@ROWCOUNT

IF @rows_count=1
  print 'Один Палыч'

IF @rows_count=2
  print 'Два Палыча'

После выполнения запроса мы объявляем целочисленную переменную @rows_count и сохраняем в этой переменной значение @@ROWCOUNT. Зачем сохранять? После первого обращения глобальная эта переменная автоматически очищается. Например, посмотрим следующий запрос:

SELECT * 
FROM tbPeoples
WHERE vcSurName LIKE '%ПАЛЫЧ'
print @@ROWCOUNT
print @@ROWCOUNT

После выполнения SELECT дважды печатается значение глобальной переменной @@ROWCOUNT. В первый раз будет напечатано число 2, а после этого сразу 0, хотя других запросов не выполнялось.

Но вернемся к нашим Палычам. После сохранения количества записей в переменной @rows_count, мы сравниваем эту переменную с 1, затем с цифрой 2 и выводим на экран соответствующее сообщение.

2.23.2. Условный оператор CASE

Если нужно сделать несколько сравнений с разными значениями, то лучше всего подойдет оператор CASE, который имеет следующий вид:

CASE input_expression 
    WHEN when_expression THEN result_expression 
        [ ...n ] 
    [ 
        ELSE else_result_expression 
    ] 
END

Этот оператор должен использоваться внутри оператора SELECT. Это накладывает некоторые ограничения, но я бы даже сказал, что это делает CASE более мощным. Общий вид немного запутанный, поэтому давайте рассмотрим оператор на псевдо примере:

CASE Переменная или поле
 WHEN Значение1 THEN Действие1
 ...
 ELSE ДействиеХ
END

Итак, после CASE пишется имя поля или переменной. Затем идет оператор WHEN, который сравнивает указанное поле/переменную со значением. Если результат истина, то выполняется действие после THEN. Конструкции WHEN .. THEN может повторяться сколько угодно раз. Если ни одно из условий WHEN не выполнено, и есть оператор ELSE, то будет выполнено его действие. Конец оператора CASE - это ключевое слово END.

Необходимо также отметить, что после THEN и ELSE выполняется только одно действие. Если необходимо выполнить несколько действий, то их необходимо объединить с помощью BEGIN .. END.

Давайте рассмотрим несколько примеров. Для начала переделаем пример с Палычем с использованием CASE:

SELECT * 
FROM tbPeoples
WHERE vcSurName LIKE '%ПАЛЫЧ'

SELECT 
CASE @@ROWCOUNT
 WHEN 1 THEN 
  'Один Палыч'
 WHEN 2 THEN 
  'Два Палыча'
END

Сначала выполняется запрос на выборку всех работников с отчеством Палыч. Следующий запрос отображает результат проверки оператора CASE. В данном случае, происходит проверка значения переменной @@ROWCOUNT с разными значениями. Если глобальная переменная равна 1, то в качестве результата оператор SELECT вернет текст «Один Палыч».

Давайте посмотрим, как можно использовать CASE с полями. Следующий запрос будет выводить на экран три колонки: фамилию, имя и приветствие. Третья колонка будет содержать текст приветствия, которое будет зависеть от должности работника (содержимого поля "idPosition"):

SELECT vcFamil, vcName, 
 (CASE idPosition
  WHEN 1 THEN 
   'Здравствуйте Биг Босс'
  WHEN 2 THEN 
   'Здравствуйте Босс'
  ELSE
   'Здравствуйте'
 END) AS Приветствие
FROM tbPeoples

Если поле "idPosition" содержит число 1, то это генеральный директор и для него будет приветствие «Здравствуйте Биг Босс».

2.23.3. Оператор цикла WHILE

Допустим, что нам нужно выполнить определенную операцию несколько раз. Для этого можно несколько раз написать один и тот же запрос, но это будет достаточно неудобно и не красиво. А что если количество повторений должно равняться 100? Тиражировать такой запрос достаточно сложно. Намного лучше написать что-то типа: «выполнять запрос 100 раз». И в Transact-SQL есть такая возможность – оператор WHILE, который в общем виде выглядит следующим образом:

WHILE Boolean_expression 
    { sql_statement | statement_block } 
    [ BREAK ] 
    { sql_statement | statement_block } 
    [ CONTINUE ]

Рассмотрим один интересный пример. В таблице товары у нас находиться список покупок. Допустим, что общие затраты на покупки составили 320 руб. (по крайней мере у меня столько), но мы где-то потеряли 80 руб. и необходимо затраты подогнать под эту сумму. Для этого нужно постепенно повышать цену товаров, пока общая сумма покупок не превысит 400 руб. Такая задача достаточно просто решается с помощью оператора WHILE:

WHILE (SELECT SUM(Цена*Количество) 
       FROM Товары)<400
 UPDATE Товары SET Цена=Цена*1.2

После WHILE в скобках указан запрос на выборку суммы затрат на покупки. Результат сравнивается с цифрой 400 и если она меньше, то выполняется следующая команда (в нашем случае это UPDATE).

Увеличивать цены бесконечно невозможно, потому что иногда они превышают все возможные пределы разумности и могут стать выше даже цен самого дорогого магазина Москвы. Поэтому вполне логично было бы сделать проверку, если цена Морковки стала больше 25 руб., то увеличивать уже не стоит, потому что цены станут не реальными. Это реализовано в следующем запросе:

WHILE (SELECT SUM(Цена*Количество) 
       FROM Товары) < 1000
BEGIN
 UPDATE Товары SET Цена=Цена*1.2

 IF (SELECT Цена
     FROM Товары
     WHERE [Название товара]='Морковь') > 25
  BREAK 
END

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

В данном примере в цикле нужно выполнить уже две операции – обновить цены в таблице и проверить цену морковки, поэтому обе операции объединены в BEGIN .. END. После обновления проверяем, если цена морковки больше 25, то выполняется оператор BREAK, который прерывает выполнение цикла, даже если условие WHILE все еще не выполнено.

Итак, оператор BREAK прерывает выполнение цикла, даже если условие еще не выполнено. Таким образом, можно прерывать любой цикл.

Еще усложняем задачу. Теперь будем одновременно увеличивать и количество и цену. При этом если цена морковки превысили 25 руб., то увеличивать надо только количество:

WHILE (SELECT SUM(Цена*Количество) 
       FROM Товары) < 1000
BEGIN
 -- Обновляем количество 
 UPDATE Товары SET Количество=Количество+1

 -- Проверяем цену 
 IF (SELECT Цена
     FROM Товары
     WHERE [Название товара]='Морковь') > 25
  CONTINUE

 -- Обновляем цену
 UPDATE Товары SET Цена=Цена*1.2
END

Теперь в цикле выполняется три операции:

Первый UPDATE обновляет количество купленного товара, увеличивая его на 1;
Следующим шагом определяем сумму моркови. Если она более 25, то выполняется оператор CONTINUE, который заставляет повторить цикл сначала, без выполнения оставшихся операторов цикла;
Увеличить цену товара.

Таким образом, если цена моркови меньше 25, то произойдет обновление цены. Если больше 25, то цена обновляться не будет, а произойдет переход на начало цикла, т. е. произойдет проверка общих затрат на товары и если необходимо, содержимое цикла будет выполняться сначала. Получается, что первое обновление будет происходить в любом случае, а второе обновление (третий шаг) будет выполняться, только если цена морковки меньше 25.

2.23.4. Прерывание работы сценария

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

Для того чтобы прервать работу сценария, нужно использовать оператор RETURN. Следующий пример прерывает сценарий по определенному условию:

IF Условие
  RETURN

Рассмотрим пример, в разделе 2.23.3 мы обновляли цену моркови, но что если моркови нет в таблице? В этом случае сценарий будет работать неверно, поэтому необходимо сначала сделать проверку существования соответствующей записи и только потом что-то обновлять. Таким образом, сценарий обновления может выглядеть следующим образом:

SELECT Цена
FROM Товары
WHERE [Название товара]='Морковь'

IF @rows_count=1
  RETURN

IF (SELECT Цена
     FROM Товары
     WHERE [Название товара]='Морковь') > 25
  RETURN

-- Обновляем цену
UPDATE Товары SET Цена=Цена*1.2

Обязательно проверяйте все параметры и результаты выполнения сценариев, это позволит сделать ваши сценарии более надежными и предсказуемыми. Что значит предсказуемыми? В некоторых случаях, из-за ошибки работа может привести к тому, что сценарий будет выполняться не так, как мы ожидали и могут потеряться все данные. Например, допустим, что необходимо удалить все записи, у которых цена, как у моркови:

DECLARE @cen int

SELECT @cen=Цена
FROM Товары
WHERE [Название товара]='Морковь'

DELETE
FROM Товары
WHERE [Цена]=@cen

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

2.23.5. Подмена

Давайте вспомним запрос, в котором данные из таблицы должностей связываются сами с собой:

SELECT p1.vcPositionName AS 'Должность', 
       p2.vcPositionName AS 'Главная должность'
FROM tbPosition p1, tbPosition p2
WHERE p1.idParentPosition*=p2.idPosition

В результате мы получаем примерно следующую таблицу:

Должность                      Главная должность
-----------------------------------------------------
Генеральный директор           NULL
Коммерческий директор          Генеральный директор	
Директор по общим вопросам     Генеральный директор	
Начальник отдела снабжения     Коммерческий директор	
...

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

ISNULL(Проверяемое значение, замена)

В скобках указывается два параметра:

Поле или выражение, которое необходимо сравнить с нулевым;
Если первый параметр равен NULL, то он будет заменен на значение, указанное во втором параметре.

Итак, давайте подкорректируем наш запрос следующим образом:

SELECT p1.vcPositionName AS 'Должность', 
  ISNULL(p2.vcPositionName, 'Это биг босс') AS 'Главная должность'
FROM tbPosition p1, tbPosition p2
WHERE p1.idParentPosition*=p2.idPosition

Теперь второе поле проверяется функцией ISNULL и если значение нулевое, то оно заменяется, на текст из второго параметра. Таким образом, результат запроса будет следующим:

Должность                      Главная должность
-----------------------------------------------------
Генеральный директор           Это биг босс
Коммерческий директор          Генеральный директор	
Директор по общим вопросам     Генеральный директор	
Начальник отдела снабжения     Коммерческий директор	
...

2.23.6. Ожидание

С помощью оператора WAITFOR можно перевести запрос в режим ожидания. Допустим, что вы хотите выполнить сравнение изменения данных в таблице за час. Для этого можно выбрать необходимые данные во временную таблицу, подождать час и сравнить содержимое таблицы с выбранными ранее данными. Если не использовать WAITFOR, то придется запускать два отдельных сценария с перерывом в один час. Но благодаря WAITFOR все можно сделать автоматически без лишних трудовых затрат.

Еще пример – допустим, что необходимо выполнить один раз определенный сценарий в конце рабочего дня. Если бы это была ежедневная задача, то ее можно было заключить в работу (о работах мы будем говорить в 3-й главе), а так создавать работу не эффективно. Усложним задачу – допустим, что вы не можете дождаться конца рабочего дня и вам нужно отлучиться. Чтобы все же выполнить команду в указанное время, можно запустить задержку, которая дождется определенного времени и после этого выполнится следующий, после оператора WAITFOR код.

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

WAITFOR { DELAY 'time' | TIME 'time' }

В качестве параметра передается или время задержки (максимум 24 часа) или пока не наступит определенное время. Следующий пример показывает, как создать задержку в одну минуту:

WAITFOR DELAY '000:01:00'

SELECT *
FROM Таблица

В этом примере запускается задержка в одну минуту, после чего выполнится запрос SELECT на выборку данных.

Следующий пример создает задержку до 18:30 текущего дня:

WAITFOR TIME '18:30'

SELECT *
FROM Таблица

2.23. Ход выполнения запроса

2.23.1. Условный оператор IF

2.23.2. Условный оператор CASE

2.23.3. Оператор цикла WHILE

2.23.4. Прерывание работы сценария

2.23.5. Подмена

2.23.6. Ожидание

Предыдущая глава

Следующая глава

О блоге

Ссылки

Обратная связь