Sql Server系列：自定义函数-白红宇

Sql Server系列：自定义函数

阅读量：5978 次

发布时间：2019-06-20

本文共 3015 字，大约阅读时间需要 10 分钟。

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

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 
     
       [ ,...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 
     
       [ ,...n ] ]    [ AS ]    RETURN [ ( ] select_stmt [ ) ][ ; ]

　　示例：

CREATE FUNCTION [dbo].[FN_GetProductsByCategoryID](    @CategoryID INT)    RETURNS TABLEAS    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 
     
          [ WITH 
      
        [ ,...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

你可能感兴趣的文章