Главная » Microsoft SQL Server, Базы данных » Использование выражений и скалярных функций

0

Когда мой сын Дейвид был младше, он конструировал невообразимых монстров из отдельных блоков K’NEX. Если вы еще не знаете, что такое K’NEX, найдите соответствующий сайт с помощью Google и посмотрите, какие невероятные вещи дети способны создавать с его помощью.

Что делает конструктор K’NEX столь прекрасным, так это то, что любой блок можно вставить в другой блок. Такая взаимосвязанность отдельных блоков и обеспечивает конструктору гибкость. Аналогично, взаимосвязанность выражений и функций SQL обеспечивает этому языку такую силу и гибкость.

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

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

Выражения могут быть задействованы во множестве элементов синтаксиса SQL. Практически везде, где может использоваться значение, можно вставить выражение. Это могут быть значения столбцов, предложения JOIN, WHERE, HAVING и ORDER BY. В то же время выражения нельзя подставлять вместо имен объектов, таких как таблицы или столбцы.

Инструкции и выражения SQL не зависят от ре- Совет       гистра символов — они будут выполнены оди

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

Создание выражений

Рис. 8.1. Создание выражения и назначение псевдонима в конструкторе запросов утилиты Management Studio

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

Подзапросы рассмотрены в главе 10, переменные— в главе 18, а пользовательские функции детально описаны в главе 22.

Операторы

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

?               Математический оператор деления по модулю (%) возвращает остаток от деления. Математические функции floor () и ceiling (). возвращающие округленное большее или меньшее целое число, связаны с ним. Функция floor () является эквивалентом функции int () языка Basic:

SELECT 15%4 as Modulo,

FLOOR(1.25) as [Floor], CEILING(1.25) as [Ceiling]

Результат запроса будет следующим:

Modulo               Floor Ceiling

3              12

?               Оператор + используется как в математических выражениях, так и для конкатенации строк. Этот оператор отличается от символа MS-DOS, используемого для строковой конкатенации (&):

SELECT 123 + 456 as Addition,

1abc’ + ‘defg’ as Concatenation

Результат будет следующим:

Addition Concatenation

579                  abcdefg

Данные из столбцов таблиц и строковых литералов можно объединять оператором конкатенации для возвращения произвольных данных:

Select ‘Product: ‘ + ProductName as [Product]

From Product

Результат будет следующим:

Product

Product: Basic Box Kite 21 inch Product: Dragon Flight Product: Sky Dancer

Бинарные операторы

Бинарные операторы используют для манипуляций двоичные числа. Их редко используют в транзакционных базах данных, однако они доказали свою незаменимость в некоторых операциях с метаданными. Например, одним из способов определения, какие из столбцов были обновлены триггером (программой, которая автоматически выполняется при вставке, обновлении или удалении данных; подробно она рассмотрена в главе 23), является использование функции columns_updated (), которая возвращает двоичное представление этих столбцов.

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

Булевы битовые операторы (and, or и not) являются основным строительным блоком цифровой электроники и двоичного программирования. В то время как в цифровой электронике булевы операторы работают с отдельными битами, бинарные операторы работают с каждым битом значений целочисленных типов (int, smallint, tinyint и bit).

Булев оператор and

Булев оператор AND, представленный символом амперсанда (&), возвращает значение true (т.е. “истина’’), если оба значения равны true. Если любой из операндов или оба имеют значение false (т.е. “ложь”), то результатом выражения будет также false. Например, результатом выражения SELECT 1&1

будет 1.

А вот еще один пример:

SELECT 1&0

Теперь результатом будет 0.

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

–          3 =          011

–          5 =          101

— AND             

–          1 =          001

Таким образом, инструкция

SELECT 3&5

в результате даст единицу.

Булев оператор or

Булев оператор OR, представленный символом вертикальной черты (|), возвращает значение true, если любой из операндов (или оба сразу) имеет значение true. Например, результатом выражения SELECT 1|1 будет единица.

Следующая инструкция, комбинирующая ложное и истинное значения

SELECT l|0,

также в результате даст единицу.

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

– 3 = 011 — 5 = 101

– OR  

– 7 = 111

Таким образом, инструкция

SELECT 3|5

в результате даст число 7.

Булев оператор Exclusive OR

Булев оператор Exclusive OR, представленный символом галочки (ж), возвращает значение true, если любой из операндов имеет значение true, но не оба одновременно. Его использование аналогично применению оператора OR к двум парам операторов AND, к каждому операнду которых, в свою очередь, применен оператор NOT. Хотя это достаточно просто реализовать в цифровой электронике, в программе этот оператор использовать еще легче.

