免费无版权图片素材网站/定制网站多少钱

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

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_expressionEND
[ ; ]

　　示例：

CREATE FUNCTION [dbo].[FN_GetProductNameByProductID]
(@ProductID INT
)RETURNS VARCHAR(50)
ASBEGINDECLARE @ProductName VARCHAR(50)SELECT @ProductName = [ProductName]    FROM [dbo].[Product]WHERE [ProductID] = @ProductIDRETURN @ProductNameEND

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
ASRETURN(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 RETURNEND
[ ; ]

　　示例：

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

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

4. 删除函数

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

DROP FUNCTION [dbo].[FN_GetAllChildren]