• 아티클

테이블 반환 사용자 정의 함수

table 데이터 형식을 반환하는 사용자 정의 함수는 뷰 대신 사용할 수 있는 강력한 함수입니다. 이러한 함수를 테이블 반환 함수라고 합니다. 테이블 반환 사용자 정의 함수는 Transact-SQL 쿼리에서 테이블 또는 뷰 식이 허용되는 경우에 사용할 수 있습니다. 뷰에서는 SELECT 문을 하나만 사용할 수 있지만 사용자 정의 함수에서는 여러 개를 사용할 수 있으므로 뷰에서보다 강력한 논리가 허용됩니다.

또한 하나의 결과 집합을 반환하는 저장 프로시저 대신 테이블 반환 사용자 정의 함수를 사용할 수 있습니다. 사용자 정의 함수에서 반환된 테이블은 Transact-SQL 문의 FROM 절에서는 참조할 수 있지만 결과 집합을 반환하는 저장 프로시저에서는 참조할 수 없습니다.

테이블 반환 사용자 정의 함수의 구성 요소

테이블 반환 사용자 정의 함수에서

  • RETURNS 절은 함수에서 반환한 테이블의 로컬 반환 변수 이름을 정의합니다. RETURNS 절은 테이블 형식도 정의합니다. 로컬 반환 변수 이름의 범위는 함수 내에서 로컬입니다.

  • 함수 본문에 있는 Transact-SQL 문은 행을 작성하여 RETURNS 절에서 정의한 반환 변수에 삽입합니다.

  • RETURN 문을 실행하면 변수에 삽입된 행은 함수의 테이블 형식 출력으로 반환됩니다. RETURN 문에서는 인수를 사용할 수 없습니다.

테이블 반환 함수에 있는 Transact-SQL 문은 사용자에게 직접 결과 집합을 반환할 수 없습니다. 함수에서 반환하는 table 정보만 사용자에게 반환될 수 있습니다.

[!참고]

text in row 테이블 옵션은 사용자 정의 함수에서 반환된 테이블에 대해 256으로 자동 설정됩니다. 이 값은 변경할 수 없습니다. READTEXT, WRITETEXT, UPDATETEXT 문은 테이블에 있는 text, ntext 또는 image 열을 읽거나 쓰는데 사용할 수 없습니다. 자세한 내용은 행 내부 데이터을 참조하십시오.

예

다음 예에서는 dbo.ufnGetContactInformation 함수를 만들어서 테이블 반환 함수의 구성 요소를 보여 줍니다. 이 함수에서 로컬 반환 변수 이름은 @retContactInformation입니다. 함수 본문에 있는 문은 이 변수에 행을 삽입하여 함수에서 반환된 테이블 결과를 작성합니다.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    ContactID int PRIMARY KEY NOT NULL, 
    FirstName nvarchar(50) NULL, 
    LastName nvarchar(50) NULL, 
    JobTitle nvarchar(50) NULL, 
    ContactType nvarchar(50) NULL
)
AS 
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @JobTitle nvarchar(50), 
        @ContactType nvarchar(50);
    -- Get common contact information
    SELECT 
        @ContactID = BusinessEntityID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Person 
    WHERE BusinessEntityID = @ContactID;
    -- Get contact job title
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
                THEN (SELECT JobTitle 
                      FROM HumanResources.Employee AS e
                      WHERE e.BusinessEntityID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)

            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)
            ELSE NULL 
        END;
    -- Get contact type
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
            THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
            THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
            THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN') 
            THEN 'Consumer'
             -- Check for general contact
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC') 
            THEN 'General Contact'
        END;
    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;
    RETURN;
END;
GO

다음 예에서는 두 SELECT 문의 FROM 절에서 테이블 반환 함수 dbo.ufnGetContactInformation을 사용합니다.

USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO