Скалярные функции среды CLR
Скалярная функция (SVF) возвращает единственное значение, например строку, целочисленное или битовое значение. Определяемые пользователем скалярные функции можно создавать в управляемом коде на любом языке программирования платформы .NET Framework. Эти функции доступны для Transact-SQL и другого управляемого кода. Сведения о преимуществах интеграции со средой CLR и выборе между управляемым кодом и Transact-SQL см. в статье Общие сведения об интеграции со средой CLR.
Требования к скалярным функциям среды CLR
Скалярные функции .NET Framework реализуются в виде методов класса в сборке .NET Framework. Входные параметры и тип, возвращаемые из SVF, могут быть любым из скалярных типов данных, поддерживаемых SQL Server, за исключением , , , , textntext, image, timestamptable, или cursor. rowversioncharvarchar SvFs должны обеспечивать соответствие между типом данных SQL Server и типом возвращаемых данных метода реализации. Дополнительные сведения о преобразованиях типов см. в разделе Сопоставление данных параметров CLR.
При реализации скалярной функции .NET Framework на одном из языков .NET Framework можно включить дополнительные сведения о функции, задав пользовательский атрибут SqlFunction. Атрибут SqlFunction указывает, получает ли функция доступ к данным или вносит изменения в данные, является ли детерминированной и предусматривает ли выполнение операций с плавающей запятой.
Определяемые пользователем скалярные функции могут быть детерминированными или недетерминированными. Детерминированная функция всегда возвращает один и тот же результат при вызове с конкретным набором входных параметров. Недетерминированная функция может возвращать разные результаты при вызове с конкретным набором входных параметров.
Примечание
Не следует помечать функцию как детерминированную, если она не всегда выдает одинаковые выходные значения при передаче одинаковых входных значений и при одинаковом состоянии базы данных. Не следует определять функцию как детерминированную, если в действительности она таковой не является. Это может привести к искажению индексированных представлений и вычисляемых столбцов. Определить функцию как детерминированную можно, задав для свойства IsDeterministic значение true.
Параметры, возвращающие табличные значения
Возвращающие табличное значение параметры — это определяемые пользователем табличные типы, которые передаются в процедуру или функцию, предоставляя эффективный способ передачи на сервер нескольких строк данных. 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
Примечание
/t:library указывает, что результатом компиляции должна быть библиотека, а не исполняемый объект. Исполняемые файлы не могут быть зарегистрированы в SQL Server.
Примечание
Объекты базы данных Visual C++, скомпилированные с /clr:pure помощью , не поддерживаются для выполнения на SQL Server. В частности, такими объектами базы данных являются скалярные функции.
Ниже приводится запрос 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 для работы с базами данных