Table-Valued Function (TVF) - T-SQL

T-SQL (Transact-SQL)에서 Table-Valued Function (TVF)는 아주 유용한 기능 중 하나입니다. 이 함수를 이용하면 하나의 테이블이 리턴이 되죠.

특히 Entity Framework에서 아주 유용하게 쓰입니다. Entity Framework Core에서는 Stored Procedure를 이용하여 데이터 셋을 리턴하기가 쉽지 않지만 TVF를 이용하면 다른 코드 없이 바로 자동으로 모델이 생성되고 리턴이 됩니다.
 

반응형

---

TVF는 두 가지가 있습니다

---

Inline Table-Valued Function

한 블록의 SELECT t-sql 코드로 테이블을 리턴

---

Multi-statement Table-Valued Function

여러 라인의 로직이 들어간 후 SELECT t-sql을 이용하여 마지막에 테이블을 리턴. Stored Procedure 랑 아주 흡사합니다.

---

INLINE TABLE-VALUED FUNCTION

 

CREATE FUNCTION udfProductInYear (
    @model_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

RETURNS TABLE 이 테이블을 보내라는 지정 커멘드입니다. RETURN 다음에는 하나의 SELECT 블록만 있습니다. 하나의 테이블 전체를 보내기보다 간단히 몇 가지의 칼럼만 보낼 때 유용합니다.
 

728x90

---

MULTI-STATEMENT TABLE-VALUED FUNCTION

CREATE FUNCTION udfContacts()
    RETURNS @contacts TABLE (
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(255),
        phone VARCHAR(25),
        contact_type VARCHAR(20)
    )
AS
BEGIN
    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Staff'
    FROM
        sales.staffs;

    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Customer'
    FROM
        sales.customers;
    RETURN;
END;

Multi-statement 함수는 RETURNS에 돌려보낼 테이블 Definition 지정을 하고 다음에 BEGIN과 END 가 있습니다. 그사이에 로직을 첨가할 수 있죠. 위의 예제는 contacts라는 테이블을 보내는데 staffs와 customers 두 테이블 데이터를 한 곳에 넣어서 contacts라는 마스터 테이블을 보내죠. 여기에선 두 테이블의 데이터를 넣기 위해 로직이 들어가고 Multi-statement를 쓰게 되었습니다.

사실 위의 예제를 UNION을 써서 한 블록의 Inline 함수로도 보낼 수 있죠.

---

이렇게 TVF를 만들고 .NET 프로젝트의 Entity Framework Core에 Scaffolding (Scaffold-DbContext 커멘드)을 하여 데이터 베이스를 연결하면 TVF의 함수에서 리턴되는 테이블은 클래스화가 자동으로 됩니다.

---

즐거운 코딩되세요. 

---

도움이 되셨거나 즐거우셨다면 아래의 ❤️공감버튼이나 구독버튼을 눌러 주세요~  감사합니다