CLR 저장 프로시저

아티클
04/27/2017

저장 프로시저는 스칼라 식에서 사용할 수 없는 루틴입니다. 스칼라 함수와 달리 저장 프로시저는 테이블 형식의 결과와 메시지를 클라이언트에 반환하고 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 매개 변수 데이터 매핑을 참조하세요.

테이블 반환 매개 변수

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

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

정보는 여러 가지 방법으로 .NET Framework 저장 프로시저에서 반환될 수 있습니다. 정보를 반환할 수 있습니다.

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

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

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 CLR 데이터 형식을 SQL Server 데이터 형식으로 int자동으로 변환합니다SqlInt32. 변환할 수 있고 변환할 수 없는 CLR 데이터 형식에 대한 자세한 내용은 CLR 매개 변수 데이터 매핑을 참조하세요.

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

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

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

메시지 반환

SqlPipe.Send(string)를 사용하여 메시지를 클라이언트 애플리케이션에 보낼 수 있습니다. 메시지 텍스트는 8000자로 제한되며 8000자를 초과하면 잘립니다.

테이블 형식 결과 반환

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

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는 클라이언트에 메시지를 보내고 두 번째 Send는 SqlDataReader를 사용하여 테이블 형식 결과를 보냅니다.

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

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

참고

클라이언트 애플리케이션에서는 메시지와 결과 집합이 다른 방식으로 검색됩니다. instance 경우 결과 보기에 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 사용자 정의 함수
CLR 사용자 정의 형식
CLR 트리거

다음을 통해 공유