테이블 반환 함수(table-valued function)

 

테이블 반환 함수란?

 

테이블 반환 함수는 테이블 데이터를 반환하는 사용자 정의함수이다. 

 

테이블 반환 함수의 반환 타입이 테이블이므로 이 함수를 마치 테이블처럼 사용할 수도 있다. 

 

테이블 반환 함수 만들기 

# Products 테이블 생성

Create Table Products 
(
product_name nvarchar(100),
model_year int ,
list_price money    
)

# 테이블에 데이터 입력

insert into Products 
values ('Sonata', 2020, 1000)
        , ('Avante', 2019, 700)
        , ('Santafe', 2020, 1200)

 

# 테이블 반환 함수 생성 

Create Function Fn_ProductInYear (
@Model_year int
)
returns Table
as 
Return
    select product_name,
            model_year,
            list_price 
    from products
    where model_year = @model_year;

 

테이블 반환 함수 실행

테이블 반환 함수 실행을 위해 Select문의 From절에 둔다. 

그리고 매게변수로 함수에 2019를 전달한다. ]

select *
from dbo.Fn_ProductInYear(2019);

 

실행결과

테이블 반환 함수 수정

테이블 반환 함수 수정문은 생성문과 동일하다. 다만 시작 키워드가 Create가 아닌 Alter이다. 

특정년도 사이에 제품리스트를 가져오도록 수정했다. 

 

Alter Function Fn_ProductInYear (
@start_year int,
@end_year int
)
returns Table
as 
Return
    select product_name,
            model_year,
            list_price 
    from products
    where model_year between @start_year and @end_year;

 

select *
from dbo.Fn_ProductInYear(2019, 2020)
order by product_name;

 

다중문 테이블 반환 함수

alter Table Products 
Add Product_div nvarchar(20)

update products 
   set Prodcut_div = 'Car'

 

insert into Products 
values ('Nike', 2020, 100, 'Shoes')
       , ('Rebok', 2019, 70, 'Shoes')
       , ('FILA', 2020, 120, 'Shoes')
       , ('HP', 2018, 500, 'PC')
       , ('DELL', 2019, 400, 'PC') 

 

Create Function FN_ProductsInYear2()
    Returns @Products TABLE (
        product_name nvarchar(100),
        model_year int,
        list_price money,
        Product_div nvarchar(20)

)
as
begin
    insert into @Products
    select product_name,
            model_year,
            list_price,
            Product_div
    from Products
    where Product_div = 'Car'

    insert into @Products
    select product_name,
            model_year,
            list_price,
            Product_div
    from Products
    where Product_div = 'PC'
    RETURN;
end;

 

SELECT * 
FROM FN_ProductsInYear2();

실행결과

테이블 반환 함수는 매개변수 값을 받는 뷰처럼 사용될 수 있다.

저장 프로시저와 비교해 보았을 때, 테이블처럼 사용될 수 있기 때문에 테이블 반환 함수가 저장 프로시저 보다 좀 더 유연함을 가지고 있다.