사용자 정의 함수 만들기(데이터베이스 엔진)

아티클
08/23/2023

적용 대상:SQL Server Azure SQL Database Azure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System(PDW)

이 문서에서는 Transact-SQL을 사용하여 SQL Server에서 UDF(사용자 정의 함수)를 만드는 방법을 설명합니다.

제한 사항

사용자 정의 함수는 데이터베이스 상태를 수정하는 작업을 수행하는 데 사용할 수 없습니다.
사용자 정의 함수는 테이블을 대상으로 하는 절을 포함 OUTPUT INTO 할 수 없습니다.
사용자 정의 함수는 여러 결과 집합을 반환할 수 없습니다. 여러 결과 집합을 반환해야 하는 경우 저장 프로시저를 사용합니다.
오류 처리는 사용자 정의 함수에서 제한됩니다. UDF는 지원 TRY...CATCH@ERROR 되지 않습니다. 또는 RAISERROR.
사용자 정의 함수는 저장 프로시저를 호출할 수 없지만 확장 저장 프로시저를 호출할 수 있습니다.
사용자 정의 함수는 동적 SQL 또는 임시 테이블을 사용할 수 없습니다. 테이블 변수는 허용됩니다.
SET 문은 사용자 정의 함수에서 허용되지 않습니다.
절은 FOR XML 허용되지 않습니다.
사용자 정의 함수는 중첩될 수 있습니다. 즉, 하나의 사용자 정의 함수가 다른 사용자 정의 함수를 호출할 수 있습니다. 중첩 수준은 호출된 함수의 실행이 시작되면 늘어나고 호출된 함수의 실행이 끝나면 줄어듭니다. 사용자 정의 함수는 최대 32 수준까지 중첩될 수 있습니다. 최대 중첩 수준을 초과하면 전체 함수 호출 체인이 실패합니다. Transact-SQL 사용자 정의 함수의 관리 코드 참조는 32 수준의 중첩 제한에 대해 한 수준으로 계산됩니다. 관리 코드 내에서 호출된 메서드는 이 제한에 따라 계산되지 않습니다.
Transact-SQL 사용자 정의 함수의 정의에는 다음 Service Broker 문을 포함 할 수 없습니다.
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND

사용 권한

데이터베이스에 대한 CREATE FUNCTION 권한과 함수가 생성되는 스키마에 대한 ALTER 권한이 필요합니다. 함수가 사용자 정의 형식을 지정하면 해당 유형에 대한 EXECUTE 권한이 필요합니다.

스칼라 함수 예제

스칼라 함수(스칼라 UDF)

다음 예제에서는 AdventureWorks2022 데이터베이스에 스칼라 UDF(다중 문 스칼라 함수) 를 만듭니다. 이 함수는 입력 값 1개를 ProductID사용하고 단일 데이터 값인 인벤토리에 지정된 제품의 집계된 수량을 반환합니다.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory p
    WHERE p.ProductID = @ProductID
        AND p.LocationID = '6';
     IF (@ret IS NULL)
        SET @ret = 0;
    RETURN @ret;
END;

다음 예제에서는 함수를 ufnGetInventoryStock 사용하여 75에서 80 사이의 제품에 대한 현재 재고 수량을 반환합니다 ProductModelID .

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;

스칼라 함수에 대한 자세한 내용과 예제는 CREATE FUNCTION(Transact-SQL)을 참조하세요.

테이블 반환 함수 예제

TVF(인라인 테이블 반환 함수)

다음 예제에서는 AdventureWorks2022 데이터베이스에 TVF(인라인 테이블 반환 함수)를 만듭니다. 이 함수는 하나의 입력 매개 변수, 고객(매장) ID를 사용하고, 열ProductIDName과 매장에 판매된 각 제품에 대한 연간 매출 YTD Total 집계를 반환합니다.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);

다음 예제에서는 함수를 호출하고 고객 ID 602를 지정합니다.

SELECT * FROM Sales.ufn_SalesByStore (602);

MSTVF(다중 문 테이블 반환 함수)

다음 예제에서는 AdventureWorks2022 데이터베이스에 MSTVF(다중 문 테이블 반환 함수)를 만듭니다. 함수에 EmployeeID 가 단일 입력 매개 변수로 입력되고 지정한 직원에게 직접 또는 간접적으로 보고하는 모든 직원의 목록이 반환됩니다. 그런 다음 직원 ID 109를 지정하여 함수가 호출됩니다.

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte
   RETURN
END;
GO

다음 예제에서는 함수를 호출하고 직원 ID 1을 지정합니다.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);

인라인 테이블 반환 함수(인라인 TVF) 및 MSTVF(다중 문 테이블 반환 함수)의 자세한 내용과 예제는 CREATE FUNCTION(Transact-SQL)을 참조하세요.

모범 사례

UDF(사용자 정의 함수)가 절을 사용하여 SCHEMABINDING 만들어지지 않은 경우 기본 개체에 대한 변경 내용은 함수 정의에 영향을 미치고 호출될 때 예기치 않은 결과를 생성할 수 있습니다. 기본 개체의 변경으로 인해 함수가 오래되지 않도록 하려면 다음 방법 중 하나를 구현하는 것이 좋습니다.

UDF를 WITH SCHEMABINDING 만들 때 절을 지정합니다. 이렇게 하면 함수도 수정되지 않는 한 함수 정의에서 참조되는 개체를 수정할 수 없습니다.
UDF 정의에 지정된 개체를 수정한 후 sp_refreshsqlmodule 저장 프로시저를 실행합니다.

데이터에 액세스하지 않는 UDF를 만드는 경우 옵션을 지정합니다 SCHEMABINDING . 이렇게 하면 쿼리 최적화 프로그램에서 이러한 UDF와 관련된 쿼리 계획에 불필요한 스풀 연산자를 생성하지 못하게 됩니다. 스풀 에 대한 자세한 내용은 Showplan 논리 및 물리 연산자 참조를 참조하세요. 스키마 바운드 함수를 만드는 방법은 스키마 바운드 함수를 참조하세요.

절에서 MSTVF에 조인 FROM 할 수 있지만 성능이 저하될 수 있습니다. SQL Server는 MSTVF에 포함할 수 있는 일부 문에서 최적화된 모든 기술을 사용할 수 없으므로 최적이 않은 쿼리 계획이 생성됩니다. 최상의 성능을 얻으려면 가능한 경우 함수 대신 기본 테이블 간에 조인을 사용합니다.

MSTVF의 고정 카디널리티 추측은 SQL Server 2014(12.x)부터 100이고, 이전 SQL Server 버전에서는 1입니다.

SQL Server 2017(14.x)부터 MSTVF를 사용하는 실행 계획을 최적화하면 인터리브 실행을 사용할 수 있으므로 위의 추론 대신 실제 카디널리티를 사용할 수 있습니다.

자세한 내용은 다중 명령문 테이블 반환 함수에 대한 인터리브 실행을 참조합니다.

저장 프로시저, 사용자 정의 함수에서 매개 변수를 전달하거나 배치 문에서 변수를 선언하고 설정할 때는 ANSI_WARNINGS 적용되지 않습니다. 예를 들어 변수가 char(3) 로 정의된 경우 3자보다 큰 값으로 설정하면 해당 데이터가 정의된 크기로 잘리고 INSERT 또는 UPDATE 문은 성공합니다.