Sql Server系列：自定义函数

　　用户自定义函数可以像系统函数一样在查询或存储过程中调用，可以接受参数、执行操作并将操作结果以值的形式返回。返回值可以是单个标量或结果集。

1. 标量函数

　　标量函数返回一个确定类型的标量值，对于多语句的标量函数，定义在BEGIN END块中的函数体包含一系列返回单个值的T-SQL语句。

　　创建标量函数语法结构：

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

　　示例：

CREATE FUNCTION [dbo].[FN_GetProductNameByProductID]
(
    @ProductID INT
)
    RETURNS VARCHAR(50)
AS
    BEGIN
        DECLARE @ProductName VARCHAR(50)

        SELECT @ProductName = [ProductName]    FROM [dbo].[Product]
        WHERE [ProductID] = @ProductID

        RETURN @ProductName
    END

SELECT [dbo].[FN_GetProductNameByProductID](1)
-- SELECT dbo.FN_GetProductNameByProductID(1)

2. 表值函数

　　表值函数是返回数据类型为table的函数，返回的表值是单个SELECT语句查询的结果。

　　创建表值函数语法结构：

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

　　示例：

CREATE FUNCTION [dbo].[FN_GetProductsByCategoryID]
(
    @CategoryID INT
)
    RETURNS TABLE
AS
    RETURN
    (
        SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]
        FROM [dbo].[Product]
        WHERE [CategoryID] = @CategoryID
    )

SELECT * FROM [dbo].[FN_GetProductsByCategoryID](1)

3. 多语句表值函数

　　多语句表值函数可以看作标量型函数和表值函数的结合体。该函数的返回值是一个表，但它和标量值自定义函数用于，有一个用BEGIN END包含起来的函数体。返回值的表中数据是由函数体中的语句插入的。多语句表值函数可以进行多次查询，对数据进行多次筛选与合并，弥补了表值自定义函数的不足。

　　多语句表值函数语法结构：

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

　　示例：

CREATE FUNCTION [dbo].[UFN_GetAllChildren]
(
    @CategoryID INT
)
RETURNS @Result TABLE 
(
    [CategoryID] INT NOT NULL,
    [CategoryName] VARCHAR(50) NULL,
    [ParentID] int NULL,
    [Level] int NULL
)
AS
    BEGIN
        WITH CTE AS
        (
            SELECT [CategoryID],[CategoryName],[ParentID],0 AS [Level]
            FROM [dbo].[Category]
            WHERE [CategoryID] = 2
            UNION ALL
            SELECT [dbo].[Category].[CategoryID],[dbo].[Category].[CategoryName],[dbo].[Category].[ParentID],[Level] + 1
            FROM CTE INNER JOIN [dbo].[Category]
            ON CTE.[CategoryID] = [dbo].[Category].[ParentID]
        )

        INSERT INTO @Result([CategoryID],[CategoryName],[ParentID],[Level])
        SELECT [CategoryID],[CategoryName],[ParentID],[Level] FROM CTE

        RETURN
    END

SELECT * FROM [dbo].[UFN_GetAllChildren](2)

4. 删除函数

DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ] 

DROP FUNCTION [dbo].[FN_GetAllChildren]