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

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

이 항목에서

시작하기 전 주의 사항:

제한 및 제약사항

보안
사용자 정의 함수를 만들려면 다음을 수행합니다.

스칼라 함수 만들기

테이블 반환 함수 만들기

시작하기 전에

한계 및 제한사항

사용자 정의 함수는 데이터베이스 상태를 수정하는 작업을 수행하는 데 사용할 수 없습니다.
사용자 정의 함수는 테이블을 대상으로 하는 OUTPUT INTO 절을 포함할 수 없습니다.
사용자 정의 함수는 여러 결과 집합을 반환할 수 없습니다. 여러 결과 집합을 반환해야 하는 경우 저장 프로시저를 사용합니다.
오류 처리는 사용자 정의 함수에서 제한됩니다. UDF는 TRY...CATCH 및 @ERROR RAISERROR를 지원하지 않습니다.
사용자 정의 함수는 저장 프로시저를 호출할 수 없지만 확장 저장 프로시저를 호출할 수 있습니다.
사용자 정의 함수는 동적 SQL 또는 임시 테이블을 사용할 수 없습니다. 테이블 변수가 허용됩니다.
SET 문은 사용자 정의 함수에서 허용되지 않습니다.
FOR XML 절은 허용되지 않습니다.
사용자 정의 함수는 중첩될 수 있습니다. 즉, 한 사용자 정의 함수가 다른 함수를 호출할 수 있습니다. 호출된 함수가 실행을 시작할 때 중첩 수준이 증가하며 호출된 함수가 실행을 완료하면 감소합니다. 사용자 정의 함수는 최대 32개 수준까지 중첩할 수 있습니다. 최대 중첩 수준을 초과하면 전체 호출 함수 체인이 실패합니다. Transact-SQL 사용자 정의 함수의 관리 코드에 대한 참조는 32 수준 중첩 제한에 대해 한 수준으로 계산됩니다. 관리 코드 내에서 호출된 메서드는 이 제한에 따라 계산되지 않습니다.
다음 Service Broker 문은 Transact-SQL 사용자 정의 함수의 정의에 포함될 수 없습니다.
- 대화형 대화 시작
- 대화 종료
- 대화 그룹 가져오기
- 대화를 이동하다
- 받다
- 보내기

안전

권한

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

스칼라 함수

다음 예제에서는 AdventureWorks2012 데이터베이스에 다중 상태 스칼라 함수를 만듭니다. 이 함수는 입력 값 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;  
GO

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

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

Table-Valued 함수

다음 예제에서는 AdventureWorks2012 데이터베이스에 인라인 테이블 반환 함수를 만듭니다. 이 함수는 하나의 입력 매개 변수, 고객(매장) 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);

다음 예제에서는 AdventureWorks2012 데이터베이스에 테이블 반환 함수를 만듭니다. 이 함수는 단일 입력 매개 변수를 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  
-- Example invocation  
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);

또한 참조하십시오

사용자 정의 함수
함수 생성하기(Transact-SQL)

Last updated on 2017-06-13