다음을 통해 공유

  • 아티클

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 선언에 사용된 매개 변수 수와 같아야 합니다.

관리 코드에 해당 형식이 있는 모든 네이티브 SQL Server 형식의 매개 변수를 CLR 저장 프로시저에 전달할 수 있습니다. Transact-SQL 구문으로 프로시저를 만들려면 이러한 형식을 지정할 때 가장 가까운 네이티브 SQL Server 형식을 사용해야 합니다. 형식 변환에 대한 자세한 내용은 CLR 매개 변수 데이터 매핑을 참조하십시오.

테이블 반환 매개 변수

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

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

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

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

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

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

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

C#

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 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을 사용하여 데이터베이스에 프로시저를 만들고 sum을 OUTPUT 매개 변수로 지정합니다.

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

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

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

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

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

메시지 반환

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 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 구현을 사용하여 데이터를 미리 처리하려면 SqlDataReader를 사용하는 Send 메서드의 오버로드를 사용합니다. 이 방법은 앞에서 설명한 직접적인 방법보다 조금 느리기는 하지만 데이터를 클라이언트로 보내기 전에 조작할 수 있다는 융통성이 있습니다.

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

[Visual Basic]

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

[Visual Basic]

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를 사용하여 테이블 형식 결과를 보냅니다.

이러한 예는 이해를 돕기 위한 목적으로만 사용되었습니다. 계산을 많이 수행하는 응용 프로그램의 경우 단순한 Transact-SQL 문보다 CLR 함수가 적합합니다. 앞의 예와 거의 비슷한 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

[!참고]

SQL Server 2005부터 호환성 수준이 "80"인 SQL Server 데이터베이스에서는 관리되는 사용자 정의 형식, 저장 프로시저, 함수, 집계 또는 트리거를 만들 수 없습니다. SQL Server의 이러한 CLR 통합 기능을 활용하려면 sp_dbcmptlevel 저장 프로시저를 사용하여 데이터베이스 호환성 수준을 "100"으로 설정해야 합니다.