프로그래밍 언어의 함수처럼 SQL Server 사용자 정의 함수는 매개 변수를 받아들이고 복잡한 계산과 같은 동작을 수행하며 해당 작업의 결과를 값으로 반환합니다. 반환 값은 단일 스칼라 값이나 결과 집합일 수 있습니다.

사용자 정의 함수

UDF(사용자 정의 함수)를 사용하는 이유는 무엇인가요?

  • 모듈식 프로그래밍을 허용합니다.

    함수를 한 번 만들어 데이터베이스에 저장한 후에는 프로그램에서 여러 번 호출할 수 있습니다. 사용자 정의 함수는 프로그램 원본 코드에 관계없이 수정할 수 있습니다.

  • 작업을 더 빨리 실행할 수 있습니다.

    저장 프로시저와 마찬가지로 Transact-SQL 사용자 정의 함수는 계획을 캐시하고 반복 실행에 다시 사용함으로써 Transact-SQL 코드의 컴파일 비용을 줄입니다. 즉, 사용자 정의 함수를 매번 다시 구문 분석하고 최적화할 필요가 없기 때문에 더 빨리 실행할 수 있습니다.

    CLR 함수는 계산 태스크, 문자열 조작 및 비즈니스 논리 면에서 Transact-SQL 함수보다 더 뛰어난 성공을 제공합니다. Transact-SQL 함수는 데이터를 많이 액세스하는 논리에 더 적합합니다.

  • 네트워크 트래픽을 줄일 수 있습니다.

    단일 스칼라 식으로 표현할 수 없는 일부 복잡한 제약 조건을 기반으로 데이터를 필터링하는 작업을 함수로 표현할 수 있습니다. 그런 다음 WHERE 절에서 이 함수를 호출하여 클라이언트에 전송되는 행 수를 줄일 수 있습니다.

중요

쿼리에 포함된 Transact-SQL UDF는 단일 스레드(직렬 실행 계획)에서만 실행할 수 있습니다. 따라서 UDF를 사용하여 병렬 쿼리 처리를 금지합니다.

함수 유형

스칼라 함수 사용자 정의 스칼라 함수는 RETURNS 절에 정의된 유형의 단일 데이터 값을 반환합니다. 인라인 스칼라 함수의 경우 반환된 스칼라 값이 단일 문의 결과입니다. 다중 문 스칼라 함수의 경우 함수 본문에 단일 값을 반환하는 일련의 Transact-SQL 문이 포함됩니다. 반환 유형은 text, ntext, image, cursor및 timestamp를 제외한 모든 데이터 형식일 수 있습니다.

테이블 반환 함수 사용자 정의 테이블 값 함수는 table 데이터 형식을 반환합니다. 인라인 테이블 반환 함수에는 함수 본문이 없으며 테이블이 단일 SELECT 문의 결과 집합입니다.

지침

한 문을 취소하고 모듈(트리거, 저장 프로시저 등)의 다음 문을 실행하도록 하는 Transact-SQL 오류는 함수 내에서 다르게 처리됩니다. 함수에서 그러한 오류가 발생하면 함수의 실행이 중단됩니다. 이에 따라 함수를 호출한 문도 취소됩니다.

BEGIN...END 블록 내에 있는 명령문은 어떠한 부작용도 유발하지 않습니다. 함수의 부작용으로는 데이터베이스 테이블 수정과 같은 함수 외부 범위를 갖는 리소스 상태의 영구적인 변경을 들 수 있습니다. 함수의 문에서 변경할 수 있는 것은 로컬 커서나 변수와 같은 함수의 로컬 개체뿐입니다. 함수에서 수행할 수 없는 동작의 예로는 데이터베이스 테이블의 수정, 함수에서 로컬로 사용되지 않는 커서 작업, 전자 메일 보내기, 카탈로그 수정 시도 및 사용자에게 반환되는 결과 집합 생성 등이 있습니다.

참고

SQL Server에서는 CREATE FUNCTION 문이 실행될 때 존재하지 않는 리소스에 대해 부작용이 생기는 경우에는 CREATE FUNCTION 문을 실행하지만 이 문이 호출되는 경우에는 SQL Server 에서 이 함수가 실행되지 않습니다.

쿼리에 지정된 함수가 실제로 실행되는 횟수는 최적화 프로그램에서 작성한 실행 계획마다 다릅니다. 예를 들면 WHERE 절의 하위 쿼리에서 호출하는 함수가 있습니다. 하위 쿼리 및 그 함수가 실행되는 횟수는 최적화 프로그램에서 선택한 액세스 경로에 따라 다릅니다.

함수의 유효한 문

함수에서 사용할 수 있는 문의 유형은 다음과 같습니다.

  • 함수에서 로컬로 사용되는 데이터 변수와 커서를 정의하는 데 사용되는 DECLARE 문
  • SET를 사용하여 스칼라 및 테이블 지역 변수에 값을 할당하는 것과 같이 함수의 로컬 개체에 값 할당
  • 함수에서 커서 선언, 열기, 닫기, 할당 취소 등 로컬 커서를 참조하는 커서 작업. 클라이언트에 데이터를 반환하는 FETCH 문은 사용할 수 없습니다. INTO 절을 사용하여 지역 변수에 값을 할당하는 FETCH 문만 사용할 수 있습니다.
  • TRY...CATCH 문을 제외한 흐름 제어 명령문
  • 함수에서 로컬로 사용되는 변수에 값을 할당하는 식이 있는 선택 목록이 포함된 SELECT 문
  • 함수에서 로컬로 사용되는 테이블 변수를 수정하는 UPDATE, INSERT 및 DELETE 문
  • 확장 저장 프로시저를 호출하는 EXECUTE 문

제한 사항

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

권한

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

스칼라 함수

다음 예에서는 AdventureWorks2012 데이터베이스의 다중 명령문 스칼라 함수(스칼라 UDF) 를 만듭니다. 함수에 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 함수를 사용하여 ProductModelID 가 75와 80 사이인 제품의 현재 재고 수량을 반환합니다.

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

테이블 반환 함수

다음 예제에서는 AdventureWorks2012 데이터베이스에서 인라인 TVF(테이블 반환 함수) 를 만듭니다. 함수에 고객(상점) ID가 단일 입력 매개 변수로 입력되고 ProductID, Name및 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);

다음 예제에서는 AdventureWorks2012 데이터베이스에서 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);