Результатом выражения SELECT 1Ж1 будет 0.

В то же время сочетание ложного и истинного значений SELECT 0*1 даст в результате 1.

Бинарный оператор not

Последний бинарный оператор, представленный символом тильды (~), является двоичной функцией NOT. Обычно этот оператор применяется к одному из операндов для корректировки результата операций AND или OR. В данном случае все несколько иначе. Оператор NOT выполняет логическое обращение каждого бита в выражении, а результат зависит от длины самого выражения. Приведем пример:

DECLARE @А BIT

SET @А = 1 SELECT ~@А

Результатом будет нуль.

Бинарный оператор NOT не подходит для использования в булевых выражениях, таких как условие IF. Например, следующий код некорректен:

SELECT * FROM Product WHERE -(1=1)

Следует заметить, что оператор NOT может служить дополнением к другим булевым операторам.

Оператор case

Команда CASE в SQL Server — гибкое и удобное средство создания динамических выражений. Если вы программист, то несомненно используете команду CASE в других языках программирования. Однако команда CASE в данном случае несколько отличается. Она используется не для программного переключения управления, а для логического определения значения выражения на основе заданного условия, подобно функции if О в других языках программирования.

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

Когда программист пишет текст процедуры, часть формулы может изменяться в зависимости от данных. При процедурном мышлении в данном случае лучше организовать цикл по строкам и использовать множество операторов if для выбора корректной формулы. В то же время использование выражения case для выполнения различных вычислений и операций в одном запросе позволяет SQL Server оптимизировать и значительно ускорить процесс.

Так как оператор CASE возвращает выражение, его можно использовать в любом месте любой инструкции SQL DML (SELECT, INSERT, UPDATE и DELETE), где может использо-

ваться обычное выражение (например, выражения для столбцов, а также предложения JOIN, WHERE, HAVING и ORDER BY).

Оператор CASE имеет две формы: простую и булеву. Они будут описаны в следующих разделах.

Простая форма оператора case

В простой форме оператора CASE вначале представлено само значение, после чего перечисляются все тестовые значения. Однако эта форма ограничена тем, что можно использовать только сравнения на предмет равенства. Данный оператор CASE последовательно проверяет все условия WHEN и возвращает значение THEN из первой строки, в которой выполнится условие WHEN.

В следующем примере, основанном на базе данных OBXKites, один тип (поле customer type) назначается по умолчанию для всех новых клиентов, при этом в столбец is default заносится значение true. Оператор CASE сравнивает значение в столбце isdefault со всеми возможными значениями бита, после чего возвращает символьную строку 1 default type ‘ или 1 possible 1 в зависимости от текущего значения:

USE OBXKites

SELECT CustomerTypeName,

CASE [IsDefault]

WHEN 1 THEN ‘default type’

WHEN 0 THEN ‘possible’

ELSE ‘-‘

End as AssignStatus From CustomerType

Будет получен следующий результат:

CustomerTypeName AssignStatus

Preferred             possible

Wholesale            possible

Retail                   default type

Оператор CASE завершается ключевым словом END и псевдонимом. В данном примере оператор CASE тестировал значения столбца isdef ault, но формировал столбец AssignStatus в результирующем наборе данных инструкции SELECT.

Булева форма оператора case

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

SELECT

CASE

WHEN 1<0 THEN ‘Реальность призрачна.’

WHEN GetDate() = ’11/30/2005′

THEN ‘Дейвид получил водительские права.’

WHEN 1>0 THEN ‘Жизнь продолжается.’

END AS RealityCheck

Результат запроса будет получен, когда будет выполнена проверка шестнадцатилетия Дейвида:

Дейвид получил водительские права.

Как и в простой форме оператора CASE, выполняется оператор THEN в первой строке, в котором условие WHEN было выполнено. В данном случае, даже если единица вдруг окажется меньше нуля (только представьте!), оператор CASE вернет строку ‘Жизнь продолжается ‘. Когда Дейвиду исполнится 16 лет, оператор сообщит о получении им водительских прав. Если ни одно из перечисленных событий не произойдет, мы получим сообщение, что жизнь продолжается.

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

Булева форма оператора CASE может содержать сложные условия, в том числе операторы or и and. В следующем примере для создания оператора CASE использован пакет (в том числе задействованы переменные Т-SQL, которые мы рассмотрим в главе 18), а само выражение CASE содержит операторы and и between:

DECLARE @b INT, @q INT SET @b = 2007 SET @q = 25 Select CASE

WHEN @b = 2 007 AND @q BETWEEN 10 AND 3 0 THEN 1 ELSE NULL END AS Test

Результат выполнения этого пакета будет следующим:

Test

1

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии