Поделиться через

  • Статья

Создание определяемых пользователем функций (компонент Database Engine)

Определяемые пользователем функции создаются инструкцией CREATE FUNCTION, изменяются инструкцией ALTER FUNCTION и удаляются инструкцией DROP FUNCTION. Полное имя каждой определяемой пользователем функции (schema_name.function_name) должно быть уникальным.

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

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

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

ПримечаниеПримечание

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

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

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

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

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

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

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

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

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

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

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

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

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

CURRENT_TIMESTAMP

@@MAX_CONNECTIONS

GET_TRANSMISSION_STATUS

@@PACK_RECEIVED

GETDATE

@@PACK_SENT

GETUTCDATE

@@PACKET_ERRORS

@@CONNECTIONS

@@TIMETICKS

@@CPU_BUSY

@@TOTAL_ERRORS

@@DBTS

@@TOTAL_READ

@@IDLE

@@TOTAL_WRITE

@@IO_BUSY

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

NEWID

RAND

NEWSEQUENTIALID

TEXTPTR

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

Функции, привязанные к схеме

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

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

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

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

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

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

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

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