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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Аналогично функциям в языках программирования, определяемые пользователем функции SQL ServerSQL Server представляют собой подпрограммы, которые принимают параметры, выполняют действие, например, сложные вычисления, и возвращают результат этого действия в виде значения.Like functions in programming languages, SQL ServerSQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. Возвращаемое значение может быть либо единичным скалярным значением, либо результирующим набором.The return value can either be a single scalar value or a result set.

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

Зачем нужны определяемые пользователем функции (UDF)?Why use user-defined functions (UDFs)?

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

    Можно, однажды создав функцию, сохранить ее в базе данных, а затем любое число раз вызывать из своей программы.You can create the function once, store it in the database, and call it any number of times in your program. Определяемые пользователем функции могут быть изменены независимо от исходного кода программы.User-defined functions can be modified independently of the program source code.

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

    Как и хранимые процедуры, определяемые пользователем функции Transact-SQLTransact-SQL снижают стоимость компиляции кода Transact-SQLTransact-SQL, кэшируя и повторно используя планы выполнения.Similar to stored procedures, Transact-SQLTransact-SQL user-defined functions reduce the compilation cost of Transact-SQLTransact-SQL code by caching the plans and reusing them for repeated executions. Это означает, что для определяемых пользователем функций нет необходимости выполнять повторный синтаксический анализ и оптимизацию при каждом вызове, что значительно ускоряет их выполнение.This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

    Функции CLR дают значительное преимущество в производительности по сравнению с функциями Transact-SQLTransact-SQL для вычислительных задач, работы со строками и бизнес-логикой.CLR functions offer significant performance advantage over Transact-SQLTransact-SQL functions for computational tasks, string manipulation, and business logic. Функции Transact-SQLTransact-SQL лучше подходят для логики с интенсивным доступом к данным.Transact-SQLTransact-SQL functions are better suited for data-access intensive logic.

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

    Операция, которая фильтрует данные на основе какого-нибудь сложного ограничения и не может быть выражена одним скалярным выражением, может быть реализована в виде функции.An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. Ее можно вызвать из предложения WHERE, чтобы уменьшить число строк, возвращаемых клиенту.The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.

Важно!

Определяемые пользователем функции Transact-SQLTransact-SQL в запросах могут выполняться только как один поток (план последовательного выполнения).Transact-SQLTransact-SQL UDFs in queries can only be executed on a single thread (serial execution plan). Поэтому использование определяемых пользователем функций запрещает параллельную обработку запросов.Therefore using UDFs inhibits parallel query processing. Дополнительные сведения о параллельной обработке запросов см. в статье Руководство по архитектуре обработки запросов.For more information about parallel query processing, see the Query Processing Architecture Guide.

Типы функцийTypes of functions

Скалярная функцияScalar Function
Пользовательские скалярные функции возвращают одно значение типа данных, заданного в предложении RETURNS.User-defined scalar functions return a single data value of the type defined in the RETURNS clause. Для встроенной скалярной функции возвращаемое скалярное значение является результатом одной инструкции.For an inline scalar function, the returned scalar value is the result of a single statement. Скалярная функция из нескольких инструкций имеет текст может содержать последовательность инструкций Transact-SQLTransact-SQL, возвращающих одно значение.For a multistatement scalar function, the function body can contain a series of Transact-SQLTransact-SQL statements that return the single value. Такие функции могут возвращать любые типы данных, кроме text, ntext, image, cursorи timestamp.The return type can be any data type except text, ntext, image, cursor, and timestamp. Примеры.Examples.

Функции с табличными значениямиTable-Valued Functions
Определяемые пользователем функции с табличным значением возвращают данные типа table.User-defined table-valued functions return a table data type. Встроенная функция с табличным значением не имеет текста, таблица является результирующим набором одной инструкции.For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement. Примеры.Examples.

Системные функцииSystem Functions
SQL ServerSQL Server предоставляет множество системных функций для выполнения различных операций.provides many system functions that you can use to perform a variety of operations. Их нельзя изменить.They cannot be modified. Дополнительные сведения см. в разделах Встроенные функции (Transact-SQL), Системные хранимые функции (Transact-SQL) и Динамические административные представления и функции (Transact-SQL).For more information, see Built-in Functions (Transact-SQL), System Stored Functions (Transact-SQL), and Dynamic Management Views and Functions (Transact-SQL).

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

