Статья
04/01/2012

Хранимые процедуры CLR

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

Требования для хранимых процедур CLR

В среде CLR хранимые процедуры реализуются как общие статические методы класса в сборке Microsoft .NET Framework. Статический метод может быть объявлен как void или может возвратить целое значение. Если он возвращает целое значение, возвращенное целое число рассматривается как код возврата из процедуры. Например,

EXECUTE @return_status = procedure_name

Переменная @return_status содержит значение, возвращаемое методом. Если метод объявляется как void, код возврата равен 0.

Если метод принимает параметры, число параметров в реализации .NET Framework должно быть равно числу параметров, используемых в декларации Transact-SQL хранимой процедуры.

Параметры, передаваемые в хранимую процедуру CLR, могут быть любого собственного типа SQL Server, имеющего эквивалент в управляемом коде. Для синтаксиса Transact-SQL, используемого при создании процедуры, такие типы должны задаваться при помощи наиболее подходящего эквивалента собственного типа SQL Server. Дополнительные сведения о преобразованиях типов см. в разделе Сопоставление данных о параметрах CLR.

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

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

Возврат результатов хранимых процедур CLR

Возврат данных из хранимых процедур .NET Framework может осуществляться несколькими способами. Это относится к выходным параметрам, табличным результатам и сообщениям.

Параметры OUTPUT и хранимые процедуры CLR

Так же, как и для хранимых процедур Transact-SQL, данные могут возвращаться из хранимых процедур .NET Framework при помощи параметров, описанных с ключевым словом OUTPUT. Синтаксис Transact-SQL DML, используемый для создания хранимых процедур .NET Framework, тот же, что и синтаксис, используемый для создания хранимых процедур, написанных на Transact-SQL. Соответствующий параметр в коде реализации в классе .NET Framework должен использовать в качестве аргумента параметр, передаваемый по ссылке. Следует отметить, что язык Visual Basic не поддерживает выходные параметры так, как они поддерживаются в языке Visual C#. Необходимо передать параметр по ссылке и применить атрибут <Out()> для указания параметра OUTPUT, как в следующем примере:

Imports System.Runtime.InteropServices…Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32)

Следующий пример представляет хранимую процедуру с входным и выходным параметрами.

using System;using System.Data.SqlTypes;using System.Data.SqlClient;using Microsoft.SqlServer.Server; public class StoredProcedures {   [Microsoft.SqlServer.Server.SqlProcedure]   public static void PriceSum(out SqlInt32 value)   {      using(SqlConnection connection = new SqlConnection("context connection=true"))       {         value = 0;         connection.Open();         SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);         SqlDataReader reader = command.ExecuteReader();                  using (reader)         {            while( reader.Read() )            {               value += reader.GetSqlInt32(0);            }         }               }   }}

Visual Basic

Imports SystemImports System.DataImports System.Data.SqlImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.Data.SqlClientImports System.Runtime.InteropServices'The Partial modifier is only required on one class definition per project.Partial Public Class StoredProcedures     ''' <summary>    ''' Executes a query and iterates over the results to perform a summation.    ''' </summary>    <Microsoft.SqlServer.Server.SqlProcedure> _    Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)                Using connection As New SqlConnection("context connection=true")           value = 0           Connection.Open()           Dim command As New SqlCommand("SELECT Price FROM Products", connection)           Dim reader As SqlDataReader           reader = command.ExecuteReader()           Using reader              While reader.Read()                 value += reader.GetSqlInt32(0)              End While           End Using        End Using            End SubEnd Class

Когда сборка, содержащая указанную выше хранимую процедуру CLR, построена и создана на сервере, используются следующие конструкции Transact-SQL для создания процедуры в базе данных, и для параметра sum указывается ключевое слово OUTPUT.

CREATE PROCEDURE PriceSum (@sum int OUTPUT)AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum

Следует заметить, что параметр sum объявляется с типом данных SQL Server int, а параметр value, заданный в хранимой процедуре CLR, задан с типом данных CLRSqlInt32. Когда вызывающая программа выполняет хранимую процедуру CLR, SQL Server автоматически преобразует тип данных CLR SqlInt32 в тип данных intSQL Server. Дополнительные сведения о том, какие типы данных CLR можно и нельзя преобразовать, см. в разделе Сопоставление данных о параметрах CLR.

