Пользовательские функции

Функции для обработки пустых значений

Часто пустое значение нужно преобразовать в некоторое допустимое, чтобы данные можно было понять или чтобы выражение имело результат. Пустые значения требуют специальной обработки при использовании в выражениях, и язык SQL содержит ряд функций, специально предназначенных для работы с пустыми значениями. Функции isnull () и coalesce() преобразуют пустые значения в пригодные для использования, а функция nullif() создает пустое значение, если выполняется определенное условие.

Наиболее часто используемой функцией, предназначенной для работы с пустыми значениями, является isnull(). Эта функция в качестве аргумента принимает одно выражение или столбец, а также подстановочное значение. Если первый аргумент является допустимым значением (т.е. не пустым), эта функция возвращает его. Однако если первый аргумент представляет собой пустое значение, то возвращается значение второго аргумента. Общий синтаксис функции следующий:

Isnull(исходное_выражение, замещающее_значение).

Следующий пример подставляет строку 'не указан' вместо пустого значения там, где для клиента не определен телефон:

SELECT FName, LName, ISNULL(Phone, 'не указан') AS Phone

FROM Customer

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

Coalesce(выражение, выражение,...)

В следующем примере продемонстрирована функция coalesce(), возвращающая первое непустое значение (в данном случае это 3): SELECT COALESCE(NULL, 1+NULL, 1+2, 'abc'))

Иногда пустое значение нужно создать на месте заменяющего его суррогатного. Если база данных заполнена значениями n/a, - или пустыми строками там, где должны находиться пустые значения, вы можете воспользоваться функцией nullif() и расчистить базу данных.

Функция nullif() принимает два аргумента. Если они равны, то возвращается пустое значение, в противном случае возвращается первый параметр.

Следующий фрагмент кода преобразует все пробелы в столбце FName в пустые значения.

SELECT NULLIF(LTRIM(RTRIM(FName)),'') AS FName

FROM Customer

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

Пользовательские функции обладают следующими преимущества:

· С их помощью можно внедрить в запросы сложную логику.

· Создавая новые функции, можно проектировать сложные выражения.

· Эти функции обладают всеми достоинствами представлений, поскольку могут использоваться в предложении FROM инструкции SELECT и в выражениях и могут быть задействованы в схеме. К тому же пользовательские функции могут принимать параметры, в то время как представления — нет.

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

Главным аргументом против использования пользовательских функций является вопрос переносимости. Пользовательские функции привязаны к SQL Server, и любую базу данных, использующую множество таких функций, будет сложно или даже невозможно перенести на другую платформу СУБД без существенной переработки. Эта задача усложняется тем, что также должны быть переписаны и все инструкции SELECT, в которые внедрены пользовательские функции. Если в будущем планируется развертывание базы данных на других платформах, то лучше заменить все пользовательские функции представлениями или хранимыми процедурами.

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

· Пользовательские функции могут возвращать значения, относящиеся к большинству типов данных SQL Server. Не допускается использовать в качестве типов возвращаемых значений лишь такие типы, как text, ntext, image, cursor и timestamp.

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

Пользовательские функции во многом аналогичны функциям, используемым в классических языках программирования. Эти функции принимают несколько параметров и возвращают одно значение. Различия между пользовательскими функциями SQL Server и функциями многих процедурных языков программирования состоят в том, что в них передача параметров осуществляется по значению, поэтому для них не предусмотрен способ передачи параметров, подобный применению ссылки или передачи указателей. Тем не менее пользовательские функции удобны тем, что позволяют возвращать данные в виде специальной таблицы.

Пользовательские функции подразделяются на три типа:

· Скалярные, возвращающие одно значение.

· Внедренные табличные, аналогичные представлениям.

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

Дата добавления: 2014-12-08