Мы познакомились с этими операторами в предыдущих лекциях. Здесь также описываются ключевые слова T-SQL, используемые для управления последовательностью выполнения операторов. Вы можете использовать эти операторы и ключевые слова в любом месте, где применяется T-SQL, – в командных строках, сценариях, хранимых процедурах, в пакетных заданиях и прикладных проблемах. В частности, мы рассмотрим операторы обработки данных INSERT, UPDATE и DELETE (см.
лекцию 13), а также программные конструкции IF...ELSE, WHILE и CASE.
Прежде чем перейти к нашей основной теме, мы создадим таблицу items для использования в наших примерах. (Мы создадим эту таблицу в базе данных MyDB.) Ниже приводятся операторы T-SQL, используемые для создания таблицы items:
USE MyDB GO CREATE TABLE items ( item_category CHAR(20) NOT NULL, item_id SMALLINT NOT NULL, price SMALLMONEY NULL, item_desc VARCHAR(30) DEFAULT 'No desc' ) GO
Колонка item_id могла бы вполне подойти для свойства IDENTITY. (См. раздел "Добавление свойства IDENTITY" в лекции 10). Но поскольку вы не можете явным образом помещать значения в такую колонку, то мы не используем здесь свойство IDENTITY. В данном случае мы будем использовать более гибкий подход в примерах, где используется оператор INSERT.
Оператор INSERT, введенный в лекции 13, используется для добавления новой строки или строк в таблицу или представление. Ниже показан INSERT:
INSERT [INTO] имя_таблицы [(список_колонок)] VALUES выражение | производная_таблица
Ключевое слово INTO и параметр список_колонок не являются обязательными. Параметр список_колонок указывает, в какие колонки вы помещаете данные; эти значения имеют взаимно-однозначное соответствие (по порядку) со значениями, указанными в выражении (которое может быть просто списком значений). Рассмотрим некоторые примеры.
В следующем примере показано, как вставить одну строку данных в таблицу items:
INSERT INTO items
(item_category, item_id, price, item_desc)
VALUES ('health food', 1, 4.00, 'tofu 6 oz.')
GO
Поскольку мы задали значения для каждой колонки данной таблицы и перечислили эти значения в порядке соответствующих колонок таблицы, то можно было бы совсем не использовать параметр список_колонок. Но если бы значения были заданы не в порядке колонок, то вы получили бы неверные данные или появилось бы сообщение об ошибке. Например, если запустить следующую последовательность операторов, то вы получите сообщение об ошибке, указанное после этой последовательности:
INSERT INTO items VALUES (1, 'health food', 4.00, 'tofu 6 oz.') GO Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'health food' to a column of data type smallint. (Синтаксическая ошибка в результате преобразования varchar-значения 'health food' в колонке данных типа smallint)
Невыполнение вставки строки и это сообщение являются следствием неверного порядка значений Мы пытались поместить значение item_id в колонку item_category и значение item_category в колонку item_id. Указанные значения несовместимы с типами данных для этих колонок. Если бы они были совместимы, то SQL Server позволил бы вставить данную строку независимо от порядка следования значений.
Чтобы увидеть, как выглядит строка, которую мы вставили в таблицу, укажите запрос выбора всех строк таблицы с помощью следующего оператора SELECT:
SELECT * from items GO Вы получите следующий набор результатов: item_category item_id price item_desc ------------------------------------------------------- health food 1 4.0000 tofu 6 oz.
При создании таблицы items было определено, что колонка price (цена) может содержать пустые значения, а для колонки item_desc (описание) было задано значение по умолчанию No desc. (Нет описания). Если в операторе INSERT не указано никакого значения для колонки price, то в эту колонку для новой строки будет помещено значение NULL. Если не указано никакого значения для колонки item_desc, то в эту колонку для новой строки будет помещено значение No desc.
В первом примере оператора INSERT в предыдущем разделе мы могли бы пропустить значения, а также имена колонок для колонок price и item_desc, поскольку для них заданы значения по умолчанию. Если пропустить значение для какой-либо колонки, то мы должны включить в список_колонок имена оставшихся колонок, иначе SQL Server сопоставит перечисленные значения с колонками в порядке, указанном при определении колонок в таблице.
Например, предположим, что мы пропустим значение колонки price и вообще не укажем список_колонок, как в следующем запросе:
INSERT INTO items
VALUES ('junk food', 2, 'fried pork skins')
GO
SQL Server попытается поместить значение, заданное для item_desc ( fried pork ; третье значение в списке), в колонку price (третья колонка в таблице). В результате появится сообщение об ошибке, поскольку fried pork – это данные типа char, в то время как для колонки price был указан тип данных smallmoney. Это несовместимые типы данных. Сообщение об ошибке будет выведено в следующей форме:
Msg 213, Level 16, State 4, Server NTSERVER, Line 1 Insert Error: Column name or number of supplied values does not match table definition. (Имя колонки или количество представленных значений не соответствуют определению таблицы)
А теперь предположим, что тип данных для значения fried pork был бы совместим с типом данных для колонки price, и представим себе, как это повлияло бы на целостность таблицы. SQL Server поместил бы это значение в неверную колонку, что привело бы к несогласованности данных в таблице.
Напомним, что значение, помещаемое в таблицу или представление, должно соответствовать типу данных, совместимому с определением соответствующей колонки. Кроме того, если вставляемая строка нарушает какое-либо правило или ограничение, то вы получаете от SQL Server сообщение об ошибке и вставка не выполняется.
Чтобы избежать ошибок, связанных с несовместимыми типами данных, указывайте имена в списке_колонок в соответствии с порядком соответствующих значений, как это показано ниже:
INSERT INTO items
(item_category, item_id, item_desc)
VALUES ('junk food', 2, 'fried pork skins')
GO
Поскольку мы не указали цену, в колонку price для данной строки будет помещено значение NULL. А теперь выполните следующий оператор SELECT:
SELECT * FROM items
Вы увидите следующий набор результатов: (в который войдут две введенные нами строки). Отметим, что в колонке price находится значение NULL.
item_category item_id price item_desc ------------------------------------------------------------------ health food 1 4.0000 tofu 6 oz. junk food 2 NULL fried pork skins
А теперь добавим другую строку, не указывая значений для колонок price и item_desc, как это показано ниже:
INSERT INTO items
(item_category, item_id)
VALUES ('toys', 3)
GO
Набор результатов отдельно для этой строки можно получить с помощью следующего запроса:
SELECT * FROM items WHERE item_id = 3
Набор результатов будет представлен в следующей форме:
item_category item_id price item_desc ---------------------------------------------------------- toys 3 NULL No desc
Отметим, что в колонках price и item_desc находятся соответственно значения NULL и No desc. Вы можете изменить эти значения с помощью оператора UPDATE, как будет показано ниже в этой лекции.
SQL Server автоматически задает значения (когда они не указаны) для четырех типов колонок: для колонок, допускающих пустые значения ( null ), для колонок с заданным значением по умолчанию, для колонок со свойством identity и колонок с временными метками ( timestamp ). Мы уже видели, что происходит с колонками первых двух типов. Колонка identity получает следующее по порядку идентифицирующее значение, в колонку временной метки заносится текущее значение временной метки. (Эти типы колонок описаны в лекции 10.) В большинстве случае вы не можете вручную помещать значения данных в эти два типа колонок.
Вы можете также вставлять строки в таблицу из другой таблицы. Для этого можно использовать производную таблицу в операторе INSERT или предложение EXECUTE с хранимой процедурой, которое возвращает строку данных.
SELECT ; встраивается в предложение FROM другого оператора T-SQL. (Подробнее см. лекцию 14.)Для выполнения вставки с помощью производной таблицы создадим сначала вторую небольшую таблицу с именем two_newest_items, в которую мы поместим строки из таблицы items. Ниже показан оператор CREATE TABLE для новой таблицы:
CREATE TABLE two_newest_items ( item_id SMALLINT NOT NULL, item_desc VARCHAR(30) DEFAULT 'No desc' ) GO
Для вставки двух последних значений из колонок item_id и item_desc таблицы items в таблицу two_newest_items используйте следующий оператор INSERT:
INSERT INTO two_newest_items
(item_id, item_desc)
SELECT TOP 2 item_id, item_desc FROM items
ORDER BY item_id DESC
GO
Отметим, что вместо списка значений в этом операторе INSERT мы использовали оператор SELECT. Этот оператор возвращает данные из существующей таблицы, и эти данные используются как список значений. Кроме того, мы не использовали круглые скобки вокруг оператора SELECT, что привело бы к синтаксической ошибке.
Для получения всех строк этой новой таблицы мы можем использовать следующий запрос:
SELECT * FROM two_newest_items
Появится следующий набор результатов:
item_id item_desc --------------------- 3 No desc 2 fried pork skins
Отметим, что мы включили в оператор INSERT предложение ORDER BY item_id DESC. Это предложение указывает SQL Server, что нужно упорядочить результаты по элементу item_id в порядке убывания.
Если создать оператор SELECT в предыдущем примере вставки в виде хранимой процедуры и использовать оператор EXECUTE с именем хранимой процедуры, то мы получим те же результаты, что и в этом примере. (Хранимые процедуры описываются в лекции 21.) Для этого мы сначала удалим с помощью оператора DELETE все существующие строки в таблице two_newest_items, чтобы можно было начать работу с пустой таблицы (см. в раздел "top_two и применим ее с оператором EXECUTE для вставки двух новых строк в таблицу two_newest_items. Для выполнения этих операций используются следующие операторы T-SQL:
DELETE FROM two_newest_items
GO
CREATE PROCEDURE top_two
AS
SELECT TOP 2 item_id, item_desc FROM items
ORDER BY item_id DESC
GO
INSERT INTO two_newest_items
(item_id, item_desc)
EXECUTE top_two
GO
Удаляются две строки, вставленные в предыдущем примере, и затем оператор INSERT помещает две новые строки (содержащие те же данные) с помощью хранимой процедуры top_two.
INSERT подсказки блокировки на уровне таблицы. Для получения более подробной информации о подсказках, которые можно использовать с оператором INSERT, щелкните на вкладке Search (Поиск) в Books Online для поиска "Locking Оператор UPDATE используется для модифицирования или обновления существующих данных. Ниже показан синтаксис оператора UPDATE:
UPDATE имя_таблицы SET имя_колонки = выражение [FROM источник_для_таблицы] WHERE условие_поиска
Основываясь на таблице items, мы обновим сначала строку
UPDATE items SET price = 2.00 WHERE item_desc = 'fried pork skins' GO Теперь выберите строку junk food с помощью следующего запроса: SELECT * FROM items WHERE item_desc = 'fried pork skins' GO
Результат для строки NULL колонки price будет заменено на 2.00:
item_category item_id price item_desc ------------------------------------------------------------- junk food 2 2.00 fried pork skins
Чтобы увеличить значение этого элемента на 10 процентов, вы можете запустить следующий оператор:
UPDATE items SET price = price * 1.10 WHERE item_desc = 'fried pork skins' GO
Теперь, выбрав строку
С помощью оператора UPDATE вы можете модифицировать более чем одну строку. Например, чтобы модифицировать все строки в таблице items, увеличив все значения колонки price на 10 процентов, запустите следующий оператор:
UPDATE items SET price = price * 1.10 GO
Теперь в случае проверки таблицы items она будет выглядеть следующим образом:
item_category item_id price item_desc ------------------------------------------------------- health food 1 4.40 tofu 6 oz. junk food 2 2.42 fried pork skins toys 3 NULL No desc
Строки со значением NULL в колонке price не затрагиваются, поскольку NULL * 1.10 = NULL. Это не является какой-либо проблемой, и вы не получите сообщения об ошибке.
Оператор UPDATE позволяет вам использовать предложение FROM для указания таблицы, которая будет использоваться как источник данных при модифицировании. В список источников таблиц могут включаться имена таблиц, имена представлений, функции rowset, производные таблицы и связанные таблицы. Источником может быть даже таблица, находящаяся в процессе модифицирования. Чтобы понять, как действует этот процесс, создадим еще одну небольшую таблицу. Ниже показаны оператор CREATE TABLE для нашей новой таблицы с именем INSERT для вставки строки со значением 5.25 в колонку tax_percent (процент налогообложения):
CREATE TABLE tax
(
tax_percent real NOT NULL,
change_date smalldatetime DEFAULT getdate()
)
GO
INSERT INTO tax
(tax_percent) VALUES (5.25)
GO
В колонку change_date (дата изменения) будут помещены текущие дата и время, полученные из ее используемой по умолчанию функции GETDATE, поскольку дата не была задана явным образом.
Теперь добавим к нашей исходной таблице items новую колонку price_with_tax (цена с налогами), допускающую пустые значения, как это показано ниже:
ALTER TABLE items ADD price_with_tax smallmoney NULL GO
Затем нам нужно модифицировать новую колонку price_with_tax, чтобы она содержала результат операции items.price * для всех строк таблицы items. Для этого используйте следующий оператор UPDATE с предложением FROM:
UPDATE items
SET price_with_tax = i.price +
(i.price * t.tax_percent / 100)
FROM items i, tax t
GO
Этот оператор UPDATE реально подходит как триггер, который будет запускаться при вставке значения в колонку price. Триггер – это специальный тип хранимой процедуры, которая автоматически выполняется при возникновении определенных условий. (О триггерах см. лекцию 22.)
Еще одним способом использования оператора UPDATE является применение производной таблицы (или FROM. Производная таблица используется как входной параметр для внешнего оператора UPDATE. Для этого примера мы будем использовать в данном подзапросе таблицу two_newest_items table и таблицу items во внешнем операторе UPDATE. Нам нужно модифицировать две последние строки в таблице items, чтобы они содержали в колонке price_with_tax значение NULL. Выполняя запрос в таблице two_newest_items, мы можем найти значения item_id для строк, которые требуется модифицировать в таблице items. Это осуществляется с помощью следующего оператора:
UPDATE items SET price_with_tax = NULL FROM (SELECT item_id FROM two_newest_items) AS t1 WHERE items.item_id = t1.item_id GO
Оператор SELECT применяется как подзапрос, результаты которого помещаются во временную производную таблицу с именем t1, которая затем используется в WHERE ). В результате 2 и 3. Таким образом, затрагиваются две строки таблицы items со значениями 2 или 3. Строка со значением item_id, равным 3, уже имеет значение NULL в колонке price_with_tax, поэтому ее значения не изменяются. А в строке со значением item_id, равным 3 vv, значение price_with_tax действительно изменяется на NULL. Набор результатов, где показаны все строки таблицы items, выглядит после этой модификации следующим образом:
item_category item_id price item_desc price_with_tax -------------------------------------------------------------------------------- health food 1 4.40 tofu 6 oz. 4.6310 junk food 2 2.42 fried pork skins NULL toys 3 NULL No desc NULL
UPDATE, таких как подсказки для таблиц и запросов, найдите в Books Online "UPDATE" и выберите подтему "Оператор DELETE используется для удаления строки или строк из таблицы или представления. DELETE не влияет на определение таблицы; он просто удаляет из таблицы строки данных. Ниже показан синтаксис для оператора DELETE:
DELETE [FROM] имя_таблицы | имя_представления [FROM источники_для_таблицы] WHERE условие_поиска
Первое ключевое слово FROM не является обязательным, как и второе предложение FROM. Строки не удаляются из источников для таблицы во втором предложении FROM ; они удаляются только из таблицы или представления, указанного после DELETE.
Используя предложение WHERE вместе с DELETE, вы можете указывать определенные строки для удаления из таблицы. Например, чтобы удалить из таблицы items все строки со значением toys в колонке item_category, выполните следующий оператор:
DELETE FROM items WHERE item_category = 'toys' GO
Этот оператор удаляет из нашей таблицы items одну строку.
Вы можете использовать второе предложение FROM с одним или несколькими источниками для таблицы, чтобы указать другие таблицы и представления, которые можно использовать в WHERE. Например, чтобы удалить из таблицы items строки, соответствующие строкам в таблице two_newest_items, выполните следующий оператор:
DELETE items FROM two_newest_items WHERE items.item_id = two_newest_items.item_id GO
Отметим, что в этом операторе мы опустили первое необязательное ключевое слово FROM. Первые две строки таблицы two_newest_items содержат в колонке item_id значения 2 и 3. Таблица items содержит в колонке item_id значения 1 и 2. Поэтому удаляется строка со значением item_id, равным 2 (соответствующая условию поиска). Процесс удаления не влияет на две строки в таблице two_newest_items (источник для таблицы).
Чтобы удалить из таблицы все строки, используйте оператор DELETE без предложения WHERE. Следующий оператор DELETE удалит все строки в таблице two_newest_items table:
DELETE FROM two_newest_items GO
Теперь two_newest_items – пустая таблица: она не содержит никаких данных. Если вы хотите также удалить определение таблицы, используйте оператор DROP TABLE, как это показано ниже. (Об этом операторе см. лекцию 15.)
DROP TABLE two_newest_items GO
DELETE, таких как использование связанных таблиц (joined tables) в качестве источников для таблицы, а также использование подсказок на уровне таблиц и запросов, найдите "DELETE" в Books Online и выберите подтему "Вместе с операторами T-SQL можно использовать несколько полезных ключевых слов, позволяющих создавать программные конструкции для управления программной последовательностью. Эти конструкции можно использовать внутри пакетов (групп операторов T-SQL, которые выполняются за один раз), хранимых процедур, сценариев и эпизодических ( ad hoc ) запросов. (Для примеров в этом разделе используется база данных pubs.)
Конструкция IF...ELSE используется для наложения условий, определяющих, какие операторы T-SQL нужно выполнить. Для IF...ELSE используется следующий синтаксис:
IF Булево_выражение Оператор_T-SQL | блок_операторов [ELSE Оператор_T-SQL | блок_операторов]
Булево_выражение возвращает значение TRUE или FALSE. Если выражение в предложении IF возвращает значение TRUE, то выполняются следующие операторы, а предложение ELSE и его операторы не выполняются. Если выражение возвращает значение FALSE, то выполняются только операторы, следующие после ключевого слова ELSE. Блок_операторов просто указывает на использование более чем одного оператора T-SQL. При использовании BEGIN и END, указывающие начало и конец каждого блока, – будь то блок в предложении IF, предложении ELSE или в обоих предложениях.
Вы можете использовать предложение IF без предложения ELSE. Рассмотрим сначала пример, где используется только IF. В следующем примере происходит проверка выражения, и если результатом этого выражения является значение TRUE, то выполняется последующий оператор PRINT:
IF (SELECT ytd_sales FROM titles
WHERE title_id = 'PC1035') > 5000
PRINT 'Year-to-date sales are
greater than $5,000 for PC1035.'
GO
Значение выражения IF будет равно TRUE, поскольку значение ytd_sales для строки с title_id = "PC1035" равно 8780. Будет выполнен оператор PRINT, и на экране будет выведен текст "Year-to-date sales are greater than $5,000 for PC1035" (Объем продаж на текущий год для PC1035 больше $5000).
А теперь добавим к предыдущему примеру предложение ELSE и изменим > 5000 на > 9000. Соответствующий пример показан ниже:
IF (SELECT ytd_sales FROM titles
WHERE title_id = 'PC1035') > 9000
PRINT 'Year-to-date sales are
greater than $9,000 for PC1035.'
ELSE
PRINT 'Year-to-date sales are
less than or equal to $9,000 for PC1035.'
GO
В данном случае будет выполнен оператор PRINT, следующий после предложения ELSE, поскольку выражение IF возвращает значение FALSE.
Расширим этот пример, добавив блоки операторов после предложений IF и ELSE. Выводимое сообщение и выполняемый запрос будут зависеть от значения выражения IF ( TRUE или FALSE ). Ниже приводится этот пример:
IF (SELECT ytd_sales FROM titles WHERE title_id = 'PC1035') > 9000
BEGIN
PRINT 'Year-to-date sales are
greater than $9,000 for PC1035.'
SELECT ytd_sales FROM titles
WHERE title_id = 'PC1035'
END
ELSE --ytd_sales должно быть <= 9000.
BEGIN
PRINT 'Year-to-date sales are
less than or equal to $9,000 for PC1035.'
SELECT price FROM titles
WHERE title_id = 'PC1035'
END
GO
Если значение выражения IF равно FALSE, то выполняются операторы между BEGIN и END в предложении ELSE. Сначала выполняется оператор PRINT и затем – оператор SELECT, показывающий, что книга стоит $22.95.
Вы можете также использовать вложенные операторы IF после предложения IF или после предложения ELSE. Например, чтобы использовать вложенные операторы IF...ELSE для определения диапазона, в который попадает среднее значение ytd_sales для всех заголовков, выполните следующий пример:
IF (SELECT avg(ytd_sales) FROM titles) < 10000
IF (SELECT avg(ytd_sales) FROM titles) < 5000
IF (SELECT avg(ytd_sales) FROM titles) < 2000
PRINT 'Average year-to-date sales are
less than $2,000.'
ELSE
PRINT 'Average year-to-date sales are
between $2,000 and $4,999.'
ELSE
PRINT 'Average year-to-date sales are
between $5,000 and $9,999.'
ELSE
PRINT 'Average year-to-date sales are greater
than $9,999.'
GO
При выполнении этого примера вы дважды увидите среди результатов следующее "Warning: Null value eliminated from . Это сообщение просто означает, что пустые значения ( null ) колонки ytd_sales не учитывались как значения при расчете среднего значения. Конечным результатом этого примера будет ", поскольку среднее значение равно $6090. Будьте аккуратны при использовании вложенных операторов IF. Можно легко запутаться в том, какой IF относится к очередному ELSE, или оставить IF без соответствующего ELSE. Использование символов табуляции для отступов, как в предыдущем примере, упрощает определение соответствующих пар IF...ELSE.
Конструкция WHILE используется для проверки условия, которое вызывает повторяющееся выполнение какого-либо оператора или TRUE. Эту конструкцию обычно называют циклом WHILE, так как операторы внутри конструкции WHILE выполняются циклическим образом. Ниже приводится синтаксис:
WHILE Булево_выражение Оператор_T-SQL | Блок_операторов [BREAK] Оператор_T-SQL | Блок_операторов [CONTINUE]
Как и в предложениях IF...ELSE, вы задаете в цикле WHILE BEGIN и END. Ключевое слово BREAK используется для выхода из цикла WHILE, после чего выполнение продолжается с оператора, следующего после конца цикла WHILE. Если цикл WHILE встроен в другие циклы WHILE, то ключевое слово BREAK вызывает выход только из того цикла WHILE, в котором оно находится; любые операторы вне этого цикла, а также внешние циклы продолжают выполняться. Ключевое слово CONTINUE в цикле указывает, что следует повторить операторы между ключевыми словами BEGIN и END в данном цикле, игнорируя любые другие операторы после CONTINUE.
Рассмотрим пример, где простой цикл WHILE используется для выполнения одного оператора UPDATE. В этом цикле WHILE проверяется условие, что среднее значение по колонке 20. Если результатом проверки является значение TRUE, то колонка WHILE проверяется снова и модификация повторяется, пока среднее значение колонки
WHILE (SELECT AVG(royalty) FROM roysched) < 20 UPDATE roysched SET royalty = royalty * 1.05 GO
Поскольку среднее значение колонки 15, этот цикл WHILE выполнится 6 раз, прежде чем среднее значение не достигнет 20 ; затем выполнение цикла прекращается, поскольку результатом проверяемого условия становится значение FALSE.
Теперь рассмотрим пример, где в цикле WHILE используются BREAK, CONTINUE, BEGIN и END. Вы будет повторять в цикле оператор UPDATE, пока среднее значение 25 процентов. Но если во время цикла максимальное значение SELECT после конца цикла WHILE. Ниже приводится соответствующая последовательность T-SQL:
WHILE (SELECT AVG(royalty) FROM roysched) < 25 BEGIN UPDATE roysched SET royalty = royalty * 1.05 IF (SELECT MAX(royalty)FROM roysched) > 27 BREAK ELSE CONTINUE END SELECT MAX(royalty) AS "MAX royalty" FROM roysched GO
Этот цикл будет выполнен только один раз, поскольку значение 27 уже имеется в этой таблице. Оператор UPDATE все же выполняется один раз, так как среднее значение меньше 25 процентов. Затем проверяется условие оператора IF, и результатом является значение TRUE, поэтому выполняется оператор BREAK, вызывающий выход из цикла WHILE. Выполнение программы затем продолжается, начиная с оператора, следующего за ключевым словом END (последний оператор SELECT ).
Напомним, что вы можете также использовать вложенные циклы WHILE, но следует учитывать, что ключевое слово BREAK или CONTINUE применяется только к циклу, из которого оно было вызвано, но не к внешним циклам WHILE.
Ключевое слово CASE используется для оценки списка условий и возврата одного из нескольких возможных результатов. Возвращаемый результат зависит от того, какое условие совпадает с другим указанным условием или является истинным. Наиболее распространенным применением CASE является замена кодового или сокращенного значения на более понятное значение и упорядочивание значений, как будет показано в наших примерах этого раздела. Имеется два формата для конструкции CASE: простой и поисковый. В простом формате на входе после CASE задается в виде выражения значение, которое проверяется на равенство со значением в выражении или выражениях WHEN. В поисковом формате происходит проверка булева выражения на значение TRUE или FALSE, а не проверка на равенство с каким-либо значением. Сначала рассмотрим простой формат. В простом формате предложение CASE имеет следующий синтаксис:
CASE входное_выражение WHEN выражение_для_when THEN результирующее_выражение [WHEN выражение_для_when THEN результирующее_выражение...n] [ELSE выражение_для_else] END
Значение результирующего выражения возвращается в том случае, если значение соответствующего выражения WHEN равно значению входного выражения. Выражения сравниваются в порядке их следования в предложении CASE. Если не обнаружено ни одного совпадения, то возвращается значение результирующего выражения ELSE (если оно задано); в противном случае возвращается значение NULL. Отметим, что в простом формате значение входного выражения CASE и значение выражения WHEN должны иметь одинаковый тип данных или допускать
В следующем примере используется простой формат предложения CASE внутри оператора SELECT. Колонка payterms (сроки платежей) таблицы sales (продажи) содержит одно из следующих значений для каждой строки: Net 30, Net 60, On invoice или None. С помощью следующего оператора T-SQL в колонке payterms можно выводить на экран альтернативные (более понятные) значения:
SELECT 'Payment Terms' = (сроки платежей)
CASE payterms
WHEN 'Net 30' THEN 'Payable 30 days --к оплате в течение 30 дней
after invoice' --после получения счета-фактуры
WHEN 'Net 60' THEN 'Payable 60 days --к оплате в течение 60 дней
after invoice' --после получения счета-фактуры
WHEN 'On invoice' THEN 'Payable upon --к оплате по
receipt of invoice' --получении счета-фактуры
ELSE 'None'
END,
title_id
FROM sales
ORDER BY payterms
GO
В этом предложении CASE проверяется значение payterms для каждой строки, указанной в операторе SELECT. Значение результирующего выражения возвращается в том случае, если значение выражения WHEN равно значению колонки payterms. Результаты предложения CASE появляются в колонке
Payment Terms title_id ---------------------------------------------------- Payable 30 days after invoice PC8888 Payable 30 days after invoice TC3218 Payable 30 days after invoice TC4203 Payable 30 days after invoice TC7777 Payable 30 days after invoice PS2091 Payable 30 days after invoice MC3021 Payable 30 days after invoice BU1111 Payable 30 days after invoice PC1035 Payable 60 days after invoice PS1372 Payable 60 days after invoice PS2106 Payable 60 days after invoice PS3333 Payable 60 days after invoice PS7777 Payable 60 days after invoice BU7832 Payable 60 days after invoice MC2222 Payable 60 days after invoice PS2091 Payable 60 days after invoice BU1032 Payable 60 days after invoice PS2091 Payable upon receipt of invoice PS2091 Payable upon receipt of invoice BU1032 Payable upon receipt of invoice BU2075 Payable upon receipt of invoice MC3021 (21 row(s) affected)
А теперь рассмотрим второй формат предложения CASE – поисковый формат. В этом формате предложение CASE имеет следующий синтаксис:
CASE WHEN Булево_выражение THEN результирующее_выражение [WHEN Булево_выражение THEN результирующее_выражение...n] [ELSE результирующее_выражение_для_else] END
Предложение CASE в поисковом формате отличается от CASE в простом формате тем, что в поисковом формате после ключевого слова CASE нет входного выражения, а после ключевых слов WHEN следуют булевы выражения, которые проверяются на значение TRUE или FALSE (а не на равенство). В поисковом формате предложение CASE проверяет значения булевых выражений и выводит значение результирующего выражения для первого булева выражения, возвращающего значение TRUE. (Выражения проверяются в порядке их следования.)
Например, предложение CASE внутри следующего оператора SELECT проверяет значение колонки price (цена) каждой строки и возвращает символьную строку, соответствующую диапазону цен (Price Range), в который попадает цена данной книги:
SELECT 'Price Range' =
CASE
WHEN price BETWEEN .01 AND 10.00
THEN 'Inexpensive: $10.00 or less' --дешевые
WHEN price BETWEEN 10.01 AND 20.00
THEN 'Moderate: $10.01 to $20.00' --умеренные
WHEN price BETWEEN 20.01 AND 30.00
THEN 'Semi-expensive: $20.01 to $30.00' --не слишком дорогие
WHEN price BETWEEN 30.01 AND 50.00
THEN 'Expensive: $30.01 to $50.00' --дорогие
WHEN price IS NULL
THEN 'No price listed' --цена не указана
ELSE 'Very expensive!' --очень дорогие
END,
title_id
FROM titles
ORDER BY price
GO
Результирующий набор имеет следующий вид:
Price Range title_id ———————————————— ———— No price listed MC3026 No price listed PC9999 Inexpensive: $10.00 or less MC3021 Inexpensive: $10.00 or less BU2075 Inexpensive: $10.00 or less PS2106 Inexpensive: $10.00 or less PS7777 Moderate: $10.01 to $20.00 PS2091 Moderate: $10.01 to $20.00 BU1111 Moderate: $10.01 to $20.00 TC4203 Moderate: $10.01 to $20.00 TC7777 Moderate: $10.01 to $20.00 BU1032 Moderate: $10.01 to $20.00 BU7832 Moderate: $10.01 to $20.00 MC2222 Moderate: $10.01 to $20.00 PS3333 Moderate: $10.01 to $20.00 PC8888 Semi-expensive: $20.01 to $30.00 TC3218 Semi-expensive: $20.01 to $30.00 PS1372 Semi-expensive: $20.01 to $30.00 PC1035 (18 row(s) affected)
CASE мы ставили запятую после ключевого слова END, поскольку все предложение CASE было использовано как часть списка_колонок в предложении SELECT вместе с title_id. Иными словами, все предложение CASE было просто входом в список_колонок. Это наиболее распространенное применение ключевого слова CASE.Ниже приводятся другие ключевые слова, которые можно использовать для управления программной последовательностью:
GOTO метка. Выполняет передачу управления на метку, определенную в GOTO.RETURN. Безусловный выход из запроса или процедуры.WAITFOR. Задает задержку или определенное время для выполнения оператора.В этой лекции мы рассмотрели использование операторов T-SQL INSERT, UPDATE и DELETE. Мы также рассмотрели ключевые слова T-SQL IF, ELSE, WHILE, BEGIN, END и CASE, которые используются для управления программной последовательностью. В лекции 21 вы узнаете, как создавать хранимые процедуры, в которых вы можете использовать эти операторы и конструкции.
Мы познакомились с этими операторами в предыдущих лекциях. Здесь также описываются ключевые слова T-SQL, используемые для управления последовательностью выполнения операторов. Вы можете использовать эти операторы и ключевые слова в любом месте, где применяется T-SQL, – в командных строках, сценариях, хранимых процедурах, в пакетных заданиях и прикладных проблемах. В частности, мы рассмотрим операторы обработки данных INSERT, UPDATE и DELETE (см.
лекцию 13), а также программные конструкции IF...ELSE, WHILE и CASE.
Прежде чем перейти к нашей основной теме, мы создадим таблицу items для использования в наших примерах. (Мы создадим эту таблицу в базе данных MyDB.) Ниже приводятся операторы T-SQL, используемые для создания таблицы items:
USE MyDB GO CREATE TABLE items ( item_category CHAR(20) NOT NULL, item_id SMALLINT NOT NULL, price SMALLMONEY NULL, item_desc VARCHAR(30) DEFAULT 'No desc' ) GO
Колонка item_id могла бы вполне подойти для свойства IDENTITY. (См. раздел "Добавление свойства IDENTITY" в лекции 10). Но поскольку вы не можете явным образом помещать значения в такую колонку, то мы не используем здесь свойство IDENTITY. В данном случае мы будем использовать более гибкий подход в примерах, где используется оператор INSERT.
Оператор INSERT, введенный в лекции 13, используется для добавления новой строки или строк в таблицу или представление. Ниже показан INSERT:
INSERT [INTO] имя_таблицы [(список_колонок)] VALUES выражение | производная_таблица
Ключевое слово INTO и параметр список_колонок не являются обязательными. Параметр список_колонок указывает, в какие колонки вы помещаете данные; эти значения имеют взаимно-однозначное соответствие (по порядку) со значениями, указанными в выражении (которое может быть просто списком значений). Рассмотрим некоторые примеры.
В следующем примере показано, как вставить одну строку данных в таблицу items:
INSERT INTO items
(item_category, item_id, price, item_desc)
VALUES ('health food', 1, 4.00, 'tofu 6 oz.')
GO
Поскольку мы задали значения для каждой колонки данной таблицы и перечислили эти значения в порядке соответствующих колонок таблицы, то можно было бы совсем не использовать параметр список_колонок. Но если бы значения были заданы не в порядке колонок, то вы получили бы неверные данные или появилось бы сообщение об ошибке. Например, если запустить следующую последовательность операторов, то вы получите сообщение об ошибке, указанное после этой последовательности:
INSERT INTO items VALUES (1, 'health food', 4.00, 'tofu 6 oz.') GO Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'health food' to a column of data type smallint. (Синтаксическая ошибка в результате преобразования varchar-значения 'health food' в колонке данных типа smallint)
Невыполнение вставки строки и это сообщение являются следствием неверного порядка значений Мы пытались поместить значение item_id в колонку item_category и значение item_category в колонку item_id. Указанные значения несовместимы с типами данных для этих колонок. Если бы они были совместимы, то SQL Server позволил бы вставить данную строку независимо от порядка следования значений.
Чтобы увидеть, как выглядит строка, которую мы вставили в таблицу, укажите запрос выбора всех строк таблицы с помощью следующего оператора SELECT:
SELECT * from items GO Вы получите следующий набор результатов: item_category item_id price item_desc ------------------------------------------------------- health food 1 4.0000 tofu 6 oz.
При создании таблицы items было определено, что колонка price (цена) может содержать пустые значения, а для колонки item_desc (описание) было задано значение по умолчанию No desc. (Нет описания). Если в операторе INSERT не указано никакого значения для колонки price, то в эту колонку для новой строки будет помещено значение NULL. Если не указано никакого значения для колонки item_desc, то в эту колонку для новой строки будет помещено значение No desc.
В первом примере оператора INSERT в предыдущем разделе мы могли бы пропустить значения, а также имена колонок для колонок price и item_desc, поскольку для них заданы значения по умолчанию. Если пропустить значение для какой-либо колонки, то мы должны включить в список_колонок имена оставшихся колонок, иначе SQL Server сопоставит перечисленные значения с колонками в порядке, указанном при определении колонок в таблице.
Например, предположим, что мы пропустим значение колонки price и вообще не укажем список_колонок, как в следующем запросе:
INSERT INTO items
VALUES ('junk food', 2, 'fried pork skins')
GO
SQL Server попытается поместить значение, заданное для item_desc ( fried pork ; третье значение в списке), в колонку price (третья колонка в таблице). В результате появится сообщение об ошибке, поскольку fried pork – это данные типа char, в то время как для колонки price был указан тип данных smallmoney. Это несовместимые типы данных. Сообщение об ошибке будет выведено в следующей форме:
Msg 213, Level 16, State 4, Server NTSERVER, Line 1 Insert Error: Column name or number of supplied values does not match table definition. (Имя колонки или количество представленных значений не соответствуют определению таблицы)
А теперь предположим, что тип данных для значения fried pork был бы совместим с типом данных для колонки price, и представим себе, как это повлияло бы на целостность таблицы. SQL Server поместил бы это значение в неверную колонку, что привело бы к несогласованности данных в таблице.
Напомним, что значение, помещаемое в таблицу или представление, должно соответствовать типу данных, совместимому с определением соответствующей колонки. Кроме того, если вставляемая строка нарушает какое-либо правило или ограничение, то вы получаете от SQL Server сообщение об ошибке и вставка не выполняется.
Чтобы избежать ошибок, связанных с несовместимыми типами данных, указывайте имена в списке_колонок в соответствии с порядком соответствующих значений, как это показано ниже:
INSERT INTO items
(item_category, item_id, item_desc)
VALUES ('junk food', 2, 'fried pork skins')
GO
Поскольку мы не указали цену, в колонку price для данной строки будет помещено значение NULL. А теперь выполните следующий оператор SELECT:
SELECT * FROM items
Вы увидите следующий набор результатов: (в который войдут две введенные нами строки). Отметим, что в колонке price находится значение NULL.
item_category item_id price item_desc ------------------------------------------------------------------ health food 1 4.0000 tofu 6 oz. junk food 2 NULL fried pork skins
А теперь добавим другую строку, не указывая значений для колонок price и item_desc, как это показано ниже:
INSERT INTO items
(item_category, item_id)
VALUES ('toys', 3)
GO
Набор результатов отдельно для этой строки можно получить с помощью следующего запроса:
SELECT * FROM items WHERE item_id = 3
Набор результатов будет представлен в следующей форме:
item_category item_id price item_desc ---------------------------------------------------------- toys 3 NULL No desc
Отметим, что в колонках price и item_desc находятся соответственно значения NULL и No desc. Вы можете изменить эти значения с помощью оператора UPDATE, как будет показано ниже в этой лекции.
SQL Server автоматически задает значения (когда они не указаны) для четырех типов колонок: для колонок, допускающих пустые значения ( null ), для колонок с заданным значением по умолчанию, для колонок со свойством identity и колонок с временными метками ( timestamp ). Мы уже видели, что происходит с колонками первых двух типов. Колонка identity получает следующее по порядку идентифицирующее значение, в колонку временной метки заносится текущее значение временной метки. (Эти типы колонок описаны в лекции 10.) В большинстве случае вы не можете вручную помещать значения данных в эти два типа колонок.
Вы можете также вставлять строки в таблицу из другой таблицы. Для этого можно использовать производную таблицу в операторе INSERT или предложение EXECUTE с хранимой процедурой, которое возвращает строку данных.
SELECT ; встраивается в предложение FROM другого оператора T-SQL. (Подробнее см. лекцию 14.)Для выполнения вставки с помощью производной таблицы создадим сначала вторую небольшую таблицу с именем two_newest_items, в которую мы поместим строки из таблицы items. Ниже показан оператор CREATE TABLE для новой таблицы:
CREATE TABLE two_newest_items ( item_id SMALLINT NOT NULL, item_desc VARCHAR(30) DEFAULT 'No desc' ) GO
Для вставки двух последних значений из колонок item_id и item_desc таблицы items в таблицу two_newest_items используйте следующий оператор INSERT:
INSERT INTO two_newest_items
(item_id, item_desc)
SELECT TOP 2 item_id, item_desc FROM items
ORDER BY item_id DESC
GO
Отметим, что вместо списка значений в этом операторе INSERT мы использовали оператор SELECT. Этот оператор возвращает данные из существующей таблицы, и эти данные используются как список значений. Кроме того, мы не использовали круглые скобки вокруг оператора SELECT, что привело бы к синтаксической ошибке.
Для получения всех строк этой новой таблицы мы можем использовать следующий запрос:
SELECT * FROM two_newest_items
Появится следующий набор результатов:
item_id item_desc --------------------- 3 No desc 2 fried pork skins
Отметим, что мы включили в оператор INSERT предложение ORDER BY item_id DESC. Это предложение указывает SQL Server, что нужно упорядочить результаты по элементу item_id в порядке убывания.
Если создать оператор SELECT в предыдущем примере вставки в виде хранимой процедуры и использовать оператор EXECUTE с именем хранимой процедуры, то мы получим те же результаты, что и в этом примере. (Хранимые процедуры описываются в лекции 21.) Для этого мы сначала удалим с помощью оператора DELETE все существующие строки в таблице two_newest_items, чтобы можно было начать работу с пустой таблицы (см. в раздел "top_two и применим ее с оператором EXECUTE для вставки двух новых строк в таблицу two_newest_items. Для выполнения этих операций используются следующие операторы T-SQL:
DELETE FROM two_newest_items
GO
CREATE PROCEDURE top_two
AS
SELECT TOP 2 item_id, item_desc FROM items
ORDER BY item_id DESC
GO
INSERT INTO two_newest_items
(item_id, item_desc)
EXECUTE top_two
GO
Удаляются две строки, вставленные в предыдущем примере, и затем оператор INSERT помещает две новые строки (содержащие те же данные) с помощью хранимой процедуры top_two.
INSERT подсказки блокировки на уровне таблицы. Для получения более подробной информации о подсказках, которые можно использовать с оператором INSERT, щелкните на вкладке Search (Поиск) в Books Online для поиска "Locking Оператор UPDATE используется для модифицирования или обновления существующих данных. Ниже показан синтаксис оператора UPDATE:
UPDATE имя_таблицы SET имя_колонки = выражение [FROM источник_для_таблицы] WHERE условие_поиска
Основываясь на таблице items, мы обновим сначала строку
UPDATE items SET price = 2.00 WHERE item_desc = 'fried pork skins' GO Теперь выберите строку junk food с помощью следующего запроса: SELECT * FROM items WHERE item_desc = 'fried pork skins' GO
Результат для строки NULL колонки price будет заменено на 2.00:
item_category item_id price item_desc ------------------------------------------------------------- junk food 2 2.00 fried pork skins
Чтобы увеличить значение этого элемента на 10 процентов, вы можете запустить следующий оператор:
UPDATE items SET price = price * 1.10 WHERE item_desc = 'fried pork skins' GO
Теперь, выбрав строку
С помощью оператора UPDATE вы можете модифицировать более чем одну строку. Например, чтобы модифицировать все строки в таблице items, увеличив все значения колонки price на 10 процентов, запустите следующий оператор:
UPDATE items SET price = price * 1.10 GO
Теперь в случае проверки таблицы items она будет выглядеть следующим образом:
item_category item_id price item_desc ------------------------------------------------------- health food 1 4.40 tofu 6 oz. junk food 2 2.42 fried pork skins toys 3 NULL No desc
Строки со значением NULL в колонке price не затрагиваются, поскольку NULL * 1.10 = NULL. Это не является какой-либо проблемой, и вы не получите сообщения об ошибке.
Оператор UPDATE позволяет вам использовать предложение FROM для указания таблицы, которая будет использоваться как источник данных при модифицировании. В список источников таблиц могут включаться имена таблиц, имена представлений, функции rowset, производные таблицы и связанные таблицы. Источником может быть даже таблица, находящаяся в процессе модифицирования. Чтобы понять, как действует этот процесс, создадим еще одну небольшую таблицу. Ниже показаны оператор CREATE TABLE для нашей новой таблицы с именем INSERT для вставки строки со значением 5.25 в колонку tax_percent (процент налогообложения):
CREATE TABLE tax
(
tax_percent real NOT NULL,
change_date smalldatetime DEFAULT getdate()
)
GO
INSERT INTO tax
(tax_percent) VALUES (5.25)
GO
В колонку change_date (дата изменения) будут помещены текущие дата и время, полученные из ее используемой по умолчанию функции GETDATE, поскольку дата не была задана явным образом.
Теперь добавим к нашей исходной таблице items новую колонку price_with_tax (цена с налогами), допускающую пустые значения, как это показано ниже:
ALTER TABLE items ADD price_with_tax smallmoney NULL GO
Затем нам нужно модифицировать новую колонку price_with_tax, чтобы она содержала результат операции items.price * для всех строк таблицы items. Для этого используйте следующий оператор UPDATE с предложением FROM:
UPDATE items
SET price_with_tax = i.price +
(i.price * t.tax_percent / 100)
FROM items i, tax t
GO
Этот оператор UPDATE реально подходит как триггер, который будет запускаться при вставке значения в колонку price. Триггер – это специальный тип хранимой процедуры, которая автоматически выполняется при возникновении определенных условий. (О триггерах см. лекцию 22.)
Еще одним способом использования оператора UPDATE является применение производной таблицы (или FROM. Производная таблица используется как входной параметр для внешнего оператора UPDATE. Для этого примера мы будем использовать в данном подзапросе таблицу two_newest_items table и таблицу items во внешнем операторе UPDATE. Нам нужно модифицировать две последние строки в таблице items, чтобы они содержали в колонке price_with_tax значение NULL. Выполняя запрос в таблице two_newest_items, мы можем найти значения item_id для строк, которые требуется модифицировать в таблице items. Это осуществляется с помощью следующего оператора:
UPDATE items SET price_with_tax = NULL FROM (SELECT item_id FROM two_newest_items) AS t1 WHERE items.item_id = t1.item_id GO
Оператор SELECT применяется как подзапрос, результаты которого помещаются во временную производную таблицу с именем t1, которая затем используется в WHERE ). В результате 2 и 3. Таким образом, затрагиваются две строки таблицы items со значениями 2 или 3. Строка со значением item_id, равным 3, уже имеет значение NULL в колонке price_with_tax, поэтому ее значения не изменяются. А в строке со значением item_id, равным 3 vv, значение price_with_tax действительно изменяется на NULL. Набор результатов, где показаны все строки таблицы items, выглядит после этой модификации следующим образом:
item_category item_id price item_desc price_with_tax -------------------------------------------------------------------------------- health food 1 4.40 tofu 6 oz. 4.6310 junk food 2 2.42 fried pork skins NULL toys 3 NULL No desc NULL
UPDATE, таких как подсказки для таблиц и запросов, найдите в Books Online "UPDATE" и выберите подтему "Оператор DELETE используется для удаления строки или строк из таблицы или представления. DELETE не влияет на определение таблицы; он просто удаляет из таблицы строки данных. Ниже показан синтаксис для оператора DELETE:
DELETE [FROM] имя_таблицы | имя_представления [FROM источники_для_таблицы] WHERE условие_поиска
Первое ключевое слово FROM не является обязательным, как и второе предложение FROM. Строки не удаляются из источников для таблицы во втором предложении FROM ; они удаляются только из таблицы или представления, указанного после DELETE.
Используя предложение WHERE вместе с DELETE, вы можете указывать определенные строки для удаления из таблицы. Например, чтобы удалить из таблицы items все строки со значением toys в колонке item_category, выполните следующий оператор:
DELETE FROM items WHERE item_category = 'toys' GO
Этот оператор удаляет из нашей таблицы items одну строку.
Вы можете использовать второе предложение FROM с одним или несколькими источниками для таблицы, чтобы указать другие таблицы и представления, которые можно использовать в WHERE. Например, чтобы удалить из таблицы items строки, соответствующие строкам в таблице two_newest_items, выполните следующий оператор:
DELETE items FROM two_newest_items WHERE items.item_id = two_newest_items.item_id GO
Отметим, что в этом операторе мы опустили первое необязательное ключевое слово FROM. Первые две строки таблицы two_newest_items содержат в колонке item_id значения 2 и 3. Таблица items содержит в колонке item_id значения 1 и 2. Поэтому удаляется строка со значением item_id, равным 2 (соответствующая условию поиска). Процесс удаления не влияет на две строки в таблице two_newest_items (источник для таблицы).
Чтобы удалить из таблицы все строки, используйте оператор DELETE без предложения WHERE. Следующий оператор DELETE удалит все строки в таблице two_newest_items table:
DELETE FROM two_newest_items GO
Теперь two_newest_items – пустая таблица: она не содержит никаких данных. Если вы хотите также удалить определение таблицы, используйте оператор DROP TABLE, как это показано ниже. (Об этом операторе см. лекцию 15.)
DROP TABLE two_newest_items GO
DELETE, таких как использование связанных таблиц (joined tables) в качестве источников для таблицы, а также использование подсказок на уровне таблиц и запросов, найдите "DELETE" в Books Online и выберите подтему "Вместе с операторами T-SQL можно использовать несколько полезных ключевых слов, позволяющих создавать программные конструкции для управления программной последовательностью. Эти конструкции можно использовать внутри пакетов (групп операторов T-SQL, которые выполняются за один раз), хранимых процедур, сценариев и эпизодических ( ad hoc ) запросов. (Для примеров в этом разделе используется база данных pubs.)
Конструкция IF...ELSE используется для наложения условий, определяющих, какие операторы T-SQL нужно выполнить. Для IF...ELSE используется следующий синтаксис:
IF Булево_выражение Оператор_T-SQL | блок_операторов [ELSE Оператор_T-SQL | блок_операторов]
Булево_выражение возвращает значение TRUE или FALSE. Если выражение в предложении IF возвращает значение TRUE, то выполняются следующие операторы, а предложение ELSE и его операторы не выполняются. Если выражение возвращает значение FALSE, то выполняются только операторы, следующие после ключевого слова ELSE. Блок_операторов просто указывает на использование более чем одного оператора T-SQL. При использовании BEGIN и END, указывающие начало и конец каждого блока, – будь то блок в предложении IF, предложении ELSE или в обоих предложениях.
Вы можете использовать предложение IF без предложения ELSE. Рассмотрим сначала пример, где используется только IF. В следующем примере происходит проверка выражения, и если результатом этого выражения является значение TRUE, то выполняется последующий оператор PRINT:
IF (SELECT ytd_sales FROM titles
WHERE title_id = 'PC1035') > 5000
PRINT 'Year-to-date sales are
greater than $5,000 for PC1035.'
GO
Значение выражения IF будет равно TRUE, поскольку значение ytd_sales для строки с title_id = "PC1035" равно 8780. Будет выполнен оператор PRINT, и на экране будет выведен текст "Year-to-date sales are greater than $5,000 for PC1035" (Объем продаж на текущий год для PC1035 больше $5000).
А теперь добавим к предыдущему примеру предложение ELSE и изменим > 5000 на > 9000. Соответствующий пример показан ниже:
IF (SELECT ytd_sales FROM titles
WHERE title_id = 'PC1035') > 9000
PRINT 'Year-to-date sales are
greater than $9,000 for PC1035.'
ELSE
PRINT 'Year-to-date sales are
less than or equal to $9,000 for PC1035.'
GO
В данном случае будет выполнен оператор PRINT, следующий после предложения ELSE, поскольку выражение IF возвращает значение FALSE.
Расширим этот пример, добавив блоки операторов после предложений IF и ELSE. Выводимое сообщение и выполняемый запрос будут зависеть от значения выражения IF ( TRUE или FALSE ). Ниже приводится этот пример:
IF (SELECT ytd_sales FROM titles WHERE title_id = 'PC1035') > 9000
BEGIN
PRINT 'Year-to-date sales are
greater than $9,000 for PC1035.'
SELECT ytd_sales FROM titles
WHERE title_id = 'PC1035'
END
ELSE --ytd_sales должно быть <= 9000.
BEGIN
PRINT 'Year-to-date sales are
less than or equal to $9,000 for PC1035.'
SELECT price FROM titles
WHERE title_id = 'PC1035'
END
GO
Если значение выражения IF равно FALSE, то выполняются операторы между BEGIN и END в предложении ELSE. Сначала выполняется оператор PRINT и затем – оператор SELECT, показывающий, что книга стоит $22.95.
Вы можете также использовать вложенные операторы IF после предложения IF или после предложения ELSE. Например, чтобы использовать вложенные операторы IF...ELSE для определения диапазона, в который попадает среднее значение ytd_sales для всех заголовков, выполните следующий пример:
IF (SELECT avg(ytd_sales) FROM titles) < 10000
IF (SELECT avg(ytd_sales) FROM titles) < 5000
IF (SELECT avg(ytd_sales) FROM titles) < 2000
PRINT 'Average year-to-date sales are
less than $2,000.'
ELSE
PRINT 'Average year-to-date sales are
between $2,000 and $4,999.'
ELSE
PRINT 'Average year-to-date sales are
between $5,000 and $9,999.'
ELSE
PRINT 'Average year-to-date sales are greater
than $9,999.'
GO
При выполнении этого примера вы дважды увидите среди результатов следующее "Warning: Null value eliminated from . Это сообщение просто означает, что пустые значения ( null ) колонки ytd_sales не учитывались как значения при расчете среднего значения. Конечным результатом этого примера будет ", поскольку среднее значение равно $6090. Будьте аккуратны при использовании вложенных операторов IF. Можно легко запутаться в том, какой IF относится к очередному ELSE, или оставить IF без соответствующего ELSE. Использование символов табуляции для отступов, как в предыдущем примере, упрощает определение соответствующих пар IF...ELSE.
Конструкция WHILE используется для проверки условия, которое вызывает повторяющееся выполнение какого-либо оператора или TRUE. Эту конструкцию обычно называют циклом WHILE, так как операторы внутри конструкции WHILE выполняются циклическим образом. Ниже приводится синтаксис:
WHILE Булево_выражение Оператор_T-SQL | Блок_операторов [BREAK] Оператор_T-SQL | Блок_операторов [CONTINUE]
Как и в предложениях IF...ELSE, вы задаете в цикле WHILE BEGIN и END. Ключевое слово BREAK используется для выхода из цикла WHILE, после чего выполнение продолжается с оператора, следующего после конца цикла WHILE. Если цикл WHILE встроен в другие циклы WHILE, то ключевое слово BREAK вызывает выход только из того цикла WHILE, в котором оно находится; любые операторы вне этого цикла, а также внешние циклы продолжают выполняться. Ключевое слово CONTINUE в цикле указывает, что следует повторить операторы между ключевыми словами BEGIN и END в данном цикле, игнорируя любые другие операторы после CONTINUE.
Рассмотрим пример, где простой цикл WHILE используется для выполнения одного оператора UPDATE. В этом цикле WHILE проверяется условие, что среднее значение по колонке 20. Если результатом проверки является значение TRUE, то колонка WHILE проверяется снова и модификация повторяется, пока среднее значение колонки
WHILE (SELECT AVG(royalty) FROM roysched) < 20 UPDATE roysched SET royalty = royalty * 1.05 GO
Поскольку среднее значение колонки 15, этот цикл WHILE выполнится 6 раз, прежде чем среднее значение не достигнет 20 ; затем выполнение цикла прекращается, поскольку результатом проверяемого условия становится значение FALSE.
Теперь рассмотрим пример, где в цикле WHILE используются BREAK, CONTINUE, BEGIN и END. Вы будет повторять в цикле оператор UPDATE, пока среднее значение 25 процентов. Но если во время цикла максимальное значение SELECT после конца цикла WHILE. Ниже приводится соответствующая последовательность T-SQL:
WHILE (SELECT AVG(royalty) FROM roysched) < 25 BEGIN UPDATE roysched SET royalty = royalty * 1.05 IF (SELECT MAX(royalty)FROM roysched) > 27 BREAK ELSE CONTINUE END SELECT MAX(royalty) AS "MAX royalty" FROM roysched GO
Этот цикл будет выполнен только один раз, поскольку значение 27 уже имеется в этой таблице. Оператор UPDATE все же выполняется один раз, так как среднее значение меньше 25 процентов. Затем проверяется условие оператора IF, и результатом является значение TRUE, поэтому выполняется оператор BREAK, вызывающий выход из цикла WHILE. Выполнение программы затем продолжается, начиная с оператора, следующего за ключевым словом END (последний оператор SELECT ).
Напомним, что вы можете также использовать вложенные циклы WHILE, но следует учитывать, что ключевое слово BREAK или CONTINUE применяется только к циклу, из которого оно было вызвано, но не к внешним циклам WHILE.
Ключевое слово CASE используется для оценки списка условий и возврата одного из нескольких возможных результатов. Возвращаемый результат зависит от того, какое условие совпадает с другим указанным условием или является истинным. Наиболее распространенным применением CASE является замена кодового или сокращенного значения на более понятное значение и упорядочивание значений, как будет показано в наших примерах этого раздела. Имеется два формата для конструкции CASE: простой и поисковый. В простом формате на входе после CASE задается в виде выражения значение, которое проверяется на равенство со значением в выражении или выражениях WHEN. В поисковом формате происходит проверка булева выражения на значение TRUE или FALSE, а не проверка на равенство с каким-либо значением. Сначала рассмотрим простой формат. В простом формате предложение CASE имеет следующий синтаксис:
CASE входное_выражение WHEN выражение_для_when THEN результирующее_выражение [WHEN выражение_для_when THEN результирующее_выражение...n] [ELSE выражение_для_else] END
Значение результирующего выражения возвращается в том случае, если значение соответствующего выражения WHEN равно значению входного выражения. Выражения сравниваются в порядке их следования в предложении CASE. Если не обнаружено ни одного совпадения, то возвращается значение результирующего выражения ELSE (если оно задано); в противном случае возвращается значение NULL. Отметим, что в простом формате значение входного выражения CASE и значение выражения WHEN должны иметь одинаковый тип данных или допускать
В следующем примере используется простой формат предложения CASE внутри оператора SELECT. Колонка payterms (сроки платежей) таблицы sales (продажи) содержит одно из следующих значений для каждой строки: Net 30, Net 60, On invoice или None. С помощью следующего оператора T-SQL в колонке payterms можно выводить на экран альтернативные (более понятные) значения:
SELECT 'Payment Terms' = (сроки платежей)
CASE payterms
WHEN 'Net 30' THEN 'Payable 30 days --к оплате в течение 30 дней
after invoice' --после получения счета-фактуры
WHEN 'Net 60' THEN 'Payable 60 days --к оплате в течение 60 дней
after invoice' --после получения счета-фактуры
WHEN 'On invoice' THEN 'Payable upon --к оплате по
receipt of invoice' --получении счета-фактуры
ELSE 'None'
END,
title_id
FROM sales
ORDER BY payterms
GO
В этом предложении CASE проверяется значение payterms для каждой строки, указанной в операторе SELECT. Значение результирующего выражения возвращается в том случае, если значение выражения WHEN равно значению колонки payterms. Результаты предложения CASE появляются в колонке
Payment Terms title_id ---------------------------------------------------- Payable 30 days after invoice PC8888 Payable 30 days after invoice TC3218 Payable 30 days after invoice TC4203 Payable 30 days after invoice TC7777 Payable 30 days after invoice PS2091 Payable 30 days after invoice MC3021 Payable 30 days after invoice BU1111 Payable 30 days after invoice PC1035 Payable 60 days after invoice PS1372 Payable 60 days after invoice PS2106 Payable 60 days after invoice PS3333 Payable 60 days after invoice PS7777 Payable 60 days after invoice BU7832 Payable 60 days after invoice MC2222 Payable 60 days after invoice PS2091 Payable 60 days after invoice BU1032 Payable 60 days after invoice PS2091 Payable upon receipt of invoice PS2091 Payable upon receipt of invoice BU1032 Payable upon receipt of invoice BU2075 Payable upon receipt of invoice MC3021 (21 row(s) affected)
А теперь рассмотрим второй формат предложения CASE – поисковый формат. В этом формате предложение CASE имеет следующий синтаксис:
CASE WHEN Булево_выражение THEN результирующее_выражение [WHEN Булево_выражение THEN результирующее_выражение...n] [ELSE результирующее_выражение_для_else] END
Предложение CASE в поисковом формате отличается от CASE в простом формате тем, что в поисковом формате после ключевого слова CASE нет входного выражения, а после ключевых слов WHEN следуют булевы выражения, которые проверяются на значение TRUE или FALSE (а не на равенство). В поисковом формате предложение CASE проверяет значения булевых выражений и выводит значение результирующего выражения для первого булева выражения, возвращающего значение TRUE. (Выражения проверяются в порядке их следования.)
Например, предложение CASE внутри следующего оператора SELECT проверяет значение колонки price (цена) каждой строки и возвращает символьную строку, соответствующую диапазону цен (Price Range), в который попадает цена данной книги:
SELECT 'Price Range' =
CASE
WHEN price BETWEEN .01 AND 10.00
THEN 'Inexpensive: $10.00 or less' --дешевые
WHEN price BETWEEN 10.01 AND 20.00
THEN 'Moderate: $10.01 to $20.00' --умеренные
WHEN price BETWEEN 20.01 AND 30.00
THEN 'Semi-expensive: $20.01 to $30.00' --не слишком дорогие
WHEN price BETWEEN 30.01 AND 50.00
THEN 'Expensive: $30.01 to $50.00' --дорогие
WHEN price IS NULL
THEN 'No price listed' --цена не указана
ELSE 'Very expensive!' --очень дорогие
END,
title_id
FROM titles
ORDER BY price
GO
Результирующий набор имеет следующий вид:
Price Range title_id ———————————————— ———— No price listed MC3026 No price listed PC9999 Inexpensive: $10.00 or less MC3021 Inexpensive: $10.00 or less BU2075 Inexpensive: $10.00 or less PS2106 Inexpensive: $10.00 or less PS7777 Moderate: $10.01 to $20.00 PS2091 Moderate: $10.01 to $20.00 BU1111 Moderate: $10.01 to $20.00 TC4203 Moderate: $10.01 to $20.00 TC7777 Moderate: $10.01 to $20.00 BU1032 Moderate: $10.01 to $20.00 BU7832 Moderate: $10.01 to $20.00 MC2222 Moderate: $10.01 to $20.00 PS3333 Moderate: $10.01 to $20.00 PC8888 Semi-expensive: $20.01 to $30.00 TC3218 Semi-expensive: $20.01 to $30.00 PS1372 Semi-expensive: $20.01 to $30.00 PC1035 (18 row(s) affected)
CASE мы ставили запятую после ключевого слова END, поскольку все предложение CASE было использовано как часть списка_колонок в предложении SELECT вместе с title_id. Иными словами, все предложение CASE было просто входом в список_колонок. Это наиболее распространенное применение ключевого слова CASE.Ниже приводятся другие ключевые слова, которые можно использовать для управления программной последовательностью:
GOTO метка. Выполняет передачу управления на метку, определенную в GOTO.RETURN. Безусловный выход из запроса или процедуры.WAITFOR. Задает задержку или определенное время для выполнения оператора.В этой лекции мы рассмотрели использование операторов T-SQL INSERT, UPDATE и DELETE. Мы также рассмотрели ключевые слова T-SQL IF, ELSE, WHILE, BEGIN, END и CASE, которые используются для управления программной последовательностью. В лекции 21 вы узнаете, как создавать хранимые процедуры, в которых вы можете использовать эти операторы и конструкции.
Для получения официальных документов о завершении программы дополнительного профессионального образования (удостоверения о повышении квалификации, дипломов о профессиональной переподготовке и MBA) необходимо предоставить:
Внимание! Вы можете не заказывать доставку бумажной версии официального документы, а скачать его в электронном виде и распечатать самостоятельно. Информация о выданном документе в течение 1 месяца загружается в Федеральную информационную систему «Федеральный реестр сведений о документах об образовании и (или) о квалификации, документах об обучении» - ФИС ФРДО.
Доступ на новый сайт осуществляется с использованием адреса электронной почты, который был указан вами при регистрации на "старом". Мы постарались перенести все ваши данные с прежнего ресурса, однако не исключена вероятность потери части информации.
При возникновении проблемы со входом, воспользуйтесь функцией сброса пароля
Если вы обнаружите несоответствия, пожалуйста, сообщите нам.