Возврат табличных результатов и сообщений

Возврат клиенту табличных результатов и сообщений выполняется через объект SqlPipe, получаемый при использовании свойства Pipe класса SqlContext. Объект SqlPipe имеет метод Send. Вызвав метод Send, можно передать данные по каналу вызывающему приложению.

Далее приводятся несколько перегрузок метода SqlPipe.Send, включая перегрузку, которая посылает объект SqlDataReader, а также другую перегрузку, которая просто посылает текстовую строку.

Возврат сообщений

Метод SqlPipe.Send(string) используется для передачи сообщений в клиентское приложение. Текст сообщения ограничен 8000 символов. Если размер сообщения превышает 8000 символов, сообщение усекается.

Возврат табличных результатов

Чтобы послать результаты запроса непосредственно клиенту, используется один из перегруженных методов Execute на объекте SqlPipe. Это наиболее эффективный способ возврата результатов клиенту, поскольку данные передаются в сетевые буферы без копирования в управляемую память. Например,

[C#]

using System;using System.Data;using System.Data.SqlTypes;using System.Data.SqlClient;using Microsoft.SqlServer.Server; public class StoredProcedures {   /// <summary>   /// Execute a command and send the results to the client directly.   /// </summary>   [Microsoft.SqlServer.Server.SqlProcedure]   public static void ExecuteToClient()   {   using(SqlConnection connection = new SqlConnection("context connection=true"))    {      connection.Open();      SqlCommand command = new SqlCommand("select @@version", connection);      SqlContext.Pipe.ExecuteAndSend(command);      }   }}

[Visual Basic]

Imports SystemImports System.DataImports System.Data.SqlImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.Data.SqlClient'The Partial modifier is only required on one class definition per project.Partial Public Class StoredProcedures     ''' <summary>    ''' Execute a command and send the results to the client directly.    ''' </summary>    <Microsoft.SqlServer.Server.SqlProcedure> _    Public Shared Sub ExecuteToClient()        Using connection As New SqlConnection("context connection=true")            connection.Open()            Dim command As New SqlCommand("SELECT @@VERSION", connection)            SqlContext.Pipe.ExecuteAndSend(command)        End Using    End SubEnd Class

Чтобы послать результаты ранее выполненного запроса через внутрипроцессного поставщика, или чтобы предварительно обработать данные с использованием пользовательской реализации SqlDataReader, используется перегрузка метода Send, которая принимает SqlDataReader. Этот метод немного медленнее, чем описанный выше непосредственный метод, но он обеспечивает большую гибкость при обработке данных перед их отправкой клиенту.

using System;using System.Data;using System.Data.SqlTypes;using System.Data.SqlClient;using Microsoft.SqlServer.Server; public class StoredProcedures {   /// <summary>   /// Execute a command and send the resulting reader to the client   /// </summary>   [Microsoft.SqlServer.Server.SqlProcedure]   public static void SendReaderToClient()   {      using(SqlConnection connection = new SqlConnection("context connection=true"))       {         connection.Open();         SqlCommand command = new SqlCommand("select @@version", connection);         SqlDataReader r = command.ExecuteReader();         SqlContext.Pipe.Send(r);      }   }}

[Visual Basic]

Imports SystemImports System.DataImports System.Data.SqlImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.Data.SqlClient'The Partial modifier is only required on one class definition per project.Partial Public Class StoredProcedures     ''' <summary>    ''' Execute a command and send the results to the client directly.    ''' </summary>    <Microsoft.SqlServer.Server.SqlProcedure> _    Public Shared Sub SendReaderToClient()        Using connection As New SqlConnection("context connection=true")            connection.Open()            Dim command As New SqlCommand("SELECT @@VERSION", connection)            Dim reader As SqlDataReader            reader = command.ExecuteReader()            SqlContext.Pipe.Send(reader)        End Using    End SubEnd Class

Чтобы создать динамический результирующий набор, заполнить его и послать клиенту, можно создать записи на текущем соединении и послать их при помощи SqlPipe.Send.

using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server; using System.Data.SqlTypes;public class StoredProcedures {   /// <summary>   /// Create a result set on the fly and send it to the client.   /// </summary>   [Microsoft.SqlServer.Server.SqlProcedure]   public static void SendTransientResultSet()   {      // Create a record object that represents an individual row, including it's metadata.      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));            // Populate the record.      record.SetSqlString(0, "Hello World!");            // Send the record to the client.      SqlContext.Pipe.Send(record);   }}

[Visual Basic]

Imports SystemImports System.DataImports System.Data.SqlImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.Data.SqlClient'The Partial modifier is only required on one class definition per project.Partial Public Class StoredProcedures     ''' <summary>    ''' Create a result set on the fly and send it to the client.    ''' </summary>    <Microsoft.SqlServer.Server.SqlProcedure> _    Public Shared Sub SendTransientResultSet()        ' Create a record object that represents an individual row, including it's metadata.        Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )        ' Populate the record.        record.SetSqlString(0, "Hello World!")        ' Send the record to the client.        SqlContext.Pipe.Send(record)            End SubEnd Class

Здесь представлен пример отправки табличных результатов и сообщения через SqlPipe.

using System.Data.SqlClient;using Microsoft.SqlServer.Server; public class StoredProcedures {   [Microsoft.SqlServer.Server.SqlProcedure]   public static void HelloWorld()   {      SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");      using(SqlConnection connection = new SqlConnection("context connection=true"))       {         connection.Open();         SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);         SqlDataReader reader = command.ExecuteReader();         SqlContext.Pipe.Send(reader);      }   }}

[Visual Basic]

Imports SystemImports System.DataImports System.Data.SqlImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.Data.SqlClient'The Partial modifier is only required on one class definition per project.Partial Public Class StoredProcedures     ''' <summary>    ''' Execute a command and send the results to the client directly.    ''' </summary>    <Microsoft.SqlServer.Server.SqlProcedure> _    Public Shared Sub HelloWorld()        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")        Using connection As New SqlConnection("context connection=true")            connection.Open()            Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)            Dim reader As SqlDataReader            reader = command.ExecuteReader()            SqlContext.Pipe.Send(reader)        End Using    End SubEnd Class

Первый метод Send отправляет клиенту сообщение, а второй — табличные результаты с помощью объекта SqlDataReader.

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

CREATE PROCEDURE HelloWorld() ASBEGINPRINT('Hello world!')SELECT ProductNumber FROM ProductMasterEND

Примечание
Сообщения и результирующие наборы в клиентском приложении получаются по-разному. Например, результирующие наборы в среде Среда SQL Server Management Studio отображаются в представлении Результаты, а сообщения — в области Сообщения.

Сообщения и результирующие наборы в клиентском приложении получаются по-разному. Например, результирующие наборы в среде Среда SQL Server Management Studio отображаются в представлении Результаты, а сообщения — в области Сообщения.

Если сохранить приведенный выше код на языке Visual C# в файл с именем MyFirstUdp.cs и скомпилировать его следующей командой:

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs

Или если сохранить приведенный выше код на языке Visual Basic в файл с именем MyFirstUdp.vb и скомпилировать его следующей командой:

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb

Примечание
Начиная с версии SQL Server 2005 выполнение объектов баз данных языка Visual C++ (например, хранимых процедур), скомпилированных с параметром /clr:pure, не поддерживается.

Следующая инструкция DDL регистрирует результирующую сборку и вызывает точку входа:

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll'CREATE PROCEDURE HelloWorldAS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorldEXEC HelloWorld

Примечание
Начиная с SQL Server 2005 в базе данных SQL Server с уровнем совместимости 80 нельзя создавать определяемые пользователем типы, хранимые процедуры, функции, статистические выражения и триггеры. Чтобы пользоваться этими функциями интеграции со средой CLR SQL Server, необходимо при помощи хранимой процедуры sp_dbcmptlevel задать уровень совместимости базы данных 100.

Начиная с SQL Server 2005 в базе данных SQL Server с уровнем совместимости 80 нельзя создавать определяемые пользователем типы, хранимые процедуры, функции, статистические выражения и триггеры. Чтобы пользоваться этими функциями интеграции со средой CLR SQL Server, необходимо при помощи хранимой процедуры sp_dbcmptlevel задать уровень совместимости базы данных 100.