Скалярные функции среды CLR

Применимо к:SQL Server

Скалярная функция возвращает единственное значение, например строку, целочисленное или битовое значение. Вы можете создавать определяемые пользователем скалярные функции в управляемом коде с помощью любого языка программирования платформа .NET Framework. Эти функции доступны для Transact-SQL и другого управляемого кода. Сведения о преимуществах интеграции со средой CLR и выборе между управляемым кодом и Transact-SQL см. в статье Общие сведения об интеграции со средой CLR.

Требования к скалярным функциям среды CLR

Скалярные функции .NET Framework реализуются в виде методов класса в сборке .NET Framework. Входные параметры и тип, возвращаемые из SVF, могут быть любым из скалярных типов данных, поддерживаемых SQL Server, за исключением varchar, char, rowversion, text, ntext, image, timestamp, table или cursor. SvFs должны обеспечивать соответствие между типом данных SQL Server и типом возвращаемых данных метода реализации. Дополнительные сведения о преобразованиях типов см. в разделе Сопоставление данных параметров CLR.

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

Определяемые пользователем скалярные функции могут быть детерминированными или недетерминированными. Детерминированная функция всегда возвращает один и тот же результат при вызове с конкретным набором входных параметров. Недетерминированная функция может возвращать разные результаты при вызове с конкретным набором входных параметров.

Параметры, возвращающие табличные значения

Возвращающие табличное значение параметры — это определяемые пользователем табличные типы, которые передаются в процедуру или функцию, предоставляя эффективный способ передачи на сервер нескольких строк данных. TvP предоставляют функции, аналогичные массивам параметров, но обеспечивают большую гибкость и более тесную интеграцию с Transact-SQL. Они также обеспечивают возможность повышения производительности. Кроме того, возвращающие табличное значение параметры способствуют сокращению циклов приема-передачи данных с сервера и на сервер. Вместо того чтобы отправлять на сервер несколько запросов (как в случае списка скалярных параметров), данные можно отправить в виде возвращающего табличное значение параметра. Определяемый пользователем тип таблицы не может передаваться в качестве возвращаемого табличного значения параметру управляемой хранимой процедуре или функции, выполняющейся в процессе SQL Server, или возвращаться из нее. Дополнительные сведения о TVP см. в разделе Использование параметров Table-Valued (ядро СУБД).

Пример скалярной функции среды CLR

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

using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
  
public class T  
{  
    [SqlFunction(DataAccess = DataAccessKind.Read)]  
    public static int ReturnOrderCount()  
    {  
        using (SqlConnection conn   
            = new SqlConnection("context connection=true"))  
        {  
            conn.Open();  
            SqlCommand cmd = new SqlCommand(  
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
            return (int)cmd.ExecuteScalar();  
        }  
    }  
}  

Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
Public Class T  
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _  
    Public Shared Function ReturnOrderCount() As Integer  
        Using conn As New SqlConnection("context connection=true")  
            conn.Open()  
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
            Return CType(cmd.ExecuteScalar(), Integer)  
        End Using  
    End Function  
End Class  

В первой строке кода содержится ссылка на объект Microsoft.SqlServer.Server для доступа к атрибутам и на объект System.Data.SqlClient для доступа к пространству имен ADO.NET. (Это пространство имен содержит SqlClient, поставщик данных платформа .NET Framework для SQL Server.)

Далее функция получает пользовательский атрибут SqlFunction , относящийся к пространству имен Microsoft.SqlServer.Server . Пользовательский атрибут указывает, используется ли определяемая пользователем функция (UDF) внутрипроцессным поставщиком для чтения данных с сервера. SQL Server не позволяет пользовательским функциям обновлять, вставлять или удалять данные. SQL Server может оптимизировать выполнение определяемой пользователем функции, которая не использует внутрипроцессный поставщик. На это указывает параметр DataAccessKind , имеющий значение DataAccessKind.None. На следующей строке целевой метод определяется как public static (или на языке Visual Basic .NET — shared).

Затем класс SqlContext, расположенный в пространстве имен Microsoft.SqlServer.Server, может получить доступ к объекту SqlCommand с подключением к уже настроенном экземпляру SQL Server. Кроме того, становится доступным в рамках API System.Transactions контекст текущей транзакции, хотя в данном случае не используется.

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

[C#]

using(SqlConnection conn = new SqlConnection("context connection=true"))   
{  
   conn.Open();  
   SqlCommand cmd = new SqlCommand(  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
   return (int) cmd.ExecuteScalar();  
}    

[Visual Basic]

Using conn As New SqlConnection("context connection=true")  
   conn.Open()  
   Dim cmd As New SqlCommand( _  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
   Return CType(cmd.ExecuteScalar(), Integer)  
End Using  

Необходимый текст команды можно задать путем инициализации объекта SqlCommand . В предыдущем примере подсчитывалось число строк в таблице SalesOrderHeader. Далее вызывается метод ExecuteScalar объекта cmd . Он возвращает значение типа int на основе запроса. Наконец происходит возврат сведений о количестве заказов в вызывающий код.

После сохранения в файле с именем FirstUdf.cs этот код можно скомпилировать в сборку следующим образом:

[C#]

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs   

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb  

Ниже приводится запрос Transact-SQL и образец вызова для регистрации сборки и определяемой пользователем функции.

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';  
GO  
  
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT   
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;   
GO  
  
SELECT dbo.CountSalesOrderHeader();  
GO  
  

Обратите внимание, что имя функции в Transact-SQL не обязательно должно соответствовать имени общего статического целевого метода.

См. также:

Сопоставление данных о параметрах CLR
Общие сведения о пользовательских атрибутах интеграции со средой CLR
Определяемые пользователем функции
Доступ к данным из объектов среды CLR для работы с базами данных