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

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

  • Статья

Область применения:SQL Server

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

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

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

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

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

Примечание.

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

Параметры с табличным значением

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

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

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

Вот простой SVF, который обращается к данным и возвращает целочисленное значение:

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();
        }
    }
}

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

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

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

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

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();
}

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

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

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

/t:library указывает, что результатом компиляции должна быть библиотека, а не исполняемый объект. Исполняемые файлы нельзя зарегистрировать в SQL Server.

Объекты базы данных Visual C++, скомпилированные с /clr:pure, не поддерживаются для выполнения в SQL Server. В частности, такими объектами базы данных являются скалярные функции.

Запрос Transact-SQL и пример вызова для регистрации сборки и UDF:

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, не обязательно соответствует имени целевого общедоступного статического метода.

Связанное содержимое