Определяемые пользователем функции

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

В этом разделе

Преимущества определяемых пользователем функций

Типы функций

Рекомендации

Инструкции, допустимые в функциях

Привязанные к схеме функции

Указание параметров

Связанные задачи

Преимущества определяемых пользователем функций

Определяемые пользователем функции SQL Server предоставляют следующие преимущества.

• Делают возможным модульное программирование.

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

• Позволяют ускорить выполнение.

  Как и хранимые процедуры, определяемые пользователем функции Transact-SQL снижают стоимость компиляции кода Transact-SQL, кэшируя и повторно используя планы выполнения. Это означает, что для определяемых пользователем функций нет необходимости выполнять повторный синтаксический анализ и оптимизацию при каждом вызове, что значительно ускоряет их выполнение.

  Функции CLR дают значительное преимущество в производительности по сравнению с функциями Transact-SQL для вычислительных задач, работы со строками и бизнес-логикой. Функции Transact-SQL лучше подходят для логики с интенсивным доступом к данным.

• Позволяют уменьшить сетевой трафик.

  Операция, которая фильтрует данные на основе какого-нибудь сложного ограничения и не может быть выражена одним скалярным выражением, может быть реализована в виде функции. Ее можно вызвать из предложения WHERE, чтобы уменьшить число строк, возвращаемых клиенту.

Примечание
Определяемые пользователем функции Transact-SQL в запросах могут выполняться только как один поток (план последовательного выполнения).

[Top]

Типы функций

• Скалярная функция
  Пользовательские скалярные функции возвращают одно значение типа данных, заданного в предложении RETURNS. Встроенная скалярная функция не имеет тела, скалярное значение является результатом одной инструкции. Скалярная функция из нескольких инструкций имеет текст, ограниченное блоком BEGIN...END, и содержит последовательность инструкций Transact-SQL, возвращающих одно значение. Такие функции могут возвращать любые типы данных, кроме text, ntext, image, cursor и timestamp.

• Функции с табличным значением
  Определяемые пользователем функции с табличным значением возвращают значение типа table. Встроенная функция с табличным значением не имеет текста, таблица является результирующим набором одной инструкции.

• Системные функции
  SQL Server предоставляет множество системных функций для выполнения различных операций. Их нельзя изменить. Дополнительные сведения см. в разделах Встроенные функции (Transact-SQL), Системные хранимые функции (Transact-SQL) и Динамические административные представления и функции (Transact-SQL).

[Top]

Рекомендации

Ошибки языка Transact-SQL, вызывающие отмену инструкции и продолжение выполнения со следующей инструкции модуля (триггера или хранимой процедуры), внутри функций обрабатываются иным образом. В функциях такие ошибки вызывают остановку выполнения функции. Это вызывает отмену инструкции, вызвавшей функцию.

Инструкции в блоке BEGIN...END не могут иметь каких-либо побочных эффектов. Побочными эффектами функций называются любые постоянные изменения состояния ресурса, область которого лежит за пределами функции, например изменение таблицы базы данных. Инструкции внутри функции могут изменять только локальные по отношению к этой функции объекты, например локальные курсоры или переменные. Изменения таблиц баз данных, операции с курсорами, не являющимися локальными для данной функции, отправка электронной почты, попытка изменения каталога, формирование результирующего набора, возвращаемого пользователю — это примеры действий, выполнение которых внутри функции невозможно.

Примечание
Если инструкция CREATE FUNCTION создает побочные эффекты в отношении ресурсов, которые не существуют во время применения инструкции CREATE FUNCTION, то SQL Server выполняет эту инструкцию. Однако SQL Server не выполняет эту функцию при ее вызове.

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

[Top]

Инструкции, допустимые в функциях

К типам инструкций, допустимым внутри функций, относятся следующие.

• Инструкции DECLARE, используемые для определения переменных и курсоров, локальных для данной функции.

• Присвоение значений объектам, локальным для данной функции, например присвоение значений скалярным и табличным локальным переменным с помощью инструкции SET.

• Операции над курсорами, обращающиеся к локальным курсорам и выполняющие их объявление, открытие, закрытие и освобождение внутри функции. Инструкции FETCH, возвращающие данные клиенту, запрещены. Разрешены только инструкции FETCH, присваивающие значения локальным переменным с помощью предложения INTO.

• Инструкции управления потоком, за исключением инструкций TRY...CATCH.

• Инструкции SELECT, содержащие списки выборки с выражениями, присваивающими значения переменным, локальным для данной функции.

• Инструкции UPDATE, INSERT и DELETE, изменяющие табличные переменные, локальные для функции.

• Инструкции EXECUTE, вызывающие расширенную хранимую процедуру.

Встроенные системные функции

Следующие недетерминированные встроенные функции могут быть использованы в определяемых пользователем функциях языка Transact-SQL.

Следующие недетерминированные встроенные функции в определяемых пользователем функциях на языке Transact-SQL использовать нельзя.

Список детерминированных и недетерминированных встроенных системных функций см. в разделе Детерминированные и недетерминированные функции.

[Top]

Привязанные к схеме функции

Инструкция CREATE FUNCTION поддерживает предложение SCHEMABINDING, позволяющее привязать функцию к схеме каких-либо объектов, на которые она ссылается, например таблиц, представлений и других пользовательских функций. Попытка изменения или удаления любого объекта, к которому обращается привязанная к схеме функция, приводит к ошибке.

Перед указанием предложения SCHEMABINDING в инструкции CREATE FUNCTION нужно соблюсти следующие условия.

• Все представления и пользовательские функции, к которым обращается функция, должны быть привязаны к схеме.

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

• Для всех объектов (таблиц, представлений и пользовательских функций), к которым обращается функция, должно быть получено разрешение REFERENCES.

Для удаления привязки к схеме можно использовать инструкцию ALTER FUNCTION. В инструкции ALTER FUNCTION следует переопределить функцию без указания предложения WITH SCHEMABINDING.

[Top]

Указание параметров

Пользовательская функция может принимать 0 или более входных параметров и возвращать либо скалярное, либо табличное значение. Максимальное число входных параметров для функции равно 1024. Если для параметра функции установлено значение по умолчанию, необходимо указать ключевое слово DEFAULT при вызове функции, чтобы получить установленное по умолчанию значение. Это поведение отличается от использования параметров со значениями по умолчанию в пользовательских хранимых процедурах, в которых пропущенный параметр также принимает значение по умолчанию. В определяемых пользователями функциях не поддерживаются выходные параметры.

[Top]

Связанные задачи

[Top]