• 아티클

함수 유형

SQL Server 2008에서는 사용자 정의 함수와 기본 제공 시스템 함수를 모두 지원합니다.

스칼라 함수

사용자 정의 스칼라 함수는 RETURNS 절에 정의된 유형의 단일 데이터 값을 반환합니다. 간단한 스칼라 함수에는 함수 본문이 없으며 스칼라 값이 단일 함수 문(대개 SELECT 문)의 결과입니다. 다중 문 스칼라 함수의 함수 본문은 BEGIN...END 블록으로 정의되며 여기에는 단일 값을 반환하는 일련의 Transact-SQL 문이 포함됩니다. 반환 유형은 text, ntext, image, cursor, spatial, hierarchyID 및 timestamp를 제외한 모든 데이터 형식일 수 있습니다.

다음 예에서는 단일 스칼라 ufnGetInventoryStock 함수를 사용하여 ProductModelID가 75와 80 사이인 제품의 현재 재고 수량을 반환합니다.

USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO

다음 예에서는 다중 문 스칼라 함수를 만듭니다. 이 함수는 ProductID를 단일 입력 값으로 사용하고 지정한 제품의 총 재고 수량을 단일 반환 값으로 반환합니다.

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity) 
    FROM Production.ProductInventory p 
    WHERE p.ProductID = @ProductID 
        AND p.LocationID = '6';
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN @ret;
END;
GO

테이블 반환 함수

사용자 정의 테이블 반환 함수는 table 데이터 형식을 반환합니다. 인라인 테이블 반환 함수에는 함수 본문이 없으며 테이블이 단일 SELECT 문의 결과 집합입니다.

다음 예에서는 인라인 테이블 반환 함수를 만듭니다. 함수에 고객(상점) ID가 단일 입력 매개 변수로 입력되고 ProductID, Name 및 YTD Total(해당 상점에 판매된 각 제품의 연간 총 매출액) 열이 반환됩니다.

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

다음 예에서는 함수를 호출하고 고객 ID 602를 지정합니다.

SELECT * FROM Sales.ufn_SalesByStore (602);

다중 문 테이블 반환 함수에서는 BEGIN...END 블록이 함수 본문을 정의하며 여기에는 행을 작성하여 테이블 결과에 삽입하는 일련의 Transact-SQL 문이 포함됩니다.

다음 예에서는 테이블 반환 함수를 만듭니다. 이 함수는 EmployeeID를 단일 입력 매개 변수로 사용하고 지정한 직원 ID 109에 직접 또는 간접적으로 보고하는 모든 직원의 목록을 반환합니다. 직원 ID 109는 예에서 입력 매개 변수로 사용되며 직원의 목록이 결과 테이블에 반환됩니다.

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

기본 제공 함수

SQL Server에는 다양한 작업을 수행하는 데 도움이 되는 기본 제공 함수가 있습니다. 기본 제공 함수는 수정할 수 없습니다. Transact-SQL 문에 기본 제공 함수를 사용하여 다음 작업을 수행할 수 있습니다.

  • SQL Server 시스템 테이블에 직접 액세스하지 않고 시스템 테이블의 정보에 액세스할 수 있습니다. 자세한 내용은 시스템 함수 사용를 참조하십시오.

  • SUM, GETDATE, IDENTITY 등의 일반적인 태스크를 수행할 수 있습니다. 자세한 내용은 함수(Transact-SQL)를 참조하십시오.

기본 제공 함수는 스칼라 또는 table 데이터 형식을 반환합니다. 예를 들어 마지막 Transact-SQL 문이 성공적으로 실행되면 @@ERROR가 0을 반환합니다. 문이 오류를 생성하면 @@ERROR는 해당 오류 번호를 반환합니다. 또한 SUM(parameter) 함수는 매개 변수에 대한 모든 값의 합계를 반환합니다.

변경 내역

업데이트된 내용

인라인 스칼라 함수에 대한 잘못된 내용을 제거했습니다.

참고 항목

참조

함수(Transact-SQL)

관련 자료

사용자 정의 함수 이해