테이블 값을 반환하는 함수 생성하기

5분

테이블 반환 함수를 사용하면 복잡한 쿼리 논리를 결과 집합을 반환하는 재사용 가능한 구성 요소로 캡슐화할 수 있습니다. 테이블이나 뷰와 마찬가지로 쿼리에서 이러한 함수를 직접 호출하여 코드를 모듈화하고 유지 관리할 수 있습니다.

데이터베이스 애플리케이션을 빌드할 때 입력 매개 변수를 기반으로 필터링되거나 계산된 데이터 집합을 검색해야 하는 경우가 많습니다. 테이블 반환 함수는 쿼리 논리를 매개 변수를 허용하고 테이블을 반환하는 함수로 패키징하여 이 문제를 해결합니다. 저장 프로시저와 달리, JOIN 절과 SELECT 문에서 테이블 값 함수를 사용할 수 있어 함수 결과를 데이터 소스로 처리할 수 있는 유연성을 제공합니다.

테이블 값 반환 함수 유형 이해

SQL Server는 서로 다른 시나리오에 적합한 두 가지 유형의 테이블 반환 함수를 제공합니다.

인라인 테이블 값 함수

단일 SELECT 문을 포함하고 결과를 직접 반환합니다. 인라인 함수를 사용할 때, 테이블 구조를 정의할 필요가 없습니다. SQL Server가 SELECT 구문에서 이를 유추합니다. 쿼리 최적화 프로그램은 매개 변수가 있는 뷰와 같은 인라인 테이블 반환 함수를 처리하여 더 나은 실행 계획을 생성하는 경우가 많습니다.

다중 문장 테이블 값 함수

BEGIN...END 블록을 사용하고 반환된 테이블의 구조를 명시적으로 선언합니다. 이 형식은 여러 문을 실행하거나, 복잡한 계산을 수행하거나, 결과 집합을 반복적으로 빌드해야 하는 경우 더 많은 제어를 제공합니다. 그러나 최적화 프로그램에서 이러한 함수를 다르게 처리하므로 이러한 유연성은 성능 절상과 함께 제공됩니다.

이러한 함수 중에서 선택하는 것은 특정 요구 사항에 따라 달라집니다. 매개 변수를 사용하는 간단한 쿼리의 경우 인라인 함수는 더 나은 성능을 제공합니다. 결과 집합을 빌드하기 위해 절차 논리 또는 여러 단계가 필요한 경우 다중 문 함수가 필요합니다.

인라인 테이블 반환 함수 만들기

인라인 테이블 반환 함수는 쿼리를 매개 변수화하는 간결한 방법을 제공합니다. 단일 RETURN 문과 SELECT 쿼리를 사용하여 정의합니다.

다음 예제에서는 특정 고객에 대한 주문을 검색하는 인라인 함수를 보여 줍니다.

CREATE FUNCTION dbo.GetCustomerOrders
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

이제 테이블과 마찬가지로 쿼리에서 이 함수를 사용할 수 있습니다.

SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';

이 함수는 고객 ID 매개 변수를 수락하고 해당 고객의 주문만 반환합니다. 필요에 따라 결과를 추가로 필터링 JOIN하거나 집계할 수 있습니다. 이 방법은 고객 필터링 논리를 캡슐화하는 동안 기본 쿼리를 깨끗하게 유지합니다.

테이블 값을 반환하는 다중 문 함수 만들기

여러 구문으로 구성된 테이블 반환 함수는 결과 집합을 빌드하기 위해 여러 작업을 수행해야 하는 경우에 보다 높은 유연성을 제공합니다.

여러 집계를 사용하여 제품 판매 요약을 계산하는 함수를 고려합니다.

CREATE FUNCTION dbo.GetProductSalesSummary
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @SalesSummary TABLE
(
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalQuantity INT,
    TotalRevenue DECIMAL(18,2),
    AveragePrice DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @SalesSummary
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
        AVG(od.UnitPrice) AS AveragePrice
    FROM Production.Products p
    INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
    INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;
    
    RETURN;
END;

특정 열 및 데이터 형식을 사용하여 테이블 변수 @SalesSummary 를 명시적으로 선언하는 방법을 확인합니다. 함수 본문은 이 테이블 변수에 데이터를 삽입한 다음 반환합니다. 이 구조를 사용하면 필요에 따라 추가 처리 논리, 오류 처리 또는 조건문을 추가할 수 있습니다.

쿼리에서 테이블 반환 함수 사용

테이블 반환 함수는 쿼리에 원활하게 통합되어 강력한 데이터 검색 패턴을 가능하게 합니다.

함수 결과를 다른 테이블과 조인할 수 있습니다.

SELECT 
    c.CustomerName,
    s.ProductName,
    s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;

이 CROSS APPLY 예제에서는 함수 매개 변수가 상수이지만 연산자는 Customers 테이블에서 각 행에 대한 함수를 호출합니다. 열 값을 매개 변수 CROSS APPLY 로 전달하는 경우 특히 유용합니다.

SELECT 
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';

이 쿼리는 각 고객에 대해 완료된 모든 주문을 검색하여 테이블 반환 함수가 쿼리 내에서 행 단위 처리를 가능하게 하는 방법을 보여 줍니다. 이 함수는 상호 관련된 하위 쿼리 역할을 하지만 가독성과 재사용성이 향상됩니다.

행 단위 평가가 필요하지 않은 인라인 테이블 반환 함수의 경우 INNER JOIN 구문을 사용할 수 있습니다.

SELECT 
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;

이러한 기술을 사용하면 더 간단하고 테스트된 함수 구성 요소에서 복잡한 쿼리를 빌드하여 코드 유지 관리 효율성과 개발 효율성을 모두 향상시킬 수 있습니다.

피드백

이 페이지가 도움이 되었나요?