Sql Server系列：自定义函数

太过爱你忘了你带给我的痛 2021-12-21 07:59 292阅读 0赞

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

**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]

转载于:https://www.cnblogs.com/libingql/p/4166787.html