• Статья

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

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

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

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

Запросы

Пользовательские функции, возвращающие скалярные значения, разрешены в следующем:

  • В качестве выражения expression в списке select_list инструкции SELECT:

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
    StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
GO

  • В качестве выражения expression или строкового выражения string_expression в предикате предложения WHERE или HAVING:

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
ORDER BY ProductID, StartDate;
GO

  • В качестве выражения group_by_expression в предложении GROUP BY.

  • В качестве выражения order_by_expression в предложении ORDER BY.

  • В качестве выражения expression в предложении SET инструкции UPDATE:

    USE AdventureWorks2008R2;
GO
UPDATE Production.ProductListPriceHistory
SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
WHERE ProductID > 900;
GO

  • В качестве выражения expression в предложении VALUES инструкции INSERT:

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

Ограничения CHECK

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

Определения DEFAULT

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

Вычисляемые столбцы

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

Операторы присваивания

Операторы присваивания (left_operand = right_operand) могут вызывать пользовательские функции, возвращающие скалярное значение, в выражении, указанном правым операндом.

Инструкции управления потоком

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

Выражения CASE

Пользовательские функции, возвращающие скалярное значение, можно вызывать в любых выражениях CASE.

Инструкции PRINT

Пользовательские функции, возвращающие строку символов, можно вызывать в инструкциях PRINT в качестве параметра string_expr.

Функции и хранимые процедуры

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

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

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

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

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

  • значения аргументов в скобки не заключаются;

  • можно указывать имена параметров;

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

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

IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

В следующем примере выполняется функция dbo.ufn_CubicVolume. С помощью инструкции Transact-SQL EXECUTE аргументы идентифицируются в порядке, отличном от порядка параметров в определении функции:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO

Ниже приведен пример выполнения функции dbo.ufn_CubicVolume без указания имен параметров:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume 12.3, 4.5, 4.5;
SELECT @MyDecimalVar;
GO

Для выполнения функции dbo.ufn_CubicVolume из приложений OLE DB или ODBC можно также использовать синтаксис ODBC CALL.

-- First use SQLBindParam to bind the return value parameter marker
-- to a program variable of the appropriate type
SQLExecDirect(hstmt,
              "{ CALL ? = dbo.ufn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);

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

Определяемую пользователем функцию, возвращающую значение типа table, можно вызвать там, где разрешены табличные выражения: в предложении FROM инструкций SELECT, INSERT, UPDATE и DELETE. Вызов пользовательской функции, возвращающей таблицу, может сопровождаться дополнительным псевдонимом таблицы. В следующем примере иллюстрируется вызов возвращающей табличное значение функции dbo.ufnGetContactInformation в предложении FROM инструкции SELECT.

USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

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

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

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

Вызов встроенных функций, возвращающих табличное значение

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

SELECT *
FROM sys.fn_helpcollations();
GO

Использование подсказок для функций, возвращающих табличное значение

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

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

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

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

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