Ошибки Transact-SQLTransact-SQL, которые вызывают отмену инструкции и продолжение выполнения со следующей инструкции в модуле (например в триггере или хранимой процедуре), внутри функций обрабатываются иначе.Transact-SQLTransact-SQL errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function. В функциях такие ошибки вызывают остановку выполнения функции.In functions, such errors cause the execution of the function to stop. Это вызывает отмену инструкции, вызвавшей функцию.This in turn causes the statement that invoked the function to be canceled.

Инструкции в блоке BEGIN...END не могут иметь каких-либо побочных эффектов.The statements in a BEGIN...END block cannot have any side effects. Побочными эффектами функций называются любые постоянные изменения состояния ресурса, область которого лежит за пределами функции, например изменение таблицы базы данных.Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. Инструкции внутри функции могут изменять только локальные по отношению к этой функции объекты, например локальные курсоры или переменные.The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Изменения таблиц баз данных, операции с курсорами, не являющимися локальными для данной функции, отправка электронной почты, попытка изменения каталога, формирование результирующего набора, возвращаемого пользователю — это примеры действий, выполнение которых внутри функции невозможно.Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

Примечание

Если инструкция CREATE FUNCTION создает побочные эффекты в отношении ресурсов, которые не существуют во время применения инструкции CREATE FUNCTION, то SQL ServerSQL Server выполняет эту инструкцию.If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued, SQL ServerSQL Server executes the statement. Однако SQL ServerSQL Server не выполняет эту функцию при ее вызове.However, SQL ServerSQL Server does not execute the function when it is invoked.

Число раз, когда указанная в запросе функция будет фактически выполнена, может различаться для разных планов выполнения, построенных оптимизатором.The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. Примером является функция, вызываемая вложенным запросом в предложении WHERE.An example is a function invoked by a subquery in a WHERE clause. Число раз, когда вложенный запрос и его функция будут выполнены, может различаться для разных путей доступа, выбираемых оптимизатором.The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

Важно!

Дополнительные сведения и рекомендации по производительности для определяемых пользователем функций см. в разделе Создание определяемых пользователем функций (ядро СУБД).For more information and performance considerations on user-defined functions, see Create User-defined Functions (Database Engine).

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

К типам инструкций, допустимым внутри функций, относятся следующие.The types of statements that are valid in a function include:

  • Инструкции DECLARE, используемые для определения переменных и курсоров, локальных для данной функции.DECLARE statements can be used to define data variables and cursors that are local to the function.

  • Присвоение значений объектам, локальным для данной функции, например присвоение значений скалярным и табличным локальным переменным с помощью инструкции SET.Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

  • Операции над курсорами, обращающиеся к локальным курсорам и выполняющие их объявление, открытие, закрытие и освобождение внутри функции.Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. Инструкции FETCH, возвращающие данные клиенту, запрещены.FETCH statements that return data to the client are not allowed. Разрешены только инструкции FETCH, присваивающие значения локальным переменным с помощью предложения INTO.Only FETCH statements that assign values to local variables using the INTO clause are allowed.

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

  • Инструкции SELECT, содержащие списки выборки с выражениями, присваивающими значения переменным, локальным для данной функции.SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

  • Инструкции UPDATE, INSERT и DELETE, изменяющие табличные переменные, локальные для данной функции.UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

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

Встроенные системные функцииBuilt-in system functions

Следующие недетерминированные встроенные функции могут быть использованы в определяемых пользователем функциях языка Transact-SQL.The following nondeterministic built-in functions can be used in Transact-SQL user-defined functions.

CURRENT_TIMESTAMPCURRENT_TIMESTAMP @@MAX_CONNECTIONS@@MAX_CONNECTIONS
GET_TRANSMISSION_STATUSGET_TRANSMISSION_STATUS @@PACK_RECEIVED@@PACK_RECEIVED
GETDATEGETDATE @@PACK_SENT@@PACK_SENT
GETUTCDATEGETUTCDATE @@PACKET_ERRORS@@PACKET_ERRORS
@@CONNECTIONS@@CONNECTIONS @@TIMETICKS@@TIMETICKS
@@CPU_BUSY@@CPU_BUSY @@TOTAL_ERRORS@@TOTAL_ERRORS
@@DBTS@@DBTS @@TOTAL_READ@@TOTAL_READ
@@IDLE@@IDLE @@TOTAL_WRITE@@TOTAL_WRITE
@@IO_BUSY@@IO_BUSY

