다음을 통해 공유

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 매개 변수 데이터 매핑을 참조하세요.

Table-Valued 매개 변수

프로시저 또는 함수로 전달되는 사용자 정의 테이블 형식인 TVP(테이블 반환 매개 변수)를 사용하면 여러 개의 데이터 행을 서버로 편리하게 전달할 수 있습니다. TVP는 매개 변수 배열과 유사한 기능을 제공하지만 Transact-SQL과 더 유연하고 긴밀한 통합을 제공합니다. 또한 성능도 향상될 수 있습니다. 또한 TVP는 서버 왕복 횟수를 줄이는 데 도움이 될 수 있습니다. 스칼라 매개 변수 목록과 같이 여러 요청을 서버에 보내는 대신 데이터를 서버에 TVP로 보낼 수 있습니다. 사용자 정의 테이블 형식은 SQL Server 프로세스에서 실행되는 관리되는 저장 프로시저 또는 함수에 테이블 반환 매개 변수로 전달되거나 반환될 수 없습니다. TVP에 대한 자세한 내용은 Table-Valued 매개 변수 사용(데이터베이스 엔진)을 참조하세요.

CLR 저장 프로시저에서 결과 반환

여러 가지 방법으로 .NET Framework 저장 프로시저에서 정보를 반환할 수 있습니다. 여기에는 출력 매개 변수, 테이블 형식 결과 및 메시지가 포함됩니다.

OUTPUT 매개 변수 및 CLR 저장 프로시저

Transact-SQL 저장 프로시저와 마찬가지로 OUTPUT 매개 변수를 사용하여 .NET Framework 저장 프로시저에서 정보를 반환할 수 있습니다. .NET Framework 저장 프로시저를 만드는 데 사용되는 Transact-SQL DML 구문은 Transact-SQL로 작성된 저장 프로시저를 만드는 데 사용되는 구문과 동일합니다. .NET Framework 클래스의 구현 코드에 있는 해당 매개 변수는 pass-by-reference 매개 변수를 인수로 사용해야 합니다. Visual Basic은 C#과 동일한 방식으로 출력 매개 변수를 지원하지 않습니다. 다음과 같이 매개 변수를 참조로 지정하고 출력 매개 변수를 나타내려면 Out()> 특성을 적용<해야 합니다.

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

다음은 OUTPUT 매개 변수를 통해 정보를 반환하는 저장 프로시저를 보여줍니다.

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

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
Imports 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 Sub  
End Class

위의 CLR 저장 프로시저를 포함하는 어셈블리가 서버에 빌드되고 생성되면 다음 Transact-SQL 사용하여 데이터베이스에서 프로시저를 만들고 합계 를 OUTPUT 매개 변수로 지정합니다.

CREATE PROCEDURE PriceSum (@sum int OUTPUT)  
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum  
-- if StoredProcedures class was inside a namespace, called MyNS,  
-- you would use:  
-- AS EXTERNAL NAME TestStoredProc.[MyNS.StoredProcedures].PriceSum

합계는 SQL Server 데이터 형식으로 int 선언되며 CLR 저장 프로시저에 정의된 값 매개 변수는 CLR 데이터 형식으로 SqlInt32 지정됩니다. 호출 프로그램이 CLR 저장 프로시저를 실행하면 SQL Server는 SqlInt32 CLR 데이터 형식을 SQL Server 데이터 형식으로 int자동으로 변환합니다. 변환할 수 있고 변환할 수 없는 CLR 데이터 형식에 대한 자세한 내용은 CLR 매개 변수 데이터 매핑을 참조하세요.

테이블 형식 결과 및 메시지 반환

테이블 형식 결과 및 메시지를 클라이언트에 반환하는 작업은 클래스의 SqlContext 속성을 사용하여 가져오는 개체를 Pipe 통해 SqlPipe 수행됩니다. SqlPipe 개체에 메서드가 있습니다Send. 메서드를 호출하여 파이프를 Send 통해 데이터를 호출 애플리케이션으로 전송할 수 있습니다.

이러한 오버로드는 단순히 텍스트 문자열을 SqlPipe.Send 보내는 메서드와 보내는 SqlDataReader 오버로드를 포함하여 메서드의 여러 오버로드입니다.

메시지 반환

클라이언트 애플리케이션에 메시지를 보내는 데 사용합니다 SqlPipe.Send(string) . 메시지의 텍스트는 8000자로 제한됩니다. 메시지가 8000자를 초과하면 잘립니다.

테이블 형식 결과 반환

쿼리 결과를 클라이언트에 직접 보내려면 개체에서 메서드 SqlPipe 의 Execute 오버로드 중 하나를 사용합니다. 데이터가 관리되는 메모리에 복사되지 않고 네트워크 버퍼로 전송되므로 클라이언트에 결과를 반환하는 가장 효율적인 방법입니다. 다음은 그 예입니다.

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

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports 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 Sub  
End Class

In-process 공급자를 통해 이전에 실행된 쿼리의 결과를 보내거나 사용자 지정 구현 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);  
      }  
   }  
}  

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports 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 Sub  
End 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);  
   }  
}  

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports 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 Sub  
End 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);  
      }  
   }  
}  

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports 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 Sub  
End Class

첫 번째는 Send 클라이언트에 메시지를 보내고, 두 번째는 .를 사용하여 SqlDataReader테이블 형식 결과를 보냅니다.

이러한 예제는 설명용으로만 사용됩니다. CLR 함수는 계산 집약적 애플리케이션에 대한 간단한 Transact-SQL 문보다 더 적합합니다. 이전 예제와 거의 동일한 Transact-SQL 저장 프로시저는 다음과 같습니다.

CREATE PROCEDURE HelloWorld() AS  
BEGIN  
PRINT('Hello world!')  
SELECT ProductNumber FROM ProductMaster  
END;

비고

메시지 및 결과 집합은 클라이언트 애플리케이션에서 다르게 검색됩니다. 예를 들어 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부터 컴파일된 /clr:pure Visual C++ 데이터베이스 개체(예: 저장 프로시저)는 실행이 지원되지 않습니다.

다음 DDL을 사용하여 결과 어셈블리를 등록하고 진입점을 호출할 수 있습니다.

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll';  
CREATE PROCEDURE HelloWorld  
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;  
EXEC HelloWorld;

또한 참조하십시오

CLR User-Defined 함수
CLR User-Defined 형식
CLR 트리거

  • Last updated on