테이블 값 사용자 정의 함수
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 AdventureWorks;
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 = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual AS i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
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 AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO
참고 항목
개념
인라인 사용자 정의 함수
결정적 함수 및 비결정적 함수
저장 프로시저를 함수로 다시 작성