Следующие недетерминированные встроенные функции нельзя использовать в определяемых пользователем функциях Transact-SQLTransact-SQL.The following nondeterministic built-in functions cannot be used in Transact-SQLTransact-SQL user-defined functions.

NEWIDNEWID RANDRAND
NEWSEQUENTIALIDNEWSEQUENTIALID TEXTPTRTEXTPTR

Список детерминированных и недетерминированных встроенных системных функций см. в разделе Детерминированные и недетерминированные функции.For a list of deterministic and nondeterministic built-in system functions, see Deterministic and Nondeterministic Functions.

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

Инструкция CREATE FUNCTION поддерживает предложение SCHEMABINDING, позволяющее привязать функцию к схеме каких-либо объектов, на которые она ссылается, например таблиц, представлений и других пользовательских функций.CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. Попытка изменения или удаления любого объекта, к которому обращается привязанная к схеме функция, приводит к ошибке.An attempt to alter or drop any object referenced by a schema-bound function fails.

Перед указанием предложения SCHEMABINDING в инструкции CREATE FUNCTION нужно соблюсти перечисленные ниже условия.These conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION:

  • Все представления и пользовательские функции, к которым обращается функция, должны быть привязаны к схеме.All views and user-defined functions referenced by the function must be schema-bound.

  • Все объекты, к которым обращается функция, должны находиться в той же базе данных, что и функция.All objects referenced by the function must be in the same database as the function. Обращение к объектам должно производиться по однокомпонентным либо двухкомпонентным именам.The objects must be referenced using either one-part or two-part names.

  • Для всех объектов (таблиц, представлений и пользовательских функций), к которым обращается функция, должно быть получено разрешение REFERENCES.You must have REFERENCES permission on all objects (tables, views, and user-defined functions) referenced in the function.

Для удаления привязки к схеме можно использовать инструкцию ALTER FUNCTION.You can use ALTER FUNCTION to remove the schema binding. В инструкции ALTER FUNCTION следует переопределить функцию без указания предложения WITH SCHEMABINDING.The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING.

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

Пользовательская функция может принимать 0 или более входных параметров и возвращать либо скалярное, либо табличное значение.A user-defined function takes zero or more input parameters and returns either a scalar value or a table. Максимальное число входных параметров для функции равно 1024.A function can have a maximum of 1024 input parameters. Если для параметра функции установлено значение по умолчанию, необходимо указать ключевое слово DEFAULT при вызове функции, чтобы получить установленное по умолчанию значение.When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. Это поведение отличается от использования параметров со значениями по умолчанию в пользовательских хранимых процедурах, в которых пропущенный параметр также принимает значение по умолчанию.This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value. В определяемых пользователями функциях не поддерживаются выходные параметры.User-defined functions do not support output parameters.

Дополнительные примерыMore examples!

Описание задачиTask Description РазделTopic
Описывает, как создать определяемую пользователем функцию Transact-SQL.Describes how to create a Transact-SQL user-defined function. Создание определяемых пользователем функций (компонент Database Engine)Create User-defined Functions (Database Engine)
Описывает, как создать функции CLR.Describes how create a CLR function. Создание функций CLRCreate CLR Functions
Описывает, как создать определяемую пользователем агрегатную функцию.Describes how to create a user-defined aggregate function Создание пользовательских агрегатных функцийCreate User-defined Aggregates
Описывает, как изменить определяемую пользователем функцию Transact-SQL.Describes how to modify a Transact-SQL user-defined function. Изменение пользовательских функцийModify User-defined Functions
Описывает, как удалить определяемую пользователем функцию.Describes how to delete a user-defined function. Удаление пользовательских функцийDelete User-defined Functions
Описывает, как создать определяемую пользователем функцию выполнения.Describes how to execute a user-defined function. Выполнение пользовательских функцийExecute User-defined Functions
Описывает, как переименовать определяемую пользователем функциюDescribes how to rename a user-defined function Переименование пользовательских функцийRename User-defined Functions
Описывает, как просмотреть определяемую пользователем функцию.Describes how to view the definition of a user-defined function. Просмотр пользовательских функцийView User-defined Functions