CLR 스칼라 반환 함수

적용 대상:SQL Server

SVF(스칼라 반환 함수)는 문자열, 정수 또는 비트 값과 같은 단일 값을 반환합니다. .NET Framework 프로그래밍 언어를 사용하여 관리 코드에서 스칼라 반환 사용자 정의 함수를 만들 수 있습니다. 이러한 함수는 Transact-SQL 또는 다른 관리 코드에서 액세스할 수 있습니다. CLR 통합의 이점과 관리 코드와 Transact-SQL 중에서 선택하는 방법에 대한 자세한 내용은 CLR 통합 개요를 참조하세요.

CLR 스칼라 반환 함수에 대한 요구 사항

.NET Framework SVF는 .NET Framework 어셈블리 클래스의 메서드로 구현됩니다. 입력 매개 변수와 SVF에서 반환된 형식은 varchar, char, rowversion, text, ntext, image, timestamp, table 또는 cursor를 제외하고 SQL Server 지원하는 스칼라 데이터 형식일 수 있습니다. SVF는 SQL Server 데이터 형식과 구현 메서드의 반환 데이터 형식 간에 일치하는지 확인해야 합니다. 형식 변환에 대한 자세한 내용은 CLR 매개 변수 데이터 매핑을 참조하세요.

.NET Framework 언어로 .NET Framework SVF를 구현하는 경우 SqlFunction 사용자 지정 특성을 지정하여 함수에 대한 추가 정보를 포함할 수 있습니다. SqlFunction 특성은 함수가 데이터를 액세스 또는 수정하는지, 결정적인지 여부 및 함수에 부동 소수점 연산이 포함되는지 여부를 나타냅니다.

스칼라 반환 사용자 정의 함수는 결정적이거나 비결정적일 수 있습니다. 결정적 함수는 특정 입력 매개 변수 집합으로 호출될 때 항상 같은 결과를 반환합니다. 비결정적 함수는 특정 입력 매개 변수 집합으로 호출될 때 다른 결과를 반환할 수 있습니다.

테이블 반환 매개 변수

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

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

Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
Public Class T  
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _  
    Public Shared Function ReturnOrderCount() As Integer  
        Using conn As New SqlConnection("context connection=true")  
            conn.Open()  
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
            Return CType(cmd.ExecuteScalar(), Integer)  
        End Using  
    End Function  
End Class  

코드의 첫 번째 줄에서는 Microsoft.SqlServer.Server 를 참조하여 특성에 액세스하고 System.Data.SqlClient 를 참조하여 ADO.NET 네임스페이스에 액세스합니다. (이 네임스페이스에는 SQL Server .NET Framework 데이터 공급자인 SqlClient가 포함되어 있습니다.)

그런 다음 이 함수는 SqlFunction 네임스페이스에 있는 Microsoft.SqlServer.Server 사용자 지정 특성을 받습니다. 사용자 지정 특성은 UDF(사용자 정의 함수)에서 in-process 공급자를 사용하여 서버의 데이터를 읽는지 여부를 나타냅니다. SQL Server UDF가 데이터를 업데이트, 삽입 또는 삭제하는 것을 허용하지 않습니다. SQL Server in-process 공급자를 사용하지 않는 UDF의 실행을 최적화할 수 있습니다. 이렇게 하려면 DataAccessKind 를 DataAccessKind.None으로 설정합니다. 다음 줄에서 대상 메서드는 public static(Visual Basic .NET의 경우 공유) 메서드입니다.

Microsoft.SqlServer.Server 네임스페이스에 있는 SqlContext 클래스는 이미 설정된 SQL Server instance 대한 연결로 SqlCommand 개체에 액세스할 수 있습니다. 여기서는 사용되지 않지만 System.Transactions API(애플리케이션 프로그래밍 인터페이스)를 통해 현재 트랜잭션 컨텍스트를 사용할 수도 있습니다.

함수 본문에 있는 대부분의 줄은 System.Data.SqlClient 네임스페이스에 있는 형식을 사용하는 클라이언트 애플리케이션을 작성해 본 개발자에게 익숙한 코드여야 합니다.

[C#]

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

[Visual Basic]

Using conn As New SqlConnection("context connection=true")  
   conn.Open()  
   Dim cmd As New SqlCommand( _  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
   Return CType(cmd.ExecuteScalar(), Integer)  
End Using  

SqlCommand 개체를 초기화하여 적절한 명령 텍스트가 지정됩니다. 앞의 예에서는 SalesOrderHeader테이블의 행 수를 계산합니다. 그런 다음 ExecuteScalar 개체의 cmd 메서드가 호출됩니다. 이렇게 하면 쿼리를 기반으로 int 형식의 값이 반환됩니다. 마지막으로 주문 횟수가 호출자에게 반환됩니다.

이 코드를 FirstUdf.cs라는 파일에 저장하면 다음과 같이 어셈블리로 컴파일할 수 있습니다.

[C#]

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

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb  

어셈블리와 UDF를 등록할 Transact-SQL 쿼리 및 샘플 호출은 다음과 같습니다.

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에 노출되는 함수 이름이 대상 공용 정적 메서드 이름과 일치할 필요는 없습니다.

참고 항목

CLR 매개 변수 데이터 매핑
CLR 통합 사용자 지정 특성 개요
사용자 정의 함수
CLR 데이터베이스 개체에서 데이터 액세스