테이블 반환 사용자 정의 함수
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